sql - 使用oracle的一个case语句中的多个条件

Question

我有以下 sql 和结果:

SELECT oh.order_id,disallow_short_ship,sm.number
FROM table1 oh
    LEFT JOIN table2 sm
        ON sm.client_id = oh.client_id
            AND sm.site_id = oh.from_site_id
            AND sm.order_id = oh.order_id
WHERE oh.order_id = 'abc'
GROUP BY oh.order_id,oh.disallow_short_ship,sm.number
ORDER BY oh.order_id desc;

结果:

order_id   disallow_short_ship      number 
abc        null                     2107
abc        null                     2108

预期的结果是把 case 条件
如果订单有更多的结果，那么
比较两个数字列，
较大的数字( 2108 )应该有 disallow_short_ship = 'Y'对于小数 ( 2107 ) disallow_short_ship = 'N'结果。
我曾尝试为 disallow_short_ship 写案例陈述列，但我需要输入两个条件吗
在一个案例陈述中。
预期结果:

order_id   disallow_short_ship   number 
abc        N                     2107
abc        Y                     2108

Answer 1

使用窗口函数和 case表达:

SELECT oh.order_id, disallow_short_ship, sm.number,
       (CASE WHEN COUNT(*) OVER (PARTITION BY oh.order_id) > 1 AND
                  ROW_NUMBER() OVER (PARTITION BY oh.order_id ORDER BY number) = 1
             THEN 'N'
             WHEN COUNT(*) OVER (PARTITION BY oh.order_id) > 1 AND
                  ROW_NUMBER() OVER (PARTITION BY oh.order_id ORDER BY number DESC) = 1
             THEN 'Y'
             ELSE disallow_short_ship
        END) as disallow_short_ship             
FROM table1 oh LEFT JOIN
     table2 sm
     ON sm.client_id = oh.client_id AND
        sm.site_id = oh.from_site_id AND
        sm.order_id = oh.order_id
WHERE oh.order_id = 'abc'
GROUP BY oh.order_id, oh.disallow_short_ship, sm.number
ORDER BY oh.order_id desc;