SQL -分组依据筛选器
SQL -分组依据筛选器
提问于 2018-12-05 02:55:44
因此，我有一个查询来提取一个>1的列的总和，并按几个用户对某些部门进行分组，如下所示：
SELECT
t.DEPARTMENT_ID
,SUM(CASE WHEN t.VALUE > 1 THEN 1 ELSE 0 END) AS Results
FROM TABLE t
WHERE t.USER_ID = 000
OR t.DEPARTMENT ID = 1001
AND t.USER_ID IN
('01'
,'02'
)
OR t.DEPARTMENT_ID = 1002
AND t.USER_ID IN
('02'
,'03'
)
GROUP BY t.DEPARTMENT ID结果如下所示：
DEPARTMENT_ID Results
1001 5
1002 7现在，这是可行的，因为在该列中指定了多个部门，而我只是按它分组。
但是，如果我想创建自己的团队(参见笔记中的团队--)并按团队领导分组，该怎么办？例如：
SELECT
t.DEPARTMENT_ID
,SUM(CASE WHEN t.VALUE > 1 THEN 1 ELSE 0 END)
FROM TABLE t
WHERE t.USER_ID = 000
--Team_1
OR t.DEPARTMENT ID = 1001
AND t.USER_ID IN
('01'
,'02'
)
OR t.DEPARTMENT_ID = 1002
AND t.USER_ID IN
('02'
,'03'
)
-- Team_2
OR t.DEPARTMENT_ID = 1003
AND t.USER_ID IN
('05'
,'06'
)我希望结果如下所示：
TEAMS RESULTS
Team_1 6
Team_2 4如何按团队分组？
回答 3
Stack Overflow用户
回答已采纳
发布于 2018-12-05 03:08:26
在select & group by中使用case表达式，并从select & group by中删除department_ID。
SELECT CASE WHEN t.DEPARTMENT ID = 1001 AND t.USER_ID IN ('01','02')
OR t.DEPARTMENT_ID = 1002 AND t.USER_ID IN ('02','03') then 'Team1'
WHEN t.DEPARTMENT_ID = 1003 AND t.USER_ID IN ('05','06') then 'Team2' END as Team
, SUM(CASE WHEN t.VALUE > 1 THEN 1 ELSE 0 END) as Results
FROM TABLE t
WHERE t.USER_ID = 000
--Team_1
OR (t.DEPARTMENT ID = 1001
AND t.USER_ID IN ('01','02')
OR t.DEPARTMENT_ID = 1002
AND t.USER_ID IN ('02','03'))
-- Team_2
OR (t.DEPARTMENT_ID = 1003
AND t.USER_ID IN ('05','06'))
GROUP BY CASE WHEN t.DEPARTMENT ID = 1001 AND t.USER_ID IN ('01','02')
OR t.DEPARTMENT_ID = 1002 AND t.USER_ID IN ('02','03') then 'Team1'
WHEN t.DEPARTMENT_ID = 1003 AND t.USER_ID IN ('05','06') then 'Team2' END根据团队注释在where子句中猜测()以获得所需的结果。
Stack Overflow用户
发布于 2018-12-05 03:05:40
用例语句：
select case
when t.DEPARTMENT_ID = 1001 and t.USER_ID in ('01', '02') then 'Team 1'
when t.DEPARTMENT_ID = 1002 and t.USER_ID in ('02', '03') then 'Team 2'
when t.DEPARTMENT_ID = 1003 and t.USER_ID in ('05', '06') then 'Team 3'
end as Teams,
sum(case when t.value > 1 then 1 else 0 end)
from TABLE t
group by
case when t.DEPARTMENT_ID = 1001 and t.USER_ID in ('01', '02') then 'Team 1'
when t.DEPARTMENT_ID = 1002 and t.USER_ID in ('02', '03') then 'Team 2'
when t.DEPARTMENT_ID = 1003 and t.USER_ID in ('05', '06') then 'Team 3'
endStack Overflow用户
发布于 2018-12-05 04:16:24
您可以构建一个VALUES()列表并将其加入其中。比重复自己三次简单得多：
SELECT teams.name, SUM(CASE WHEN t.VALUE > 1 THEN 1 ELSE 0 END) AS Results
FROM (VALUES
('team 1', 1001, 1),
('team 1', 1001, 2),
('team 2', 1002, 3),
('team 2', 1002, 4),
('team 3', 1001, 5),
('team 3', 1001, 6)
) AS teams(name, deptid, userid)
INNER JOIN t ON teams.deptid = t.DEPARTMENT_ID AND teams.userid = t.USER_ID
GROUP BY teams.name页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/53619669
复制相关文章
相似问题