只返回多个重复父id的子表上最后插入的父id

Question

我正在尝试获取子表上最后插入的父id的值，然后加入祖父母的表，以获得财产和他们的总数。

这是我的表格结构：

parents
+------------
|pid | item |
+----+------+
| 1  | ite1 |
| 2  | ite2 |
+-----------+
childs
+-------------+
| cid  | pid  |
+------+------+
| 1    | 1    | -- not
| 2    | 1    | ---- row to be selected(last inserted to join grandparents)
| 3    | 2    | -- not 
| 4    | 2    | ---- row to be selected(last inserted to join grandparents)
+-------------+
grandparents
+----------------------+
| gid | cid | property |
+-----+-----+----------+
| 1   | 1   | 1200     |
| 2   | 1   | 1500     |
| 3   | 2   | 101      |
| 4   | 2   | 303      |
| 5   | 3   | 600      |
| 6   | 3   | 10       |
| 7   | 4   | 335      |
| 8   | 4   | 250      |
+----------------------+

结果

+----------------------------+
| item   | cid  |  property  |
+--------+------+------------+
| ite1   | 2    | 101        |
| ite1   | 2    | 303        |
| ite1   | 4    | 335        |
| ite1   | 4    | 250        |
+----------------------------+
Total property results : sum(101 + 303 +335 + 250) =   989

我尝试了这个查询，但返回/包括导致全祖父母合计表

query:
SELECT g.property from grandparents g
join childs c on g.cid = c.cid
join parents p on c.pid = p.pid
where c.pid in (select DISTINCT pid from childs) and c.pid = 1 

Answer 1

您可以尝试使用子查询来获取MAX(cid)，然后通过子查询的cid来执行JOIN

SELECT g.property 
from grandparents g
join (
SELECT pid ,MAX(cid) cid
FROM childs 
GROUP BY pid  
) c on g.cid = c.cid
join parents p on c.pid = p.pid