一对多查询，为每个父项选择所有父项和单个顶层子项
一对多查询，为每个父项选择所有父项和单个顶层子项
提问于 2009-10-13 21:54:37
有两个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”列值的每一行。在此示例中，结果应为：
+----+------+----+--------+---------+
|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=子表
我试图保留OUTER JOIN和GROUP BY，但MSSQL Express告诉我，GROUP BY查询需要在每个非分组字段上使用聚合函数。我不想将它们全部分组，而是选择顶行(具有自定义排序)。
我完全没主意了.
回答 5
Stack Overflow用户
回答已采纳
发布于 2009-10-13 22:00:22
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.parentStack Overflow用户
发布于 2009-10-14 03:03:12
使用CTE (SQL Server 2005+)：
WITH max_feature AS (
SELECT c.id,
c.parent,
MAX(c.feature) 'feature'
FROM CHILD c
GROUP BY c.id, c.parent)
SELECT p.id,
p.text,
mf.id,
mf.parent,
mf.feature
FROM PARENT p
LEFT JOIN max_feature mf ON mf.parent = p.id非CTE等效项：
SELECT p.id,
p.text,
mf.id,
mf.parent,
mf.feature
FROM PARENT p
LEFT JOIN (SELECT c.id,
c.parent,
MAX(c.feature) 'feature'
FROM CHILD c
GROUP BY c.id, c.parent) mf ON mf.parent = p.id您的问题缺少处理平局中断的详细信息(当2+ CHILD.id值具有相同的特征值时)。Agent9191的答案使用TOP 1，但它将采用返回的第一个值&不一定是您想要的值。
Stack Overflow用户
发布于 2009-10-13 22:03:56
这应该是可行的：
SELECT p.id, p.text, c.id, c.parent,c.feature
FROM parent p
LEFT OUTER JOIN (SELECT TOP 1 child.id,
child.parent,
MAX(child.feature)
FROM child
WHERE child.parent = p.id
GROUP BY child.id, child.parent
) c ON p.id = c.parent页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/1563148
复制相关文章
相似问题