例如:
表1-项目
Id | 名称 | 主项目活动 | ||
---|---|---|---|---|
1 | 项目A | |||
2 | 项目A-1 | 1 | Y||
3 | 项目A-2 | 1 | N | |
4 | 项目B | N | ||
5 | 项目B-1 | 4 | N | |
6 | 项目C | |||
7 | 项目C-1 | 6 | Y |
以下是我使用带有sum(case-when)的内部联接表的解决方案
id | 名称 | master_project活动 | ||
---|---|---|---|---|
1 | 项目A | |||
2 | 项目A-1 | 1 | Y||
3 | 项目A-2 | 1 | N | |
4 | 项目B | N | ||
5 | 项目B-1 | 4 | N | |
6 | 项目C | |||
7 | 项目C-1 | 6 | Y |
;WITH MasterProjects as
(
SELECT
Name,
CASE
WHEN (SELECT COUNT(1) FROM Table1 t2 WHERE t2.Name LIKE t1.Name + '%' AND Active = 'Y') > 0 THEN 'Y'
ELSE 'N' END AS Active
FROM Table1 t1
WHERE MasterProject = ''
)
SELECT t1.Id, t1.Name, t1.MasterProject, t2.Active
FROM Table1 t1
JOIN MasterProjects t2 ON t1.Name LIKE t2.Name + '%'
我假设isActive对于子项目不是null