得到每个父母最小的孩子 [英] Get youngest child of each parent

问题描述

您好，我对TSQL不太熟悉。我正在研究解决方案并遇到问题。



我的表结构如下：

Hello, I am not very well familiar with TSQL. I was working on a solution and fall into a problem.

I have a table structure like this:

CREATE TABLE Family (
  Id INT NOT NULL PRIMARY KEY,
  MotherId INT REFERENCES Family(Id),
  FatherId INT REFERENCES Family(Id),
  Name VARCHAR(30) NOT NULL,
  Age INT NOT NULL
);

表中显示此记录：

With this record present in the table:

INSERT INTO people(Id, MotherId, FatherId, Name, Age) VALUES(1, NULL, NULL, 'David', 65);
    INSERT INTO people(Id, MotherId, FatherId, Name, Age) VALUES(2, NULL, NULL, 'Keti', 55);
    INSERT INTO people(Id, MotherId, FatherId, Name, Age) VALUES(3, 2, 1, 'Crysti', 25);
    INSERT INTO people(Id, MotherId, FatherId, Name, Age) VALUES(4, 2, 1, 'Ellen', 18);

我想要实现的是为每个父母获得最小的孩子。与上面的示例数据一样，预期结果应该是：

What I want to achieve is that get the youngest child for each parent. Like in the above example data, the expected result should be:

Name      Age
=============
David    25
Keti      25

预先感谢您的帮助。



什么我试过了：



我尝试了多种解决方案，例如 UNIONS，JOINS，CTE（...）等但是一切都是徒劳。正如我之前提到的，我不是编写TSQL查询的专家。

Thanks in advance for your help.

What I have tried:

I have tried multiple solutions like UNIONS, JOINS, with CTE (...) etc but all in vain. As I earlier mentioned, I'm not an expert in writing TSQL queries.

推荐答案

您的数据和/或预期结果是错误的：18是两个中最小的孩子这些数据的案例。



首先找到每个父母最小的孩子：

Your data and/or expected result is wrong: 18 is the youngest child in both cases for that data.

Start by finding the youngest child of each parent:

SELECT MotherID, MIN(Age) As Age FROM People GROUP BY MotherID HAVING MotherID IS NOT NULL;

SELECT FatherID, MIN(Age) As Age FROM People GROUP BY FatherID HAVING FatherID IS NOT NULL;

然后使用JOIN获取父名称：

Then use JOIN to get the parent name:

SELECT a.Name, b.Age FROM People a
JOIN (SELECT MotherID, MIN(Age) As Age FROM People GROUP BY MotherID HAVING MotherID IS NOT NULL) b ON a.ID = b.MotherId;

SELECT a.Name, c.Age FROM People a
JOIN (SELECT FatherID, MIN(Age) As Age FROM People GROUP BY FatherID HAVING FatherID IS NOT NULL) c ON a.ID = c.FatherId;

然后您可以使用UNION结合两个结果集。

You can then use a UNION to combine the two result sets.

你也可以使用CTE：

You can use CTE too:

WITH Fathers
    AS (SELECT  F.FatherId, MIN(F.Age) Age
          FROM  dbo.Family AS F
         WHERE  F.FatherId IS NOT NULL
         GROUP BY F.FatherId), Mothers
    AS (SELECT  F.MotherId, MIN(F.Age) Age
          FROM  dbo.Family AS F
         WHERE  F.MotherId IS NOT NULL
         GROUP BY F.MotherId)
SELECT  Fathers.FatherId AS ParentId, Fathers.Age
  FROM  Fathers
UNION ALL
SELECT  Mothers.MotherId, Mothers.Age
  FROM  Mothers;

这篇关于得到每个父母最小的孩子的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！