select top 6 userID, SUM(shuju2)-SUM(shuju1) as todaycount from A where datediff(day,[GuessTime],getdate())=0 group by userID having SUM(shuju2)-SUM(shuju1)>0 order by todaycount desc union all select top 6 userID, CONVERT(int,shuju2)-CONVERT(int,shuju1) as benzhoucount from B where datediff(week,CreateTime,getdate())=0 group by userID,shuju2,shuju1 having CONVERT(int,shuju2)-CONVERT(int,shuju1)>0 order by benzhoucount desc
SELECT * FROM ( select top 6 userID, SUM(shuju2)-SUM(shuju1) as todaycount from A where datediff(day,[GuessTime],getdate())=0 group by userID having SUM(shuju2)-SUM(shuju1)>0 order by todaycount desc )T UNION ALL select top 6 userID, CONVERT(int,shuju2)-CONVERT(int,shuju1) as benzhoucount from B where datediff(week,CreateTime,getdate())=0 group by userID,shuju2,shuju1 having CONVERT(int,shuju2)-CONVERT(int,shuju1)>0 order by benzhoucount desc
先把第一条的 todaycount 也转化成 int 型,再 union all 起来就不会报错了。
SELECT ISNULL(T1.userID,T2.userID)userID ,ISNULL(T1.todaycount,0)+ISNULL(T2.benzhoucount,0) FROM ( select top 6 userID, SUM(shuju2)-SUM(shuju1) as todaycount from A where datediff(day,[GuessTime],getdate())=0 group by userID having SUM(shuju2)-SUM(shuju1)>0 order by todaycount desc )T1 FULL JOIN ( select top 6 userID, CONVERT(int,shuju2)-CONVERT(int,shuju1) as benzhoucount from B where datediff(week,CreateTime,getdate())=0 group by userID,shuju2,shuju1 having CONVERT(int,shuju2)-CONVERT(int,shuju1)>0 order by benzhoucount DESC )T2 ON T1.userID=T2.userID
SELECT a.userID, a.todaycount + ISNULL(b.benzhoucount) todaycount FROM (第一条) a LEFT JOIN (第二条) b ON b.userID = a.userID
select top 6 userID, CONVERT(int,shuju2)-CONVERT(int,shuju1) as benzhoucount from B where datediff(week,CreateTime,getdate())=0 group by userID,shuju2,shuju1 having CONVERT(int,shuju2)-CONVERT(int,shuju1)>0 order by benzhoucount desc
select top 6 userID, SUM(shuju2)-SUM(shuju1) as todaycount from A where datediff(day,[GuessTime],getdate())=0
group by userID having SUM(shuju2)-SUM(shuju1)>0 order by todaycount desc
)T
UNION ALL select top 6 userID, CONVERT(int,shuju2)-CONVERT(int,shuju1) as benzhoucount from B where datediff(week,CreateTime,getdate())=0
group by userID,shuju2,shuju1 having CONVERT(int,shuju2)-CONVERT(int,shuju1)>0 order by benzhoucount desc
不对啊,,这样的话,只是合并了,相同的未合并啊
比如:
第一条查询出来的是
10001 30000
10003 28733
10010 18883
10088 12223
10123 4532
10234 4111
第二条查询出来的是
10001 233000
10003 128733
10020 188832
10188 122232
10123 94532您这样的语句合并后相同的userID未合并,而且,统计相同的也没相加啊我想要查询出来的结果为100001 263000
100003 157466
10020 188832
10188 122232
10123 990694
10088 12223
10123 4532
10234 4111
各位谢谢
ISNULL(T1.userID,T2.userID)userID
,ISNULL(T1.todaycount,0)+ISNULL(T2.benzhoucount,0)
FROM
(
select top 6 userID, SUM(shuju2)-SUM(shuju1) as todaycount from A where datediff(day,[GuessTime],getdate())=0
group by userID having SUM(shuju2)-SUM(shuju1)>0 order by todaycount desc
)T1
FULL JOIN
(
select top 6 userID, CONVERT(int,shuju2)-CONVERT(int,shuju1) as benzhoucount from B where datediff(week,CreateTime,getdate())=0
group by userID,shuju2,shuju1 having CONVERT(int,shuju2)-CONVERT(int,shuju1)>0 order by benzhoucount DESC
)T2 ON T1.userID=T2.userID
FROM (第一条) a
LEFT JOIN (第二条) b
ON b.userID = a.userID