SQL技术求助：实现购物篮分组奖励汇总及解决SQL0811错误与结果为Null问题

首先咱们梳理下问题：你需要根据每个购物订单的商品数量匹配对应奖励（商品越多奖励越高），现有两张数据表，期望得到按订单分组的奖励汇总结果，但自己写的SQL要么返回award全为Null，要么报错SQL0811 result of select more than one row，下面咱们一步步解决这个问题。

现有数据表结构 #

tab_basket（购物篮表） #

tab_award（奖励规则表） #

你期望的结果 #

你的尝试问题分析 #

先看你写的SQL：

SELECT award, items, shopping_no, type_award 
FROM tab_basket t 
LEFT OUTER JOIN tab_award u 
ON t.TYPE = u.type_award 
AND CASE 
WHEN (SELECT COUNT(shopping_no) FROM tab_basket m WHERE t.id = m.id AND t.name = m.name AND t.shopping_no = m.shopping_no AND t.TYPE = u.TYPE GROUP BY shopping_no ) = '1' THEN '1' 
CASE WHEN (SELECT COUNT(shopping_no) FROM tab_basket m WHERE t.id = m.id AND t.name = m.name AND t.shopping_no = m.shopping_no AND t.TYPE = u.TYPE GROUP BY shopping_no ) = '2' THEN '2' 
CASE WHEN (SELECT COUNT(shopping_no) FROM tab_basket m WHERE t.id = m.id AND t.name = m.name AND t.shopping_no = m.shopping_no AND t.TYPE = u.TYPE GROUP BY shopping_no ) = '3' THEN '3' 
CASE WHEN (SELECT COUNT(shopping_no) FROM tab_basket m WHERE t.id = m.id AND t.name = m.name AND t.shopping_no = m.shopping_no AND t.TYPE = u.TYPE GROUP BY shopping_no ) = '4' THEN '4' 
ELSE 'no_price' END = u.award

这里有几个关键错误：

1. 子查询逻辑错误：子查询里加了t.id = m.id，导致每一行只能匹配自己，统计出的count永远是1，根本拿不到整个订单的商品数量；而且CASE WHEN语法错误，应该是一个CASE包含多个WHEN分支，不是重复写多个CASE。
2. 关联条件匹配错误：你把CASE的结果和u.award比较，这完全不对——我们需要用订单的商品数量匹配奖励表的items字段，而不是奖励金额。
3. 未先分组订单：直接从tab_basket关联会产生重复行，必须先按订单分组统计商品数量，再关联奖励表。

正确的解决方案 #

方案1：使用CTE（推荐，可读性高） #

大多数现代数据库支持CTE（公共表表达式），先统计每个订单的商品数量，再关联奖励表：

WITH order_item_counts AS (
    SELECT 
        shopping_no,
        type,
        COUNT(*) AS items
    FROM tab_basket
    GROUP BY shopping_no, type
)
SELECT 
    a.award,
    o.items,
    o.shopping_no,
    a.type_award
FROM order_item_counts o
INNER JOIN tab_award a 
    ON o.type = a.type_award 
    AND o.items = a.items;

方案2：使用子查询（兼容老版本数据库，比如DB2） #

如果你的数据库不支持CTE，用子查询也能实现：

SELECT 
    a.award,
    o.items,
    o.shopping_no,
    a.type_award
FROM (
    SELECT 
        shopping_no,
        type,
        COUNT(*) AS items
    FROM tab_basket
    GROUP BY shopping_no, type
) o
INNER JOIN tab_award a 
    ON o.type = a.type_award 
    AND o.items = a.items;

方案解释 #

1. 先分组统计：通过GROUP BY shopping_no, type得到每个订单唯一的一行数据，包含订单号、商品类型和该订单的商品数量，避免了多行错误。
2. 精准关联奖励表：用订单的类型和商品数量匹配奖励表的type_award和items字段，能准确拿到对应的奖励金额，不会出现Null。
3. 结果完全匹配期望：执行后返回的结果和你想要的完全一致。

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