如何合并SQL：优先取Priority 1行，无则取首行ship_to_cust_id

需求：在PeopleSoft App Engine的INSERT语句中实现以下逻辑：

• 若存在SLDTO_PRIORITY_NBR=1的行，取对应的ship_to_cust_id
• 若不存在，则取结果集中首行的ship_to_cust_id

现有两条分别实现单一逻辑的简化SQL：

SELECT 
    s.cust_id, s.ship_to_cust_id, s.sldto_priority_nbr
FROM 
    sysadm.ps_cust_shipto s, sysadm.PS_CUST_BILLTO b
WHERE 
    b.setid = 'SHARE' 
    AND b.cust_id = '90677671'
    AND b.setid = s.setid 
    AND b.cust_id = s.cust_id 
    AND s.ship_to_cust_id IN (SELECT x.ship_to_cust_id 
                              FROM sysadm.ps_cust_shipto x
                              WHERE x.sldto_priority_nbr = 1 
                                AND x.cust_id = b.cust_id)

SELECT 
    s.cust_id, s.ship_to_cust_id, s.sldto_priority_nbr
FROM 
    sysadm.ps_cust_shipto s, sysadm.PS_CUST_BILLTO b
WHERE 
    b.setid = 'SHARE' 
    AND b.cust_id = '90677671'
    AND b.setid = s.setid 
    AND b.cust_id = s.cust_id 
    AND s.ship_to_cust_id IN (SELECT y.ship_to_cust_id 
                              FROM sysadm.ps_cust_shipto y
                              WHERE rownum = 1 
                                AND y.cust_id = b.cust_id) 

样本数据：

SETID  CUST_ID SHIP_TO_CUST_ID  SLDTO_PRIORITY_NBR  LAST_OPRID
SHARE   90677671    114369323         2            JENNIFERBRYAN
SHARE   90677671    114479768         3            JENNIFERBRYAN
SHARE   90677671    90677695          1             TARAARROYO

解决方案：使用窗口函数合并逻辑 #

可以通过ROW_NUMBER()窗口函数对数据进行排序，优先标记SLDTO_PRIORITY_NBR=1的行，再取排序后的第一行，同时覆盖两种场景：

SELECT cust_id, ship_to_cust_id, sldto_priority_nbr
FROM (
    SELECT 
        s.cust_id, 
        s.ship_to_cust_id, 
        s.sldto_priority_nbr,
        -- 优先级1的行排第1，其他行按默认顺序排序
        ROW_NUMBER() OVER (
            PARTITION BY s.cust_id 
            ORDER BY CASE WHEN s.sldto_priority_nbr = 1 THEN 0 ELSE 1 END, s.sldto_priority_nbr
        ) AS rn
    FROM 
        sysadm.ps_cust_shipto s
    JOIN 
        sysadm.PS_CUST_BILLTO b ON b.setid = s.setid AND b.cust_id = s.cust_id
    WHERE 
        b.setid = 'SHARE' 
        AND b.cust_id = '90677671'
) t
WHERE rn = 1;

逻辑说明： #

1. 内层查询通过ROW_NUMBER()给每个cust_id的行分配序号：
   • CASE WHEN s.sldto_priority_nbr = 1 THEN 0 ELSE 1 END确保优先级1的行序号为1，其他行从2开始
   • 第二个排序条件s.sldto_priority_nbr保证没有优先级1的行时，按优先级升序取第一行（可根据需求调整排序规则）
2. 外层查询只取序号为1的行，即符合要求的目标行

针对提供的样本数据，这条SQL会返回SLDTO_PRIORITY_NBR=1的行：

CUST_ID SHIP_TO_CUST_ID  SLDTO_PRIORITY_NBR
90677671    90677695          1

如果样本数据中没有SLDTO_PRIORITY_NBR=1的行，会返回结果集中按优先级升序排列的第一行。

内容的提问来源于stack exchange，提问作者Okie4ever