blocks|key|1217348|text|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|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1217349|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>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
</code></pre>

blocks|key|1432211|text|这应该是可行的：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1432212|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|code-block|syntax|javascript|1432213|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

This should work:

<pre><code>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
</code></pre>

blocks|key|1432250|text|使用CTE+(SQL+Server+2005%2B)：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1432251|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|code-block|syntax|javascript|1432252|非CTE等效项：|1432253|+++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|1432254|您的问题缺少处理平局中断的详细信息(当2%2B+CHILD.id值具有相同的特征值时)。Agent9191的答案使用TOP+1，但它将采用返回的第一个值&不一定是您想要的值。|offset|length|style|CODE|1432255|entityMap^0|0|0|0|0|M|8|1K|5|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|U|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|V|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|W|8|@$M|X|N|Y|O|P]|$M|Z|N|10|O|P]]|9|@]|A|$]]|$1|Q|3|-4|5|6|7|11|8|@]|9|@]|A|$]]]|R|$]]

Using CTE (SQL Server 2005+):

<pre><code>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
</code></pre>

Non CTE equivalent:

<pre><code> 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
</code></pre>

Your question lacks details for handling tie breakers (when 2+ <code>CHILD.id</code> values have the same feature value). Agent_9191's answer uses <code>TOP 1</code>, but that will take the first that is returned &amp; not necessarily the one you want.

blocks|key|1432307|text|manji的查询不处理最大功能的平局。这是我的方法，我测试过了：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1432308|;WITH+WithClause+AS+(SELECT+p.id,+p.text,+
++++++++(SELECT+TOP+1+c.id+from+childs+c+
++++++++++++where+c.parent+=+p.id+order+by+c.feature+desc)+
++++++++AS+BestChildID
++++FROM+Parents+p)+
SELECT+WithClause.id,+WithClause.text,+c.id,+c.parent,+c.feature
FROM+WithClause+
LEFT+JOIN+childs+c+on+WithClause.BestChildID+=+c.id|code-block|syntax|javascript|1432309|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

manji's query does not handle tie-breakers for max feature. Here is my method, which I've tested:

<pre><code>;WITH WithClause AS (SELECT p.id, p.text, 
 (SELECT TOP 1 c.id from childs c 
 where c.parent = p.id order by c.feature desc) 
 AS BestChildID
 FROM Parents p) 
SELECT WithClause.id, WithClause.text, c.id, c.parent, c.feature
FROM WithClause 
LEFT JOIN childs c on WithClause.BestChildID = c.id
</code></pre>

blocks|key|1217483|text|如果需要连接不同于MAX列的列和group+by+select闭包中描述的任何列，可以使用APPLY函数。这是一个最简单的解决方案。您还可以使用WITH+operator。但这看起来更难。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1217484|SELECT+p.id,+p.text,+CHILD_ROW.ANY_COLLUMN
FROM+parent+p
OUTER+APPLY+(SELECT+TOP+1+child.ANY_COLLUMN
++++++++++++++++++FROM+child
++++++++++++++++++WHERE+child.parent+=+p.id
++++++++++++++++++ORDER+BY+child.feature+DESC+
++++++++++++++++++)+CHILD_ROW|code-block|syntax|javascript|1217485|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

If you need to join different from a MAX column and any columns described in a group by closure of a nested select, you can use an APPLY function. 
It is a simplest solution. You also can use WITH operator. But that look harder.

<pre><code>SELECT p.id, p.text, CHILD_ROW.ANY_COLLUMN
FROM parent p
OUTER APPLY (SELECT TOP 1 child.ANY_COLLUMN
 FROM child
 WHERE child.parent = p.id
 ORDER BY child.feature DESC 
 ) CHILD_ROW
</code></pre>

There are two SQL tables:

<pre><code>Parents:
+--+---------+
|id| text |
+--+---------+
| 1| Blah |
| 2| Blah2 |
| 3| Blah3 |
+--+---------+

Childs
+--+------+-------+
|id|parent|feature|
+--+------+-------+
| 1| 1 | 123 |
| 2| 1 | 35 |
| 3| 2 | 15 |
+--+------+-------+
</code></pre>

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:

<pre><code>+----+------+----+--------+---------+
|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 |
+----+------+----+--------+---------+
</code></pre>

Where p = Parent table and c = Child table

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...

one-to-many query selecting all parents and single top child for each parent

SQL Server

有两个SQL表：Parents:+--+---------+|id|   text  |+--+---------+| 1|  Blah   || 2|  Blah2  || 3|  Blah3  |+--+---------+Childs+--+------+-------+|id|parent|feature|+-...

问一对多查询，为每个父项选择所有父项和单个顶层子项
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问一对多查询，为每个父项选择所有父项和单个顶层子项EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问一对多查询，为每个父项选择所有父项和单个顶层子项
EN