Scenario: If there is a login log table, the table name is Sys_loginlog, the table records the user's use of the system, there is a field called LogTime, that is, landing time. The questions are as follows:
1. Statistics of a certain year each month user login system.
2. Statistics of a one-month user login system.
3. Statistics of a certain day of the time period of user login system.
The answers are as follows, using Oracle and SQL Server syntax, respectively. Code:/** counts the number of logons per month in the year 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) /** Statistics of the monthly logon times of the year 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) ///*** counts the number of logons in each period of the year----------Oracle syntax **/ Select to_char (t.logintime, ' hh24 ') | | ': 0 0-' | | To_char (To_number (To_char (t.logintime, ' hh24 ')) +1 | | ': 0 0 ' 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 ') | | ': 0 0-' | | To_char (To_number (To_char (t.logintime, ' hh24 ')) +1 | | ': 0 0 ', To_char (To_number (To_char (t.logintime, ' hh24 ')) ///*** Count the number of logons in each period of the year----------SQL Server Syntax **/ select convert (char (2), t.logintime,108) + ': 00-' + Convert (char (2), DATEADD (Hh,+1,logintime), 108) + ': + ' as internal,count (*) as sum, CONVERT (char (2), dateAdd (Hh,+0,logintime), 108) as time from mwpm_sys_ Loginlog t where convert (char (), t.logintime,120) = ' 2010-01-18 ' group by convert (char (2), t.logintime,108) + ': 00-' + convert (char (2) , DateAdd (Hh,+1,logintime), 108) + ': + ', convert (char (2), DATEADD (Hh,+0,logintime), 108) /*** Statistics of the number of daily landings per month in a year--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 ' &nbsP; and t.logintype= ' 0 ' group by to_char ( T.logintime, ' yyyy-mm-dd ') /*** Statistics of the number of daily landings a month in a year--sqlserver***/ Select substring ((Convert (char), 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 () method does not support substring (CONVERT (char), t.logintime ), 9,2) **/