情景:假如有一个登陆日志表,表名为sys_loginlog,该表记录的是用户使用系统的情况,有一个字段叫logtime,即登陆时间。问题如下:
1.统计某一年各个月用户登录系统的情况。
2.统计某一个月用户登录系统的情况。
3.统计某一天各个时间段用户登录系统的情况。
答案如下,分别使用了oracle 和 sqlserver语法。
- /**统计一年中每月的登录次数oracl */
- select count(*) as sum ,substr(to_char(t.logintime,'YYYY-MM'),6,2) as month
- from Mwpm_Sys_Loginlog t where
- to_char(t.logintime,'YYYY') = '2009' and t.logintype='0' group by substr(to_char(t.logintime,'YYYY-MM'),6,2)
- /**统计一年中每月的登录次数sqlserver */
- select count(*) as sum ,substring(convert(char(7),t.logintime,120),6,2) as month
- from Mwpm_Sys_Loginlog t where
- convert(char(4),t.logintime,120) = '2009' and t.logintype='0' group by substring(convert(char(7),t.logintime,120),6,2)
- ///*** 统计一年中各个时间段的登录次数----------oracle语法**/
- select to_char(t.logintime,'hh24')||':00-'||to_char(to_number(to_char(t.logintime,'hh24'))+1) ||':00' as internal,count(*) as sum,
- to_char(to_number(to_char(t.logintime,'hh24'))) as time from Mwpm_Sys_Loginlog t
- where to_char(t.logintime,'yyyy-MM-dd')='2010-01-18'
- group by to_char(t.logintime,'hh24')||':00-'||to_char(to_number(to_char(t.logintime,'hh24'))+1)||':00',to_char(to_number(to_char(t.logintime,'hh24')))
- ///*** 统计一年中各个时间段的登录次数----------sqlserver语法**/
- select convert(char(2),t.logintime,108)+':00-'+ convert(char(2),dateadd(hh,+1,logintime),108)+':00' as internal,count(*) as sum,
- convert(char(2),dateadd(hh,+0,logintime),108) as time
- from
- Mwpm_Sys_Loginlog t where convert(char(10),t.logintime,120)='2010-01-18'
- group by convert(char(2),t.logintime,108)+':00-'+ convert(char(2),dateadd(hh,+1,logintime),108)+':00',convert(char(2),dateadd(hh,+0,logintime),108)
- /***统计一年中某个月的每天登陆的次数--oracle***/
- select substr(to_char(t.logintime,'YYYY-MM-DD'),9,9) as day ,count(*) as sum from Mwpm_Sys_Loginlog t
- where to_char(t.logintime,'YYYY-MM') = '2010-01'
- and t.logintype='0' group by to_char(t.logintime,'YYYY-MM-DD')
- /***统计一年中某个月的每天登陆的次数--sqlserver***/
- select substring((convert(char(10),t.logintime,120)),9,2) as day , count(*) as sum
- from Mwpm_Sys_Loginlog t where convert(char(7),t.logintime,120) = '2010-01' group by substring(convert(char(10),t.logintime,120),9,2)
- /**find()方法不支持 substring((convert(char(10),t.logintime,120)),9,2)**/