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
