分组依据根据三个表
分组依据根据三个表
提问于 2011-06-21 13:43:49
我有三张桌子
tbl_worker
City WorkerName Job
10 Raymond Carpenter
10 Sara Programmer
11 John Painter
11 Alice Booker
tbl_workshop
City WorkshopName
10 Dell
11 Apple
10 Sony
Tbl_city
Id Name
10 Mexico
11 Sydney现在我想要这样的结果：
City WorkersQty WorkshopQry
Mexico 2 2
Sydney 2 1结果返回在不同城市注册的工人和车间的数量。我应该写什么查询？
回答 2
Stack Overflow用户
回答已采纳
发布于 2011-06-21 13:47:28
分别对tbl_workshop和tbl_worker表进行分组，然后使用LEFT JOIN获取结果：
SELECT c.City,
k.Cnt AS WorkersQty,
w.Cnt AS WorkshopQry
FROM Tbl_city c
LEFT JOIN (
SELECT City, COUNT(*) Cnt
FROM tbl_worker
GROUP BY City
) k ON c.ID = k.City
LEFT JOIN (
SELECT City, COUNT(*) Cnt
FROM tbl_workshop
GROUP BY City
) w ON c.ID = w.City如果只想显示有车间或工人的城市，请添加：
WHERE k.Cnt > 0 OR w.Cnt > 0Stack Overflow用户
发布于 2011-06-21 15:59:26
使用Dense_rank获取车间或工人的数量
SELECT city, MAX(WorkshopQty) AS WorkshopQty , MAX(WorkersQty) AS WorkersQty
FROM (
SELECT c.name AS City,
DENSE_RANK() OVER (PARTITION BY c.id ORDER BY WorkshopName) AS WorkshopQty,
DENSE_RANK() OVER (PARTITION BY c.id ORDER BY WorkerName) AS WorkersQty
FROM tbl_city c
INNER JOIN tbl_workshop s ON s.city = c.id
INNER JOIN tbl_worker w ON w.city = c.id
) s
GROUP BY City页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/6420855
复制相关文章
相似问题
腾讯云开发者
