合并两个表中的行/列-JOIN子句

Question

在链接中查看我的SQLFIDDLE并查看b

CREATE TABLE Projects
(`p_id` int, `project_title` varchar(9), `l_id` int);
INSERT INTO Projects
(`p_id`, `project_title`, `l_id`)
VALUES
(1, 'A', 6),
(2, 'B', 6),
(3, 'C', 7),
(4, 'D', 8),
(5, 'E', 9),
(6, 'F', 10);
CREATE TABLE Locations
(`l_id` int, `title` varchar(9), `parent_id` int );
INSERT INTO Locations
(`l_id`, `title`, `parent_id`)
VALUES
(1, 'Country', 0),
(2, 'District1', 1),
(3, 'District2', 1),
(4, 'District3', 1),
(5, 'District4', 1),
(6, 'Loc 5', 2),
(7, 'Loc 6', 3),
(8, 'Loc 7', 3),
(9, 'Loc 8', 4),
(10, 'Loc 9', 4),
(11, 'Loc 10', 4),
(12, 'Loc 11', 5);

我想实现这一点：

+------+-----------+-------------+
| L_ID | Title     | count P_ID  | 
+------+-----------+-------------+
|  2   | District1 |    2        | 
|  3   | District2 |    2        | 
|  4   | District3 |    2        | 
|  5   | District4 |    0        | 
+----+------------+------+-------+

我试过使用INNER JOIN，LEFT OUTER JOIN。我所能达到的只是下面这样，对我没有帮助：

+------+-----------+----------------------+
| L_ID | Title     | parent_id  | counted |
+------+-----------+------------+---------+
|  6   | Loc 5     |    2       |   2     | 
|  7   | Loc 6     |    3       |   2     | 
|  9   | Loc 8     |    4       |   2     | 
+---- -+-----------+------------+---------+

位置表是嵌套的，如果这很重要的话。我需要计算每个地区的项目，并获得地区名称。

我试过了：

SELECT l.*, COUNT(p.l_id) AS thecounted 
FROM locations l 
INNER JOIN  projects p  ON p.l_id = l.l_id  
GROUP BY l.parent_id

和

SELECT l.*, COUNT(p.l_id) AS thecounted 
FROM locations l 
LEFT OUTER JOIN projects p on l.l_id = p.l_id 
GROUP BY l.parent_id

Answer 1

考虑两个联接：

select d.l_id, d.title, count(p.l_id) count_p_id
from locations d
left join locations l on l.parent_id = d.l_id
left join projects  p on p.l_id      = l.l_id
where d.parent_id = 0
group by d.l_id, d.title

查询从地区列表(d(开始，其父级是0。然后，它向下一级到达位置(l(，并查找相应的项目(p(。最后一步是聚合。

Answer 2

GMB的解决方案返回此1行

l_id    title       count_p_id
1       Country     0

使用此脚本版本

select d.l_id, d.title, count(p.l_id) count_p_id
from locations d
left join locations l on l.parent_id = d.l_id
left join projects  p on p.l_id      = l.l_id
where d.parent_id = 0
group by d.l_id, d.title

我们用稍微修正的条件得到了期望的结果

where d.parent_id = 1

结果：

l_id    title       count_p_id
2       District1   2
3       District2   2
4       District3   2
5       District4   0

很抱歉发布答案，而不是简单的评论，这就足够了，但还没有足够的信誉