时间戳

首页 > 解决方案 > DB2 SQL 基于自定义加权从左连接表返回一行

sql - DB2 SQL 基于自定义加权从左连接表返回一行

问题描述

我有两个表，“INVOICES”包含发票信息，“CSEML”包含客户电子邮件地址。电子邮件地址类型（字段 EMLTP）可以指定为“AP”（应付帐款）、“PO”（采购）或空白（默认）。每个客户在 CSML 中可以有 3 个电子邮件地址（每个指定一个）。我希望能够在查询中执行此操作：将发票表连接到电子邮件表，并为每张发票最多提取一个电子邮件地址 - 如果已定义，则为 AP 地址，否则为 PO 地址，如果已定义，否则为默认地址（如果已定义），否则为 null。

CSML 的结构是这样的

CUSTNO,EMLTP,EMAIL
000989367,PO,po@example.com
000254785,PO,po@example3.com
000989367,,default@example.com
000112354,,default@example2.com
000989367,AP,ap@example.com
000254785,,default@example3.com

现在我有以下查询

SELECT I.INVNO, I.CUSTNO, E.EMAIL 
FROM INVOICES I 
LEFT JOIN CSEML E ON I.CUSTNO = E.CUSTNO
WHERE I.INVNO = '10124'

当定义了所有 3 个地址时，它会返回如下内容：

10124    000989367    po@example.com
10124    000989367    default@example.com
10124    000989367    ap@example.com

如何为不同的电子邮件“类型”分配权重，以便我只获得最喜欢的可用地址？

基于已接受答案的工作代码

SELECT INVNO, CUSTNO, EMAIL
FROM(
    SELECT I.INVNO, I.CUSTNO, E.EMAIL, ROW_NUMBER() OVER (PARTITION BY I.INVNO
        ORDER BY
            CASE WHEN E.EMLTP = 'AP' THEN 0
                 WHEN E.EMLTP = 'PO' THEN 1
                 ELSE 2 END) rn 
    FROM INVOICES I 
    LEFT JOIN CSEML E ON I.CUSTNO = E.CUSTNO
    WHERE I.INVNO = '10124'
) t
WHERE t.rn = 1

标签： sqldb2left-join

解决方案

您可以尝试ROW_NUMBER在此处使用CASE表达式来排序/优先处理每张发票的电子邮件地址：

SELECT INVNO, CUSTNO, EMAIL
FROM
(
    SELECT I.INVNO, I.CUSTNO, E.EMAIL, ROW_NUMBER() OVER (PARTITION BY I.INVNO
        ORDER BY
            CASE WHEN E.EMAIL LIKE 'ap@%' THEN 0
                 WHEN E.EMAIL LIKE 'po@%' THEN 1
                 ELSE 2 END) rn
    FROM INVOICES I 
    LEFT JOIN CSEML E
        ON I.CUSTNO = E.CUSTNO
    WHERE I.INVNO = '10124'
) t
WHERE t.rn = 1;

推荐阅读