处理空值上的Max函数或返回多个值

Question

下面有两个表结构，我需要得到与特定事件的最大verified_dt_tm相对应的结果。如果某个特定事件具有相同的verified_dt_tm，则需要根据该特定事件的最大result_id来获取它。我在下面写了一个示例查询。它适用于非空值，但不适用于空值。我已经为空值使用了合并，但这是行不通的，因为外部查询仍然有verified_dt_tm作为空广告，它们不匹配。下面是表的详细说明。

• 订单表对每个订单有一个条目。
• 一个订单可以有多个结果。例如，血液测试可以有嗜碱性细胞计数，单核细胞计数，淋巴细胞计数等，所以一个血液测试顺序有3个或3个以上的事件，我得到了结果。

订单表

order_id       order_status
12345          Completed

结果表

result_table_id  order_id  event              verified_dt_tm      result_value
98765            12345     Basophils count    22/02/19 11:00      12
87654            12345     monocytes count    21/02/19 15:00      34
76543            12345     lymphocytes count  21/02/19 14:44      35
76542            12345     rbcytes count                          35
76540            12345     rbcytes count                          1
76532            12345     rbcytes count                          3

98765            12345     Basophils count    22/02/19 10:00      12
87654            12345     monocytes count    21/02/19 11:00      34
76543            12345     lymphocytes count  21/02/19 11:44      35

所需输出

result_table_id  order_id  event              verified_dt_tm      result_value
98765            12345     Basophils count    22/02/19 11:00      12
87654            12345     monocytes count    21/02/19 15:00      34
76543            12345     lymphocytes count  21/02/19 14:44      35
76542            12345     rbcytes count                          35

下面是示例查询。此外，任何关于更好的查询的建议都是非常欢迎的：

 select o.order_id
  ,TO_CHAR(r_o.verified_dt_tm, 'DD-MON-YYYY HH24:MI:SS') as verified_dt_tm
  ,r_o.result_val as result
  ,r_o.result_table_id
  ,omf_get_cv_display(r_o.event_cd) as event
  ,omf_get_cv_display(o.ORDER_STATUS_CD) as order_status

from orders o
left outer join (select * from results r where r.event_cd > 0                                
                            and r.VERIFIED_DT_TM = (select max(COALESCE(r1.VERIFIED_DT_TM,to_date('12/31/2099','mm/dd/yyyy'))) from result r1 where r.ORDER_ID = r1.ORDER_ID
                                                    and r.EVENT_CD = r1.event_cd))r_o on r_o.ORDER_ID = o.order_id

Answer 1

在这里，ROW_NUMBER应该是可行的：

WITH cte AS (
    SELECT res.*,
        ROW_NUMBER() OVER (PARTITION BY res.order_id, res.event
            ORDER BY res.verified_dt_tm DESC, res.result_value DESC) rn
    FROM orders o
    INNER JOIN result res
        ON o.order_id = r.order_id
)

SELECT *
FROM cte
WHERE rn = 1;

Answer 2

根据样本数据，“Tim”之前的回答是完美的。但是，如果有一个具有'rbcytes计数‘的记录具有非空的，那么它也会给出以空verified_dt_tm为空的输出，这是由desc首先考虑的。

查询应该根据ROW_NUMBER的订单计算verified_dt_tm desc nulls last。

以下应适用于您：

WITH CTE AS (
    SELECT
        RES.*,
        ROW_NUMBER() OVER(
            PARTITION BY RES.ORDER_ID, RES.EVENT
            ORDER BY
                RES.VERIFIED_DT_TM DESC NULLS LAST,  -- added NULLS LAST 
                RES.RESULT_VALUE DESC
        ) RN
    FROM
        ORDERS O
        INNER JOIN RESULT R ON O.ORDER_ID = R.ORDER_ID
)
SELECT
    *
FROM
    CTE
WHERE
    RN = 1;

干杯！！

Answer 3

除了上面的答案之外，如果有人需要使用它作为左联接和内联子查询：

left outer join (SELECT r.RESULT_VAL 
,r.EVENT
,r.result_table_ID
,r.ORDER_ID
,r.VERIFIED_DT_TM
, RANK() OVER(
    PARTITION BY r.ORDER_ID,r.EVENT ORDER BY r.VERIFIED_DT_TM DESC NULLS LAST,r.result_table_ID ) AS RANK 

from results r )r_o on (r_o.order_id = o.order_id AND r_o.RANK = 1)