More practical statistical query in the project

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) **/