因此,我有一个查询来提取一个>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
如何按团队分组?
发布于 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子句中猜测()以获得所需的结果。
发布于 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'
end
发布于 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
https://stackoverflow.com/questions/53619669
复制相似问题