一对多查询，选择所有父母和每个父母的独生子女 [英] 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屋！