求两条sql 语句合并的有关问题
求两条sql 语句合并的问题
------解决思路----------------------
第一条,查询今天的(A 表数据)
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
查询结果如下
10001 30000
10003 28733
10010 18883
10088 12223
10123 4532
10234 4111
第二条,查询本周的(不包括今天)(B表数据)
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 233000
10003 128733
10020 188832
10188 122232
10123 94532
10234 84111
我现在就是想把第一条语句和第二条语句合并起来,因为查询本周的不包括今天的,所以我现在想把两张表的数据加起来
今天+本周的(未查询当天的,因为此表为统计表,不包括)=本周的
求解。。。。谢谢
------解决思路----------------------
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