MS SQL Server 2019：获取相同master_id的前后行值SQL实现

算力阿峰

2026-8-16

SQL查询实现任务工时按员工+项目分组转列

原始表格结构及数据

假设你的原始表名为TaskHours，数据如下：

EmployeeID	ProjectID	TaskID	TaskName	Hours
1	101	1	Design	5
1	101	2	Coding	10
1	102	1	Design	7
2	101	2	Coding	8
2	102	1	Design	6
2	102	2	Coding	12

期望结果

按EmployeeID和ProjectID分组，将不同TaskName作为列展示工时，并汇总总工时：

EmployeeID	ProjectID	Design	Coding	TotalHours
1	101	5	10	15
1	102	7	0	7
2	101	0	8	8
2	102	6	12	18

实现SQL语句

使用条件聚合即可完成，兼容大多数SQL数据库（MySQL、SQL Server、PostgreSQL等）：

SELECT
    EmployeeID,
    ProjectID,
    SUM(CASE WHEN TaskName = 'Design' THEN Hours ELSE 0 END) AS Design,
    SUM(CASE WHEN TaskName = 'Coding' THEN Hours ELSE 0 END) AS Coding,
    SUM(Hours) AS TotalHours
FROM TaskHours
GROUP BY EmployeeID, ProjectID
ORDER BY EmployeeID, ProjectID;