SQL层次结构-获得最新的孙女

Question

我有一个H2数据库，在Bird上有一个左联接查询，它返回所有的鸟类以及最新的Health_Check.Catch_Date。我想扩展这个查询，并在结果中包括最新的PIT.ID、PIT.CODE和TRANSMITTER.IDc、TRANSMITTER.CHANNEL，它们都与Health_Check相关。

Note：并不是每个Health_Check都与发射机或PIT有关系。

​

​

一些关于数据是如何构造的说明。

• 大多数鸟类都进行了多次健康检查；
• 并不是每个健康检查都有相关的凹坑或发射器；
• 一只鸟的最新Health_Check可能不包含一个坑或发射器；
• 大多数鸟都有一个坑；
• 大多数鸟类都有几个发射器；
• 发送器或PIT所附加的日期是基于相关的Health_Check.Catch_Date；
• 我的大多数查询将需要返回一个鸟与最新的健康检查，坑和发射机。

获取最新HEALTH_CHECK的SQL是：

SELECT b.NAME, b.ID as birdId, hc1.CATCH_DATE, hc1.id as healthCheckId
FROM BIRD b
         LEFT OUTER JOIN
     (  HEALTH_CHECK hc1
         INNER JOIN
         (
             SELECT BIRD_ID, MAX(CATCH_DATE) AS MAX_DATE
             FROM HEALTH_CHECK
             GROUP BY BIRD_ID
         ) hc2
         ON hc2.BIRD_ID = hc1.BIRD_ID AND
            hc2.MAX_DATE = hc1.CATCH_DATE
         )
     ON hc1.BIRD_ID = b.ID;

问题：我怎样才能在结果中返回最新的PIT和发射机？注:最新的PIT和TRANS通常不在最新的HealthCheck上。我对模式更改/添加第二个关系是开放的，但是我必须能够确定一个PIT和TRANS连接到哪个HealthCheck中。

例如，结果如下：

BIRD.ID  |  BIRD.NAME  | NEWEST HEALTH CHCECK | PIT.ID | PIT.CODE | TRANS.ID | TRANS.CHNL |
---------|-------------|----------------------|--------|----------|----------|------------|
   1     |    Bob      |    2022-03-01        |  AB001 |     3    |    2     |     40     |
   2     |    Jim      |      NULL            |  NULL  |    NULL  |   NULL   |    NULL    |
   3     |    Jane     |    2022-01-02        |  DC123 |     2    |    3     |     50     |

Answer 1

一种解决方案是，要从每个跨ANd坑中获得最高的ID，我认为这是ic能够找到的唯一顺序。

Row_number将为特定的chekc id提供每一行，而nimber 1应该是最后一行(按ID排序)，它为每个通道计算一个新的

SELECT b.NAME, b.ID as birdId, hc1.CATCH_DATE, hc1.id as healthCheckId
,PIT.ID , PIT.CODE,TRANS.ID , TRANS.CHANNEL
FROM BIRD b
         LEFT OUTER JOIN
     (  HEALTH_CHECK hc1
         INNER JOIN
         (
             SELECT BIRD_ID, MAX(CATCH_DATE) AS MAX_DATE
             FROM HEALTH_CHECK
             GROUP BY BIRD_ID
         ) hc2
         ON hc2.BIRD_ID = hc1.BIRD_ID AND
            hc2.MAX_DATE = hc1.CATCH_DATE
         )
     ON hc1.BIRD_ID = b.ID
LEFT JOIN
(SELECT ID, CODE,HEALTH_CHECK_ID
FROM
 (SELECT ID , CODE,HEALTH_CHECK_ID
, ROW_NUMBER() OVER( PARTITION BY HEALTH_CHECK_ID ORDER BY ID DESC) rn FROM PIT) Pt
WHERE rn = 1) PIT ON PIT.HEALTH_CHeCK_ID = hc1.ID

LEFT JOIN
(SELECT ID, CHANNEL,HEALTH_CHECK_ID
FROM
 (SELECT ID , CHANNEL,HEALTH_CHECK_ID
, ROW_NUMBER() OVER( PARTITION BY HEALTH_CHECK_ID ORDER BY ID DESC) rn FROM PIT) tr
WHERE rn = 1) TRANS ON TRANS.HEALTH_CHECK_ID = hc1.ID;

如果有1:1的关系，你只需要加入他们，

SELECT b.NAME, b.ID as birdId, hc1.CATCH_DATE, hc1.id as healthCheckId
,PIT.ID , PIT.CODE,TRANS.ID , TRANS.CHANNEL
FROM BIRD b
         LEFT OUTER JOIN
     (  HEALTH_CHECK hc1
         INNER JOIN
         (
             SELECT BIRD_ID, MAX(CATCH_DATE) AS MAX_DATE
             FROM HEALTH_CHECK
             GROUP BY BIRD_ID
         ) hc2
         ON hc2.BIRD_ID = hc1.BIRD_ID AND
            hc2.MAX_DATE = hc1.CATCH_DATE
         )
     ON hc1.BIRD_ID = b.ID
LEFT JOIN
 PIT ON PIT.HEALTH_CHeCK_ID = hc1.ID

LEFT JOIN
 TRANS ON TRANS.HEALTH_CHECK_ID = hc1.ID;

如果您需要另一个不在最新健康检查中的heahc_ckes tarns和Pit，则需要在第一个查询的sunselect中添加该鸟的所有治疗功能。

SELECT b.NAME, b.ID as birdId, hc1.CATCH_DATE, hc1.id as healthCheckId
,PIT.ID , PIT.CODE,TRANS.ID , TRANS.CHANNEL
FROM BIRD b
         LEFT OUTER JOIN
     (  HEALTH_CHECK hc1
         INNER JOIN
         (
             SELECT BIRD_ID, MAX(CATCH_DATE) AS MAX_DATE
             FROM HEALTH_CHECK
             GROUP BY BIRD_ID
         ) hc2
         ON hc2.BIRD_ID = hc1.BIRD_ID AND
            hc2.MAX_DATE = hc1.CATCH_DATE
         )
     ON hc1.BIRD_ID = b.ID
LEFT JOIN
(SELECT ID, CODE,HEALTH_CHECK_ID,BIRD_ID
FROM
 (SELECT ID , CODE,hc3.BIRD_ID,HEALTH_CHECK_ID
, ROW_NUMBER() OVER( PARTITION BY HEALTH_CHECK_ID ORDER BY hc3.CATCH_DATE DESC) rn FROM HEALTH_CHECK hc3 JOIN PIT ON hc3.ID = PIT.HEALTH_CHeCK_ID
WHERE hc3.BIRD_ID = hc1.BIRD_ID) Pt
WHERE rn = 1) PIT ON PIT.BIRD_ID = hc1.BIRD_ID

LEFT JOIN
(SELECT ID, CHANNEL,HEALTH_CHECK_ID,BIRD_ID
FROM
 (SELECT ID , CHANNEL,HEALTH_CHECK_ID,BIRD_ID
, ROW_NUMBER() OVER( PARTITION BY HEALTH_CHECK_ID ORDER BY hc4.CATCH_DATE DESC) rn FROM HEALTH_CHECK hc4 JOIN PIT ON hc4.ID = PIT.HEALTH_CHeCK_ID
WHERE hc4.BIRD_ID = hc1.BIRD_ID) tr
WHERE rn = 1) TRANS ON TRANS.BIRD_ID = hc1.BIRD_ID;

Answer 2

对于每个鸟(左联接)返回一个结果集，其中包括：

• 最新健康检查(按日期计算)
• 递归获取最新的凹坑(根据相关健康检查的日期确定)
• 递归获取最新的Trans (根据相关健康检查的日期确定)

注意到PIT和Trans经常没有进行最新的健康检查，可以这样做：

SELECT *
  FROM BIRD AS b
  LEFT JOIN (
        SELECT *
          FROM (
                SELECT id AS hc_id, bird_id, catch_date
                     , ROW_NUMBER() OVER (PARTITION BY bird_id ORDER BY catch_date DESC) AS n
                  FROM HEALTH_CHECK
               ) AS cte1a
         WHERE n = 1
     ) AS cte1
    ON cte1.bird_id = b.id
  LEFT JOIN (
        SELECT *
          FROM (
                SELECT p.id AS pit_id
                     , hc.bird_id
                     , p.Health_Check_Id
                     , p.code
                     , hc.catch_date as catchdate
                     , RANK() OVER (PARTITION BY hc.bird_id ORDER BY catch_date DESC) AS n
                  FROM PIT          AS p
                  JOIN HEALTH_CHECK AS hc  
                    ON p.Health_Check_Id = hc.id
               ) AS cte2a
         WHERE n = 1
     ) AS cte2
    ON cte2.bird_id = b.id
  LEFT JOIN (
        SELECT *
          FROM (
                SELECT t.id AS transmitter_id
                     , hc.bird_id
                     , t.Health_Check_Id
                     , t.channel
                     , hc.catch_date as catchdate
                     , RANK() OVER (PARTITION BY hc.bird_id ORDER BY catch_date DESC) AS n
                  FROM TRANSMITTER          AS t
                  JOIN HEALTH_CHECK AS hc  
                    ON t.Health_Check_Id = hc.id
               ) AS cte3a
         WHERE n = 1

小提琴是这里。