急急急!!! 多条select where in () 语句如何合并?

zamowhite 2016-08-09 03:03:01

select SUM(num) as a1
FROM table1
where dayid IN (1,2) AND UserID = 1
select SUM(num) as a2
FROM table1
where dayid IN (3,4) AND UserID = 1
.
.
.
.
select SUM(num) as a51
FROM table1
where dayid IN (101,102) AND UserID = 1


得到结果
a1 a2 …… a51
10 10 …… 10

...全文
445 6 打赏 收藏 转发到动态 举报
写回复
用AI写文章
6 条回复
切换为时间正序
请发表友善的回复…
发表回复
红柚皮 2016-08-11
  • 打赏
  • 举报
回复

SELECT 
SUM(CASE WHEN dayid IN (1,2) THEN num ELSE 0 END) AS a1,
SUM(CASE WHEN dayid IN (3,4) THEN num ELSE 0 END) AS a2,
.............
SUM(CASE WHEN dayid IN (101,102) THEN num ELSE 0 END) AS a51
 
FROM table1
WHERE UserID = 1
不用group by, sum计算总和
中国风 2016-08-09
  • 打赏
  • 举报
回复
UserID 作为条件时,不需要分组,把 THEN 1 改为 THEN num
中国风 2016-08-09
  • 打赏
  • 举报
回复
SELECT 
SUM(CASE WHEN dayid IN (1,2) THEN num ELSE 0 END) AS a1,
SUM(CASE WHEN dayid IN (3,4) THEN num ELSE 0 END) AS a1,
SUM(CASE WHEN dayid IN (5,6) THEN num ELSE 0 END) AS a1,
.............
SUM(CASE WHEN dayid IN (101,102) THEN num ELSE 0 END) AS a51

FROM table1
WHERE UserID = 1
zamowhite 2016-08-09
  • 打赏
  • 举报
回复
引用 2 楼 KanzakiOrange 的回复:
SELECT UserID, SUM(CASE WHEN dayid IN (1,2) THEN 1 ELSE 0 END) AS a1, SUM(CASE WHEN dayid IN (3,4) THEN 1 ELSE 0 END) AS a1, SUM(CASE WHEN dayid IN (5,6) THEN 1 ELSE 0 END) AS a1, ... SUM(CASE WHEN dayid IN (101,102) THEN 1 ELSE 0 END) AS a51 FROM table1 WHERE UserID = 1 GROUP BY UserID
不太好使,我是要sum(num) 字段的和,这个语句是统计行数的哦。 我原始语句是 select SUM(num1) + sum(num2) as a1 FROM table1 where dayid IN (1,2) AND UserID = 1
Ginnnnnnnn 2016-08-09
  • 打赏
  • 举报
回复
SELECT UserID, SUM(CASE WHEN dayid IN (1,2) THEN 1 ELSE 0 END) AS a1, SUM(CASE WHEN dayid IN (3,4) THEN 1 ELSE 0 END) AS a1, SUM(CASE WHEN dayid IN (5,6) THEN 1 ELSE 0 END) AS a1, ... SUM(CASE WHEN dayid IN (101,102) THEN 1 ELSE 0 END) AS a51 FROM table1 WHERE UserID = 1 GROUP BY UserID
shoppo0505 2016-08-09
  • 打赏
  • 举报
回复
select 语句1, 语句2, 语句3, ......

22,206

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

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