将在单个查询中工作的两段SQL代码组合在一起。学习SQL Server
将在单个查询中工作的两段SQL代码组合在一起。学习SQL Server
提问于 2019-06-25 23:06:01
我正在编写一个查询，以组合来自两个表的数据，但从一个表中，我希望数据由不同的组输出
尝试连接、分组集、子查询、Union All
查询:提取每个级别的每个位置的平均成本(每个位置的招聘成本总和)每个位置的招聘人数/number*每个级别的每个位置的招聘人数
我正在努力组合两个查询-我想知道你是否可以帮助我。
表是
招聘：
Location Hiring Start Date Hiring Complete Date Grade
United Kingdom 08/08/2018 29/12/2018 C
United Kingdom 24/10/2018 29/12/2018 B
United Kingdom 24/10/2018 05/01/2019 B
United Kingdom 04/05/2018 15/01/2019 B
United States 16/08/2018 09/02/2019 D
India 09/08/2018 02/03/2019 B
India 15/12/2018 02/03/2019 B
United Kingdom 22/12/2018 02/03/2019 C
United Kingdom 26/12/2018 02/03/2019 B
United Kingdom 26/12/2018 02/03/2019 B
United Kingdom 07/01/2019 02/03/2019 A
United States 06/11/2018 03/03/2019 D
United States 23/01/2019 03/03/2019 AHireCosts：
Location Item Cost
United Kingdom Advertisement 640
United Kingdom Recruiter 926
United Kingdom Interviews 833
United Kingdom Admin 211
United States Advertisement 540
United States Recruiter 1122
United States Interviews 934
United States Admin 192
India Advertisement 211
India Recruiter 543
India Interviews 321
India Admin 102下面是两段单独工作的代码，但我想将它们组合成一段代码
SELECT COUNT(*) AS HiresperLocationGrade,
Location,
Grade,
(SELECT SUM(Cost) AS HireCost
FROM HireCosts
WHERE Location = Hiring.Location) AS RecruitingCostsperLocation
FROM Hiring
GROUP BY Location,
Grade;这会正确地给出我的位置、等级、HiresperLocationperGrade、RecruitingCostsperlocation
SELECT COUNT(*) AS HiresperLocation
FROM Hiring
GROUP BY Location;这正确地给出了Hiresperlocation
我的问题是，从聘用表中，我希望每个职位每个职级的招聘人数，以及每个职位的招聘人数，我希望输出位置，职级，招聘地点，HiresperLocation，RecruitingCostsperLocation
+--------------------------+----------------+-------+-----------------+------------------+
| HiresperlocationperGrade | Location | Grade | RecruitingCosts | Hiresperlocation |
+--------------------------+----------------+-------+-----------------+------------------+
| 2 | India | A | 1177 | 28 |
| 20 | India | B | 1177 | 28 |
| 4 | India | C | 1177 | 28 |
| 2 | India | D | 1177 | 28 |
| 17 | United Kingdom | A | 2610 | 109 |
| 44 | United Kingdom | B | 2610 | 109 |
| 24 | United Kingdom | C | 2610 | 109 |
| 15 | United Kingdom | D | 2610 | 109 |
| 8 | United Kingdom | E | 2610 | 109 |
| 1 | United Kingdom | F | 2610 | 109 |
| 10 | United States | A | 2788 | 33 |
| 4 | United States | B | 2788 | 33 |
| 3 | United States | C | 2788 | 33 |
| 11 | United States | D | 2788 | 33 |
| 3 | United States | E | 2788 | 33 |
| 2 | United States | F | 2788 | 33 |
+--------------------------+----------------+-------+-----------------+------------------+回答 1
Stack Overflow用户
回答已采纳
发布于 2019-06-26 00:31:11
下面是对子查询使用左连接解决方案：
SELECT COUNT(*) AS HiresperLocationGrade,
H.Location,
Grade,
(
SELECT SUM(Cost) AS HireCost
FROM HireCosts
WHERE Location = H.Location
) AS RecruitingCostsperLocation,
HiresperLocation
FROM Hiring H
LEFT JOIN
(
SELECT Location,
COUNT(*) AS HiresperLocation
FROM HireCosts
GROUP BY Location
) HL ON HL.Location = H.Location
GROUP BY H.Location,
Grade,
HiresperLocation;页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/56757052
复制相关文章
相似问题
腾讯云开发者
