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 ' )
(
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