SQL分组查询：按No判断是否存在错误并返回单行结果

刚好碰到过类似的需求，咱们直接来看怎么实现每个编号（No）仅返回一行，判断该编号下是否存在至少一个错误的SQL写法。

需求明确 #

需编写SQL查询，针对特定编号（No）检查是否存在任何错误。若该编号下存在1个及以上错误，结果返回“Yes”；若无错误，返回“No”。要求每个No仅返回一行结果，而非多行。

原查询的问题 #

你提供的原查询会返回每条记录的错误状态，导致一个No对应多行结果，显然不符合“每个No一行”的要求：

SELECT 
    CASE WHEN ISNUMERIC(SUB1.ItemNo) != 1 THEN 'ERROR: ItemNo not numeric' ELSE 'OK' END AS ErrorType, 
    SUB1.No 
FROM ( 
    --123 
    SELECT 123 AS No, '123456789' AS ItemNo UNION ALL 
    SELECT 123 AS No, '123456789x' AS ItemNo UNION ALL 
    SELECT 123 AS No, '122456789' AS ItemNo UNION ALL 
    --456 
    SELECT 456 AS No, '123456789' AS ItemNo UNION ALL 
    SELECT 456 AS No, '178978788' AS ItemNo UNION ALL 
    SELECT 456 AS No, '114147787' AS ItemNo 
) AS SUB1 

两种可行的解决方案 #

方案1：用MAX()标记错误状态 #

这种方式通过标记错误记录为1，正确为0，取分组内的最大值来判断是否存在错误：

SELECT 
    CASE 
        WHEN MAX(CASE WHEN ISNUMERIC(ItemNo) != 1 THEN 1 ELSE 0 END) = 1 THEN 'Yes' 
        ELSE 'No' 
    END AS Error,
    No
FROM ( 
    --123 
    SELECT 123 AS No, '123456789' AS ItemNo UNION ALL 
    SELECT 123 AS No, '123456789x' AS ItemNo UNION ALL 
    SELECT 123 AS No, '122456789' AS ItemNo UNION ALL 
    --456 
    SELECT 456 AS No, '123456789' AS ItemNo UNION ALL 
    SELECT 456 AS No, '178978788' AS ItemNo UNION ALL 
    SELECT 456 AS No, '114147787' AS ItemNo 
) AS SUB1
GROUP BY No
ORDER BY No;

方案2：用COUNT()统计错误数量 #

这个方案逻辑更直观，直接统计每个分组内的错误记录数，大于0就返回Yes：

SELECT 
    CASE 
        WHEN COUNT(CASE WHEN ISNUMERIC(ItemNo) != 1 THEN 1 END) > 0 THEN 'Yes' 
        ELSE 'No' 
    END AS Error,
    No
FROM ( 
    --123 
    SELECT 123 AS No, '123456789' AS ItemNo UNION ALL 
    SELECT 123 AS No, '123456789x' AS ItemNo UNION ALL 
    SELECT 123 AS No, '122456789' AS ItemNo UNION ALL 
    --456 
    SELECT 456 AS No, '123456789' AS ItemNo UNION ALL 
    SELECT 456 AS No, '178978788' AS ItemNo UNION ALL 
    SELECT 456 AS No, '114147787' AS ItemNo 
) AS SUB1
GROUP BY No
ORDER BY No;

执行结果 #

不管用哪种方案，都会得到你期望的结果：

Error | No
------|-----
Yes   | 123
No    | 456

简单解释一下逻辑 #

1. 内层的SUB1子查询还是用你原来的数据源，不用改。
2. 外层查询按No分组，这样每个No只会生成一行结果。
3. 内层的CASE语句用来判断单条记录是否错误：
   • 方案1里，错误记为1，正确记为0，MAX()会拿到分组里的最大值，只要有一个错误，最大值就是1，就返回Yes。
   • 方案2里，错误记录会被计数，正确的会被忽略（因为CASE返回NULL，COUNT不统计NULL），只要计数大于0，说明有错误，返回Yes。

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