SQL实现Basket与Order的SKU匹配计数查询需求

方法一：直接基于原始表统计（推荐） #

既然你已经有原始的Order表（OrderNo, SKU）和Basket表（BasketNo, SKU），直接通过表关联统计是最高效、最可靠的方式，完全不需要依赖逗号分隔的视图：

SELECT
    b.BasketNo AS BasketID,
    o.OrderNo,
    -- 统计当前Basket的总SKU数（去重）
    COUNT(DISTINCT b.SKU) AS ItemCount,
    -- 统计与当前Order匹配的SKU数量（去重）
    COUNT(DISTINCT CASE WHEN o.SKU = b.SKU THEN b.SKU END) AS MatchCount
FROM
    Basket b
-- 关联所有Order与Basket的组合，如需仅保留有匹配的组合，可替换为INNER JOIN
CROSS JOIN
    [Order] o
GROUP BY
    b.BasketNo, o.OrderNo;

如果只需要保留存在SKU匹配的Basket-Order组合，修改为INNER JOIN并调整统计逻辑：

SELECT
    b.BasketNo AS BasketID,
    o.OrderNo,
    -- 单独统计当前Basket的总SKU数
    (SELECT COUNT(DISTINCT SKU) FROM Basket WHERE BasketNo = b.BasketNo) AS ItemCount,
    -- 统计匹配的SKU数量
    COUNT(DISTINCT b.SKU) AS MatchCount
FROM
    Basket b
INNER JOIN
    [Order] o ON b.SKU = o.SKU
GROUP BY
    b.BasketNo, o.OrderNo;

方法二：基于逗号分隔视图处理（仅当必须使用视图时） #

如果必须依赖已生成的带逗号分隔BasketSKUs、OrderSKUs的视图，需要先将字符串拆分为单独的SKU行，再进行匹配统计。以下以SQL Server为例（其他数据库可替换对应的字符串拆分函数，比如MySQL用SUBSTRING_INDEX结合序列表）：

假设视图名为SKUCombinedView，包含BasketID, BasketSKUs, OrderNo, OrderSKUs字段：

SELECT
    v.BasketID,
    v.OrderNo,
    -- 统计Basket的总SKU数
    (SELECT COUNT(*) FROM STRING_SPLIT(v.BasketSKUs, ',') WHERE value <> '') AS ItemCount,
    -- 统计匹配的SKU数量（去重）
    COUNT(DISTINCT s_basket.value) AS MatchCount
FROM
    SKUCombinedView v
-- 拆分Basket的SKU字符串
CROSS APPLY
    STRING_SPLIT(v.BasketSKUs, ',') s_basket
-- 拆分Order的SKU字符串并匹配
INNER JOIN
    STRING_SPLIT(v.OrderSKUs, ',') s_order ON s_basket.value = s_order.value
WHERE
    -- 过滤空SKU值
    s_basket.value <> ''
    AND s_order.value <> ''
GROUP BY
    v.BasketID, v.OrderNo;

注意事项 #

• 逗号分隔的SKU字符串不符合关系型数据库的设计范式，处理时会产生额外的性能开销，数据量较大时不建议使用。
• 若SKU存在重复值，务必使用DISTINCT确保统计结果准确。

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