SQL Server中基于另一表统计产品符合条件的国家数量

我有Products和Discounts两张表，需要统计每个产品对应的满足**Qty（产品数量）<= Size（折扣阈值）**条件的国家数量。

表结构及数据 #

CREATE TABLE Products 
(
    P_Name VARCHAR(50), 
    Qty INT
);

INSERT INTO Products (P_Name, Qty)
VALUES 
    ('Product A', 9),
    ('Product B', 2);

CREATE TABLE Discounts 
(
    C_Name VARCHAR(50),
    Size INT
);

INSERT INTO Discounts (C_Name, Size) 
VALUES 
    ('USA', 2 ),
    ('Germany', 4),
    ('Brazil', 5),
    ('Russia', 6);

预期输出 #

P_Name     Qty  Count
---------------------
Product A   9    4
Product B   2    1

说明：Product A的Qty为9，满足条件的国家有4个；Product B的Qty为2，满足条件的国家有1个。

我尝试的错误SQL #

SELECT *,
    COUNT (
        CASE 
            WHEN qty <= (SELECT Size FROM Discounts) 
                THEN 1
            ELSE 0 
        END
    ) AS count
FROM
    Products;

问题分析与正确解法 #

错误原因 #

1. 子查询(SELECT Size FROM Discounts)返回多条记录，直接与Qty比较会触发单行子查询返回多行的错误；
2. COUNT()函数会统计所有非NULL值，ELSE 0中的0会被计入统计，导致结果错误；
3. 原SQL未关联两张表，无法实现每个产品与所有折扣记录的条件对比。

正确解法1：交叉连接+分组统计 #

SELECT 
    p.P_Name,
    p.Qty,
    COUNT(d.C_Name) AS Count
FROM Products p
CROSS JOIN Discounts d
WHERE p.Qty <= d.Size
GROUP BY p.P_Name, p.Qty;

逻辑：将每个产品与所有国家的折扣记录做交叉连接，筛选出满足Qty <= Size的记录后，按产品分组统计符合条件的国家数量。

正确解法2：子查询统计 #

SELECT 
    P_Name,
    Qty,
    (SELECT COUNT(*) FROM Discounts d WHERE p.Qty <= d.Size) AS Count
FROM Products p;

逻辑：对每个产品，通过子查询直接统计Discounts表中Size大于等于当前产品Qty的记录数，无需分组。

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