一对多查询,选择所有父母和每个父母的独生子女 [英] one-to-many query selecting all parents and single top child for each parent
问题描述
有两个SQL表:
Parents:
+--+---------+
|id| text |
+--+---------+
| 1| Blah |
| 2| Blah2 |
| 3| Blah3 |
+--+---------+
Childs
+--+------+-------+
|id|parent|feature|
+--+------+-------+
| 1| 1 | 123 |
| 2| 1 | 35 |
| 3| 2 | 15 |
+--+------+-------+
我想通过单个查询选择Parents表中的每一行,并选择Childs表中具有关系"parent"-"id"值和最大"feature"列值的每一行.在此示例中,结果应为:
I want to select with single query every row from Parents table and for each one single row from Childs table with relation "parent"-"id" value and the greatest "feature" column value. In this example result should be:
+----+------+----+--------+---------+
|p.id|p.text|c.id|c.parent|c.feature|
+----+------+----+--------+---------+
| 1 | Blah | 1 | 1 | 123 |
| 2 | Blah2| 3 | 2 | 15 |
| 3 | Blah3|null| null | null |
+----+------+----+--------+---------+
其中p =父表,c =子表
Where p = Parent table and c = Child table
我试图离开OUTER JOIN和GROUP BY,但是MSSQL Express告诉我,使用GROUP BY进行查询需要在每个非Groupped字段上使用聚合函数.而且我不想将它们全部分组,而是选择第一行(具有自定义顺序).
I tried to LEFT OUTER JOIN and GROUP BY but MSSQL Express told me that query with GROUP BY require Aggregate functions on every non-Groupped fields. And I do not want to Group them all, but rather select top row (with custom ordering).
我完全没有主意...
I am totally out of ideas...
推荐答案
select p.id, p.text, c.id, c.parent, c.feature
from Parents p
left join (select c1.id, c1.parent, c1.feature
from Childs c1
join (select p1.id, max(c2.feature) maxFeature
from Parents p1
left join Childs c2 on p1.id = c2.parent
group by p1.id) cf on c1.parent = cf.id
and c1.feature = cf.maxFeature) c
on p.id = c.parent
这篇关于一对多查询,选择所有父母和每个父母的独生子女的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!