SQLServerSELECT第一次出現OR如果沒有出現則選擇其他條件

我在嘗試為此處的作業形成正確的 SQL 查詢時遇到問題。我有兩個表，一個叫做 CUSTOMER，另一個叫做 CUSTOMER_CONTACT。為了簡化這一點，我將只包括相關的列名。

• CUSTOMER 列：ID、CUSTOMERNAME

• CUSTOMER_CONTACT 列：ID、CUSTOMER_ID、CONTACT_VC、EMAIL

CUSTOMER_ID是鏈接到CUSTOMER表的外鍵CUSTOMER_CONTACT。CONTACT_VC只是他們聯系資訊的條目編號。CUSTOMER_CONTACT每個客戶可能有多個記錄，但它們將有一個唯一的CONTACT_VC. EMAIL部分或全部也可以為空/空白。

我需要選擇第一個CUSTOMER_CONTACT條目，其中EMAIL是NOT NULL/空，但如果沒有的CUSTOMER_CONTACT條目有一個電子郵件地址，然后選擇CUSTOMER_CONTACT WHERE CONTACT_VC = 1

關于如何實作這一點的任何建議？

uj5u.com熱心網友回復：

以下方法使用 ROW_NUMBER 根據您在每個CUSTOMER_ID組中的排序邏輯檢索數字，然后按檢索到的第一條記錄進行過濾。

您可以嘗試以下操作：

SELECT 
    * 
FROM (
    SELECT
        *,
        ROW_NUMBER() OVER (
            PARTITION BY CUSTOMER_ID
            ORDER BY (CASE WHEN EMAIL IS NOT NULL THEN 0 ELSE 1 END),CONTACT_VC 
        ) as rn
    FROM
        CUSTOMER_CONTACT
) t
WHERE rn=1

如果您想將其加入到客戶表中，您可以使用上述查詢作為子查詢，例如

SELECT
   c.*,
   contact.*
FROM
   CUSTOMER c 
INNER JOIN (
    SELECT
        *,
        ROW_NUMBER() OVER (
            PARTITION BY CUSTOMER_ID
            ORDER BY (CASE WHEN EMAIL IS NOT NULL THEN 0 ELSE 1 END),CONTACT_VC 
        ) as rn
    FROM
        CUSTOMER_CONTACT
) contact ON c.ID = contact.CUSTOMER_ID and contact.rn=1

uj5u.com熱心網友回復：

您可以使用APPLY：

SELECT *
FROM customer
OUTER APPLY (
    SELECT TOP 1 *
    FROM customer_contact
    WHERE customer_contact.customer_id = customer.id AND (
        customer_contact.email IS NOT NULL OR
        customer_contact.contact_vc = 1
    )
    ORDER BY CASE
        WHEN customer_contact.email IS NOT NULL THEN 1,
        WHEN customer_contact.contact_vc = 1 THEN 2
    END, customer_contact.id
) AS customer_contact_x

uj5u.com熱心網友回復：

這里的答案與 ggordon 幾乎相同，但我使用了一個公共表運算式，我認為子查詢部分中的排序應該先按 CONTACT_VS 再按非空電子郵件地址。我創建了一些非常簡單的測驗資料來運行它：

DECLARE @CUSTOMER AS TABLE
(
    [ID]           INT         NOT NULL,
    [CUSTOMERNAME] VARCHAR(10) NOT NULL
);

INSERT INTO @CUSTOMER
(
    [ID],
    [CUSTOMERNAME]
)
VALUES
(1, 'Alice'),
(2, 'Bob'),
(3, 'Cathy');

DECLARE @CUSTOMER_CONTACT AS TABLE
(
    [ID]          INT         NOT NULL,
    [CUSTOMER_ID] INT         NOT NULL,
    [CONTACT_VC]  INT         NOT NULL,
    [EMAIL]       VARCHAR(40) NULL
);

INSERT INTO @CUSTOMER_CONTACT
(
    [ID],
    [CUSTOMER_ID],
    [CONTACT_VC],
    [EMAIL]
)
VALUES
(1, 1, 1, '[email protected]'),
(2, 1, 2, '[email protected]'),
(3, 2, 1, NULL),
(4, 2, 2, '[email protected]'),
(5, 3, 1, NULL),
(6, 3, 2, NULL),
(7, 3, 3, NULL);

;WITH [cc]
AS (SELECT [ID],
           [CUSTOMER_ID],
           [CONTACT_VC],
           [EMAIL],
           ROW_NUMBER() OVER (PARTITION BY [CUSTOMER_ID]
                              ORDER BY [CONTACT_VC],
                                       (CASE WHEN [EMAIL] IS NOT NULL THEN
                                                 0
                                             ELSE
                                                 1
                                        END
                                       )
                             ) AS [rn]
    FROM   @CUSTOMER_CONTACT)
SELECT [c].[ID], [c].[CUSTOMERNAME], [cc].[ID], [cc].[CUSTOMER_ID], [cc].[CONTACT_VC], [cc].[EMAIL]
FROM   @CUSTOMER AS [c]
       INNER JOIN [cc]
           ON [c].[ID] = [cc].[CUSTOMER_ID]
              AND [cc].[rn] = 1;

uj5u.com熱心網友回復：

select * from CUSTOMER_CONTACT where EMAIL IS NOT NULL 
union all 
select * from CUSTOMER_CONTACT where 
    (CONTACT_VC=1 and NOT EXISTS (select 1 FROM CUSTOMER_CONTACT where EMAIL IS NOT NUL)
order by CONTACT_VC asc limit 1

標籤：sql sql-server 外键

上一篇：計數行出現并聚合SQL

下一篇：如何組合和總結后續值，直到列中的新值