我正在尝试运行一个查询，根据处理这些类型问题的可响应性团队获取数字。

Question

SELECT CASE

    WHEN Workgroup = 'Group 1' THEN 'Computers'
    WHEN Workgroup = 'Group 2' THEN 'Laptops'
    WHEN Workgroup = 'Group 3' THEN 'Printers'
    WHEN Workgroup = 'Group 4' THEN 'Scanners'
    WHEN Workgroup = 'Group 5' THEN 'Servers'
    WHEN Workgroup = 'Group 6' THEN 'Routers'
    WHEN Workgroup = 'Group 7' THEN 'Switches'

ELSE 'UNKNOWN Workgroup'

END as Workgroup

,COUNT(*) FROM MASTERTABLE1

WHERE Status = 'OPEN'


AND Workgroup IN ('Group 1','Group 2')
OR Workgroup IN ('Group 3','Group 4')
OR Workgroup IN ('Group 5')
OR Workgroup IN ('Group 6','Group 7')

==================

Team 1 handles Group 1 and Group 2.
Team 2 handles Group 3 and Group 4.
Team 3 handles Group 5
Team 4 handles Group 6 and Group 7.

如何根据选定的团队运行此查询？

如果我想为Team 4运行一个查询，是否有一种方法将其放入查询中？

Answer 1

假设一个变量和/或一个参数来选择团队.

declare @team tinyint
set @team = 4

SELECT CASE
       WHEN Workgroup = 'Group 1' THEN 'Computers'
       WHEN Workgroup = 'Group 2' THEN 'Laptops'
       WHEN Workgroup = 'Group 3' THEN 'Printers'
       WHEN Workgroup = 'Group 4' THEN 'Scanners'
       WHEN Workgroup = 'Group 5' THEN 'Servers'
       WHEN Workgroup = 'Group 6' THEN 'Routers'
       WHEN Workgroup = 'Group 7' THEN 'Switches'
       ELSE 'UNKNOWN Workgroup'
END as Workgroup,
COUNT(*)
FROM MASTERTABLE1
WHERE Status = 'OPEN'
AND ((@team = 1 AND Workgroup IN ('Group 1','Group 2')) OR 
     (@team = 2 AND Workgroup IN ('Group 3','Group 4')) OR
     (@team = 3 AND Workgroup IN ('Group 5')) OR
     (@team = 4 AND Workgroup IN ('Group 6','Group 7')))
GROUP BY Workgroup

正如注释中提到的，您缺少了一个GROUP BY子句和一些括号。