Group By的用法示例

 

CREATE   TABLE  #A
(
    UserId         INT ,
    UserName     NVARCHAR ( 50 ),
    Mobile         VARCHAR ( 20 )
)

INSERT   INTO  #A  VALUES ( 1 , ' aaa ' , ' 13926436455 ' )
INSERT   INTO  #A  VALUES ( 3 , ' bbb ' , ' 13587656455 ' )

CREATE   TABLE  #C
(
    UserId         INT ,
    Mobile         VARCHAR ( 20 ),
    PostTime     DATETIME ,
    HasTime         INT
)


INSERT   INTO  #C  VALUES ( 1 , ' 13345656455 ' , ' 2007-09-28 17:40:46.310 ' , ' 51 ' )
INSERT   INTO  #C  VALUES ( 1 , ' 13345656455 ' , ' 2007-09-28 17:40:50.937 ' , ' 11 ' )
INSERT   INTO  #C  VALUES ( 2 , ' 13545656455 ' , ' 2007-09-28 17:41:06.187 ' , ' 13 ' )
INSERT   INTO  #C  VALUES ( 3 , ' 13587656455 ' , ' 2007-09-28 17:41:21.230 ' , ' 13 ' )
INSERT   INTO  #C  VALUES ( 4 , ' 13987656455 ' , ' 2007-09-28 17:41:33.640 ' , ' 34 ' )
INSERT   INTO  #C  VALUES ( 4 , ' 13987656455 ' , ' 2007-09-28 17:41:40.810 ' , ' 14 ' )
INSERT   INTO  #C  VALUES ( 1 , ' 13345656455 ' , ' 2007-09-28 17:41:59.450 ' , ' 19 ' )

-- 和#A表连接获取UserName，根据#C表各Mobile和#A表各UserName的HasTime的和，按时间和降序排列
-- 连接后表中UserName为NULL的将其默认设置为“匿名用户”
SELECT  #C.Mobile, ISNULL (UserName, ' 匿名用户 ' )  AS  UserName, SUM (HasTime)  AS  SumTime  FROM  #C 
LEFT   JOIN  #A  ON  #C.UserId = #A.UserId  GROUP   BY  #C.Mobile,#A.UserName  ORDER   BY  SumTime  DESC