这里有3个表格:
城市(ID PK,名称,邮政编码)
工作(ID PK,标题)
人数(身份证PK,first_name,last_name,job_id FK工作,city_id FK城市)
每个人都有一份工作并住在一个城市,城市和工作都可以至少有 0 人 (0..n)。
我想计算每个工作和城市的人数,而不删除没有人的城市和工作:
> +----------+----------+---------------+
| city_id | job_id | count(p.id) |
+------------+----------+---------------+
| 140 | 1 | 0 |
| 249 | 37 | 1 |
| 249 | 40 | 1 |
| 249 | 269 | 1 |
| 250 | 4823 | 3 |
| 251 | 1 | 4 |
| 251 | 205 | 1 |
| 433 | 1 | 0 |
| 433 | 40 | 1 |
| 433 | 23 | 1 |
| 433 | 1346 | 1 |
| 434 | 5 | 5 |
| 434 | 70 | 1 |
| 434 | 5332 | 1 |
我想查询应该是这样的:
SELECT job_id, city_id, COUNT(p.id)
FROM persons p
???? JOIN jobs j ON p.job_id=j.id
???? JOIN cities c ON p.city_id=c.id
GROUP BY j.id, c.id
我知道使用内部连接无法完成,因为所有不包含连接引用的行都将被忽略。
我尝试了正确的加入,但取决于加入的订单结果并不相同。
您应该从cities
开始查询,然后jobs
,然后计算人数。但是在您的数据库模式中,由于城市和工作没有联系,因此"很难"有效地完成。处理它的最佳方法是像这样更改数据库架构:
cities(id PK, name, zipcode)
jobs(id PK, title, city_id FK cities)
persons(id PK, first_name, last_name, job_id FK jobs)
你可以看到区别 - 一个工作链接到城市,一个人链接到一个工作,而工作又链接到这个工作所在的城市。
如果遵循这种方式,则可以编写如下所示的查询:
select c.id as city_id, j.id as job_id, count(p.id)
from cities c
inner join jobs j on (j.city_id = c.id)
left outer join persons p on p.job_id = j.id
group by c.id, j.id
但是,如果保留现有架构,则需要在城市和作业之间进行交叉联接,这可能会效率低下并产生噪声结果(某个城市中不存在的作业)。您需要此查询:
select c.id as city_id, j.id as job_id, count(p.id)
from cities c
cross join jobs j
left outer join persons p on (p.job_id = j.id) and (j.city_id = c.id)
group by c.id, j.id