我有一个表,其中包含Job,City和PersonName (在该城市工作的人)。我想归还所有工作和城市,即使在那个城市没有人在工作。这是一个简单的例子:
表格的创建:
CREATE TABLE #Data
(
Job varchar(50),
City varchar(50),
PersonName varchar(50)
)
INSERT INTO #Data
VALUES
('Teacher', 'New-York', 'John')
,('Teacher', 'Los-Angeles', 'Mary')
,('Fireman', 'New-York', 'Sam')
我的查询
SELECT
[Job]
, [City]
,COUNT(PersonName) AS NumberOfPeopleWorkingThere
FROM #Data
GROUP BY [Job], [City]
ORDER BY [Job], [City]
我的结果
Fireman New-York 1
Teacher Los-Angeles 1
Teacher New-York 1
但我想拥有:
Fireman Los-Angeles 0 -> this row in addition
Fireman New-York 1
Teacher Los-Angeles 1
Teacher New-York 1
我不知道如何执行此操作,请您能帮我吗?
这回答了问题的原始版本。
使用cross join
生成行,然后left join
:
SELECT j.job, c.city,
COUNT(PersonName) AS NumberOfPeopleWorkingThere
FROM (SELECT DISTINCT job FROM #Data) j CROSS JOIN
(SELECT DISTINCT city FROM #Data) c LEFT JOIN
#Data d
ON d.job = j.job AND d.city = c.city
GROUP BY j.job, c.city
ORDER BY j.job, c.city;
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句