sql - SELECT 中但在一个 View 上有多个 SUM

Question

我编写了一个 select 语句来协助时间表审批。它显示根据预订的不同角色针对用户输入的小时数总和，即标准办公时间、加类时间、假期和离岸时间。

我想要每个[Name]一行，但我为用户拥有数据的每个SUM获取一行。

我需要将其创建为 View 并SELECT 到该 View 吗？

我当前的代码如下:

SELECT 
    TSDEPT_HEAD As [Resource],
    CONCAT (PP_FIRST_NAME,' ',PP_SURNAME) As [Name],    
    SUM(Case WHEN TST_ROLE = 'STD' Then TST_HOURS END) As STD, 
    SUM(Case WHEN TST_ROLE LIKE 'OT%' Then TST_HOURS END) As OT,
    SUM(Case WHEN TST_ROLE LIKE 'OFF%' Then TST_HOURS END) As OFFSHORE,
    SUM(Case WHEN TST_ROLE = 'HOL' Then TST_HOURS END) As HOL
    FROM TS_TIMESHEETS
LEFT JOIN SYS_PEOPLE on TST_RESOURCE = PP_CODE
LEFT JOIN TS_USERS on TST_RESOURCE = TSU_USERID
LEFT JOIN TS_DEPARTMENTS on TSU_DEPARTMENT_CODE = TSDEPT_CODE
WHERE MONTH(TST_DATE) = MONTH(GETDATE())
GROUP BY TSDEPT_NAME,TST_ROLE,PP_FIRST_NAME,PP_SURNAME,TST_RESOURCE,PP_KNOWN_AS,TSDEPT_HEAD

Answer 1

您应该只对您真正想要聚合的列使用分组依据，通常是与 select 中相同的列，但不使用聚合函数

SELECT 
TSDEPT_HEAD As [Resource],
CONCAT (PP_FIRST_NAME,' ',PP_SURNAME) As [Name],    
SUM(Case WHEN TST_ROLE = 'STD' Then TST_HOURS END) As STD, 
SUM(Case WHEN TST_ROLE LIKE 'OT%' Then TST_HOURS END) As OT,
SUM(Case WHEN TST_ROLE LIKE 'OFF%' Then TST_HOURS END) As OFFSHORE,
SUM(Case WHEN TST_ROLE = 'HOL' Then TST_HOURS END) As HOL
FROM TS_TIMESHEETS
LEFT JOIN SYS_PEOPLE on TST_RESOURCE = PP_CODE
LEFT JOIN TS_USERS on TST_RESOURCE = TSU_USERID
LEFT JOIN TS_DEPARTMENTS on TSU_DEPARTMENT_CODE = TSDEPT_CODE
WHERE MONTH(TST_DATE) = MONTH(GETDATE())
GROUP BY     TSDEPT_HEAD, CONCAT (PP_FIRST_NAME,' ',PP_SURNAME)