求两条sql 语句合并的问题

我说我行就是行 2015-01-14 03:31:29

第一条,查询今天的(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


我现在就是想把第一条语句和第二条语句合并起来,因为查询本周的不包括今天的,所以我现在想把两张表的数据加起来
今天+本周的(未查询当天的,因为此表为统计表,不包括)=本周的


求解。。。。谢谢
...全文
199 11 打赏 收藏 转发到动态 举报
写回复
用AI写文章
11 条回复
切换为时间正序
请发表友善的回复…
发表回复
Tiger_Zhao 2015-01-14
  • 打赏
  • 举报
回复
本周和今天的前6个userid不会总是一样吧?FULL JOIN 结果不止6条啊!
  • 打赏
  • 举报
回复
引用 8 楼 ky_min 的回复:
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
谢谢,好牛叉啊
Tiger_Zhao 2015-01-14
  • 打赏
  • 举报
回复
SELECT a.userID, a.todaycount + ISNULL(b.benzhoucount) todaycount 
FROM (第一条) a
LEFT JOIN (第二条) b
ON b.userID = a.userID
还在加载中灬 2015-01-14
  • 打赏
  • 举报
回复
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
  • 打赏
  • 举报
回复
查询几条就显示几条,,上面查询的是6条,那么最后的结果应该是 100001 263000 100003 157466 10020 188832 10188 122232 10123 990694 10088 12223 是这样的,,,各位大侠,,,还能求解?
  • 打赏
  • 举报
回复
引用 3 楼 ky_min 的回复:
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
不对啊,,这样的话,只是合并了,相同的未合并啊 比如: 第一条查询出来的是 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 各位谢谢
Tiger_Zhao 2015-01-14
  • 打赏
  • 举报
回复
先把第一条的 todaycount 也转化成 int 型,再 union all 起来就不会报错了。
还在加载中灬 2015-01-14
  • 打赏
  • 举报
回复
对前一个加派生
还在加载中灬 2015-01-14
  • 打赏
  • 举报
回复
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
  • 打赏
  • 举报
回复
引用 1 楼 u011840021 的回复:
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
早用过了,,报错
jason131203 2015-01-14
  • 打赏
  • 举报
回复
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

34,575

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧