SQL查询需求：按BillNum聚合，根据BatchUpload返回指定单条结果

每次上传发票时执行语句：INSERT INTO tblVersapayInvoice。现有tblVersapayInvoice表数据如下：

需求说明 #

每个BillNum仅返回一行结果，规则如下：

• 若该BillNum存在BatchUpload = 'Y'的记录，返回'Batch'
• 若所有记录的BatchUpload均为NULL，返回该BillNum对应的MAX(LastUploadDate)

期望结果 #

BillNum    LastUploadDate
240022     2023-09-27 09:21:32.010
229985     Batch
230071     2023-09-25 09:21:32.010

当前问题查询语句 #

以下查询会因按BillNum和BatchUpload分组，导致同一个BillNum返回两行（分别对应BatchUpload = 'Y'和NULL），不符合需求：

SELECT 
    CASE 
        WHEN BatchUpload IS NOT NULL THEN 'Batch' 
        ELSE CONVERT(varchar, MAX(LastUploadDate), 1) 
    END AS LastUploadDate, 
    BillNum 
FROM 
    tblVersapayInvoice 
GROUP BY 
    BillNum, BatchUpload

正确查询方案 #

方案一：使用MAX(BatchUpload)判断

利用MAX()函数对字符串的特性（'Y'优先级高于NULL），分组后只要该BillNum存在BatchUpload='Y'的记录，MAX(BatchUpload)就会返回'Y'，以此判断返回值：

SELECT 
    BillNum,
    CASE 
        WHEN MAX(BatchUpload) = 'Y'
        THEN 'Batch'
        ELSE CONVERT(varchar, MAX(LastUploadDate), 121) -- 121格式匹配示例中的日期时间格式
    END AS LastUploadDate
FROM 
    tblVersapayInvoice
GROUP BY 
    BillNum

方案二：使用EXISTS子查询

通过子查询检查当前BillNum是否存在BatchUpload='Y'的记录，逻辑更直观：

SELECT 
    t1.BillNum,
    CASE 
        WHEN EXISTS (SELECT 1 FROM tblVersapayInvoice t2 WHERE t2.BillNum = t1.BillNum AND t2.BatchUpload = 'Y')
        THEN 'Batch'
        ELSE CONVERT(varchar, MAX(t1.LastUploadDate), 121)
    END AS LastUploadDate
FROM 
    tblVersapayInvoice t1
GROUP BY 
    t1.BillNum

两种方案都能保证每个BillNum仅返回一行，且符合需求规则。

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