是否可以简化SQL查询？
是否可以简化SQL查询？
提问于 2017-11-16 19:29:10
我认为需要计数取决于不同的价值观。我在用这个。我能简化一下吗。
SELECT
[FC].[Name] AS [JobCategory],
(SELECT COUNT(*)
FROM [dbo].[JobCandidateHire] AS [JCH]
LEFT JOIN [dbo].[Job] AS [J] ON [J].[ID]=[JCH].[JobID]
LEFT JOIN [dbo].[Candidate] AS [C] ON [C].[ID]=[JCH].[CandidateID]
LEFT JOIN [dbo].[JobCandidateHireLevel] AS [JCHL] ON [JCHL].[ID]=[JCH].[JobCandidateHireLevelID]
WHERE [JCHL].[LevelType]=5 AND [J].[FunctionalCategoryID] =[FC].[ID] AND [C].[EthnicityID]=5 AND [C].[GenderID]=2
) AS [HMale],
(SELECT COUNT(*)
FROM [dbo].[JobCandidateHire] AS [JCH]
LEFT JOIN [dbo].[Job] AS [J] ON [J].[ID]=[JCH].[JobID]
LEFT JOIN [dbo].[Candidate] AS [C] ON [C].[ID]=[JCH].[CandidateID]
LEFT JOIN [dbo].[JobCandidateHireLevel] AS [JCHL] ON [JCHL].[ID]=[JCH].[JobCandidateHireLevelID]
WHERE [JCHL].[LevelType]=5 AND [J].[FunctionalCategoryID] =[FC].[ID] AND [C].[EthnicityID]=2 AND [C].[GenderID]=3
) AS [AmericanIndianFemale],
(SELECT COUNT(*)
FROM [dbo].[JobCandidateHire] AS [JCH]
LEFT JOIN [dbo].[Job] AS [J] ON [J].[ID]=[JCH].[JobID]
LEFT JOIN [dbo].[Candidate] AS [C] ON [C].[ID]=[JCH].[CandidateID]
LEFT JOIN [dbo].[JobCandidateHireLevel] AS [JCHL] ON [JCHL].[ID]=[JCH].[JobCandidateHireLevelID]
WHERE [JCHL].[LevelType]=5 AND [J].[FunctionalCategoryID] =[FC].[ID] AND [C].[EthnicityID]=7 AND [C].[GenderID]=3
) AS [TwoMoreRaceFemale],
(SELECT COUNT(*)
FROM [dbo].[JobCandidateHire] AS [JCH]
LEFT JOIN [dbo].[Job] AS [J] ON [J].[ID]=[JCH].[JobID]
LEFT JOIN [dbo].[Candidate] AS [C] ON [C].[ID]=[JCH].[CandidateID]
LEFT JOIN [dbo].[JobCandidateHireLevel] AS [JCHL] ON [JCHL].[ID]=[JCH].[JobCandidateHireLevelID]
WHERE [JCHL].[LevelType]=5 AND [J].[FunctionalCategoryID] = [FC].[ID]
) AS [CategoryTotal],
[FC].[IsArchived]
FROM [dbo].[FunctionalCategory] AS [FC]这里，连接对于子查询是相同的，只是条件不同的地方。
回答 1
Stack Overflow用户
回答已采纳
发布于 2017-11-18 03:40:54
下面的查询在语法上是可行的，但是没有任何模式或样本数据提供，这是对更简单查询的最佳估计。
select fc.name as JobCateogry
, sum(case when jchl.LevelType = 5 and c.EthnicityID = 5 and c.GenderID = 2 then 1 else 0 end) as HMale
, sum(case when jchl.LevelType = 5 and c.EthnicityID = 2 and c.GenderID = 3 then 1 else 0 end) as AmericanIndianFemale
, sum(case when jchl.LevelType = 5 and c.EthnicityID = 7 and c.GenderID = 3 then 1 else 0 end) as TwoMoreRaceFemale
, count(*) as CategoryTotal
, fc.IsArchived
from dbo.FunctionalCategory as fc
left join dbo.Job as j on fc.ID = j.FunctionalCategoryID
left join dbo.JobCandidateHire as jch on j.ID = jch.JobID
left join dbo.Candidate as c on c.id = jch.CandidateID
left join dbo.JobCandidateHireLevel as jchl on jchl.ID = jch.JobCandidateHireLevelID
group by fc.name
, fc.IsArchived页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/47337643
复制相关文章
相似问题
腾讯云开发者
