34,575
社区成员
发帖
与我相关
我的任务
分享
第一条,查询今天的(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 a.userID, a.todaycount + ISNULL(b.benzhoucount) todaycount
FROM (第一条) a
LEFT JOIN (第二条) b
ON b.userID = a.userID
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 * 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