sql - 如何计算 PostgreSQL 中所有归属于 - 及其子级的实体？

Question

我有一个棘手的问题让我很困惑。基本上，我在一个表、一个部件表和两个parts_types 之间的连接表中有四种主要类型的车辆（粘贴在下面）。

我遇到问题的地方是试图计算 - 顶级中的所有部件 - 和子（参见下面的 parent_id）车辆类型。

http://sqlfiddle.com/#!15/6c4240/12

我的查询：

SELECT rt.root_id AS root_id, 
       rt.name AS root_name, 
       COUNT(tp.part_id) part_count
FROM types AS pt 
    LEFT OUTER JOIN types AS t ON pt.id = t.parent_id
    JOIN types_parts      AS tp ON tp.type_id = pt.id 
    JOIN parts            AS p  ON p.id = tp.part_id
    JOIN (SELECT id AS root_id, 
               name, 
               parent_id
          FROM 
               types 
          ) AS rt ON rt.root_id = pt.parent_id 
GROUP BY 1,2
ORDER BY 3 DESC

不需要的输出：

+---------+-------------+------------+
| root_id | root_name   | part_count | 
+---------+-------------+------------+
| 2       | Automobiles | 12         |
+---------+-------------+------------+
| 8       | Trucks      | 8          | 
+---------+-------------+------------+
| 3       | Boats       | 5          |
+---------+-------------+------------+
| 7       | Cars        | 5          | 
+---------+-------------+------------+

上面有两个主要问题：

• 汽车和卡车应归入汽车。
• 即使有属于飞机的部件，也没有列出飞机。我认为这是因为飞机没有像汽车和船这样的孩子。
• 这更像是一个奖励，但没有列出“无人机”，因为没有分配给无人机的部件。了解如何仍将其包含在此报告中会很有趣，但我不想减慢查询速度或使其过于复杂。

期望的输出：

+---------+-------------+------------+
| root_id | root_name   | part_count | 
+---------+-------------+------------+
| 2       | Automobiles | 12         | 
+---------+-------------+------------+
| 3       | Boats       | 8          | 
+---------+-------------+------------+
| 1       | Airplanes   | 3          |  
+---------+-------------+------------+
| 4       | Drones      | 0          |  
+---------+-------------+------------+

（上表中的part_count不准确）

类型表：

+----+-------------+-----------+--------------------------------+
| id | name        | parent_id | path                           |
+----+-------------+-----------+--------------------------------+
| 1  | Airplanes   | NULL      | /Airplanes                     |
+----+-------------+-----------+--------------------------------+
| 2  | Automobiles | NULL      | /Automobiles                   |
+----+-------------+-----------+--------------------------------+
| 3  | Boats       | NULL      | /Boats                         |
+----+-------------+-----------+--------------------------------+
| 4  | Drones      | NULL      | /Drones                        |
+----+-------------+-----------+--------------------------------+
| 5  | Tugboats    | 3         | /Boats/Tugboats                |
+----+-------------+-----------+--------------------------------+
| 6  | Battleship  | 2         | /Boats/Battleship              |
+----+-------------+-----------+--------------------------------+
| 7  | Cars        | 2         | /Automobiles/Cars              |
+----+-------------+-----------+--------------------------------+
| 8  | Trucks      | 2         | /Automobiles/Trucks            |
+----+-------------+-----------+--------------------------------+
| 9  | Convertible | 7         | /Automobiles/Cars/Convertible  |
+----+-------------+-----------+--------------------------------+
| 10 | Hatchback   | 7         | /Automobiles/Cars/Hatchback    |
+----+-------------+-----------+--------------------------------+
| 11 | Pickup      | 8         | /Automobiles/Trucks/Pickup     |
+----+-------------+-----------+--------------------------------+
| 12 | Dump Truck  | 8         | /Automobiles/Trucks/Dump Truck |
+----+-------------+-----------+--------------------------------+

零件表：

+----+--------------+
| id | name         |
+----+--------------+
| 1  | Wheels       |
+----+--------------+
| 2  | Wing         |
+----+--------------+
| 3  | Windshield   |
+----+--------------+
| 4  | Door         |
+----+--------------+
| 5  | Rims         |
+----+--------------+
| 6  | Tires        |
+----+--------------+
| 7  | Spark plugs  |
+----+--------------+
| 8  | Lifeboat     |
+----+--------------+
| 9  | Life jackets |
+----+--------------+
| 10 | Tailgate     |
+----+--------------+
| 11 | Giant tires  |
+----+--------------+
| 12 | Big guns     |
+----+--------------+
| 13 | Propeller    |
+----+--------------+
| 14 | Landing gear |
+----+--------------+

Types_Parts 表：

+---------+---------+-------+
| type_id | part_id | price |
+---------+---------+-------+
| 1       | 14      | 100   |
+---------+---------+-------+
| 1       | 13      | 200   |
+---------+---------+-------+
| 1       | 2       | 150   |
+---------+---------+-------+
| 7       | 1       | 300   |
+---------+---------+-------+
| 7       | 3       | 100   |
+---------+---------+-------+
| 7       | 4       | 200   |
+---------+---------+-------+
| 7       | 5       | 400   |
+---------+---------+-------+
| 7       | 6       | 100   |
+---------+---------+-------+
| 7       | 7       | 125   |
+---------+---------+-------+
| 5       | 6       | 300   |
+---------+---------+-------+
| 5       | 9       | 100   |
+---------+---------+-------+
| 6       | 8       | 200   |
+---------+---------+-------+
| 6       | 9       | 400   |
+---------+---------+-------+
| 6       | 12      | 350   |
+---------+---------+-------+
| 12      | 10      | 100   |
+---------+---------+-------+
| 12      | 11      | 110   |
+---------+---------+-------+
| 11      | 10      | 75    |
+---------+---------+-------+
| 11      | 1       | 100   |
+---------+---------+-------+
| 11      | 3       | 120   |
+---------+---------+-------+
| 11      | 4       | 160   |
+---------+---------+-------+
| 11      | 5       | 200   |
+---------+---------+-------+
| 11      | 6       | 240   |
+---------+---------+-------+
| 10      | 1       | 120   |
+---------+---------+-------+
| 10      | 3       | 160   |
+---------+---------+-------+
| 10      | 4       | 200   |
+---------+---------+-------+
| 10      | 5       | 240   |
+---------+---------+-------+
| 10      | 6       | 280   |
+---------+---------+-------+

标签： sqlpostgresqlaggregate-functions