查找每个父级的顶值

Question

我确信这是一个常见的请求，但我不知道如何正式地请求它。

我很久以前在军队的时候就遇到过这种情况。一名士兵有多项体能测试，但主要的体能测试计入最近一次。士兵也有多种枪法资质，但只有分配给他的武器的最新资质才有意义。

如何创建一个逐项列出父级最重要的子级的视图？

Answer 1

使用：

  SELECT p.*, x.*
    FROM PARENT p
    JOIN CHILD x ON x.parent_id = p.id
    JOIN (SELECT c.id,
                 c.parent_id,
                 MAX(c.date_column) AS max_date
            FROM CHILD c
        GROUP BY c.id, c.parent_id) y ON y.id = x.id
                                     AND y.parent_id = x.parent_id
                                     AND y.max_date = x.date

假设SQL Server 2005+：

WITH summary AS (
     SELECT p.*, 
            c.*, 
            ROW_NUMBER() OVER (PARTITION BY p.id 
                                  ORDER BY c.date DESC) AS rank
       FROM PARENT p
       JOIN CHILD c ON c.parent_id = p.id)
SELECT s.*
  FROM summary s
 WHERE s.rank = 1

Answer 2

虽然我不太确定你的“分项”是什么意思，但你可以这样做：

Select ..
From Soldier
    Left Join FitnessTest
        On FitnessTest.SoldierId = Soldier.Id
            And FitnessTest.TestDate =  (
                                        Select Max(FT1.TestDate)
                                        From FitnessTest As FT1
                                        Where FT1.SoldierId = FitnessTest.SoldierId
                                        )
    Left Join MarksmanshipTest
        On MarksmanshipTest.SoldierId = Soldier.Id
            And MarksmanshipTest.TestDate = (
                                            Select Max(MT1.TestDate)
                                            From MarksmanshipTest As MT1
                                            Where MT1.SoldierId = MarksmanshipTest.SoldierId
                                            )

这假设一个士兵不能有两个用于体能测试或射击测试的测试日期时间值。

Answer 3

与前两个答案没有显着区别，但可能会有更多细节：

create table soldier ( soldierId int primary key,
    name varchar(100) )
create table fitnessTest ( soldierId int foreign key references soldier,
    occurred datetime, result int )
create table marksmanshipTest ( soldierId int foreign key references soldier,
    occurred datetime, result int )

;with
mostRecentFitnessTest as
(
    select
        fitnessTest.soldierId,
        fitnessTest.result,
        row_number() over (order by occurred desc) as row
    from fitnessTest
),
mostRecentMarksmanshipTest as
(
    select
        marksmanshipTest.soldierId,
        marksmanshipTest.result,
        row_number() over (order by occurred desc) as row
    from marksmanshipTest
)
select
    soldier.soldierId,
    soldier.name,
    mostRecentFitnessTest.result,
    mostRecentMarksmanshipTest.result
from soldier
    left outer join mostRecentFitnessTest on
        mostRecentFitnessTest.soldierId = soldier.soldierId
        and mostRecentFitnessTest.row = 1
    left outer join mostRecentMarksmanshipTest on
        mostRecentMarksmanshipTest.soldierId = soldier.soldierId
        and mostRecentMarksmanshipTest.row = 1