一对多查询选择每个父母的所有父母和单个顶级孩子

5 回答

• 1

  manji的查询不处理最大功能的断路器 . 这是我的方法，我测试过：

  ;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
  

  回复于 2024-05-06T12:41:25+08:00
• 9

  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
  

  回复于 2024-05-06T12:41:25+08:00
• 2

  使用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 值具有相同的特征值时） . Agent_9191的答案使用了 TOP 1 ，但这将是第一个返回的，而不一定是你想要的 .

  回复于 2024-05-06T12:41:25+08:00
• 17

  这应该工作：

  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
  

  回复于 2024-05-06T12:41:25+08:00
• 4

  如果需要通过关闭嵌套选择来加入与MAX列不同的组以及组中描述的任何列，则可以使用APPLY函数 . 这是一个最简单的解决方案 . 您也可以使用WITH运算符 . 但那看起来更难 .

  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
  

  回复于 2024-05-06T12:41:25+08:00