如何用子查询实现两个含JOIN和GROUP BY的SELECT查询交集？

表结构 #

表a：

表b：

已实现的查询 #

1. 查询2022-01购买的gasoline类型品牌：

SELECT a.Brand, COUNT(Contract) AS Bought
FROM a
INNER JOIN b ON b.Brand = a.Brand AND b.TYPE = 'gasoline'
WHERE a.Buy = 1 AND a.Date = '2022-01-01'
GROUP BY a.Brand

2. 查询2022-01之后0-3个月内销售的electric类型品牌：

SELECT a.Brand, COUNT(Contract) AS Sold
FROM a
INNER JOIN b ON b.Brand = a.Brand AND b.TYPE = 'electric'
WHERE a.Sale = 1 AND a.Date BETWEEN '2022-01-01' AND ADD_MONTHS('2022-01-01', 3)
GROUP BY a.Brand

需求 #

筛选出同时满足以下两个条件的品牌：

• 属于gasoline类型且在2022-01有购买记录
• 属于electric类型且在2022-01之后0-3个月内有销售记录

解决方案 #

方法1：使用IN子查询

分别通过子查询获取满足单个条件的品牌列表，再取交集：

SELECT DISTINCT b.Brand
FROM b
WHERE b.Brand IN (
    -- 筛选gasoline类型+2022-01有购买的品牌
    SELECT a.Brand
    FROM a
    INNER JOIN b ON b.Brand = a.Brand AND b.Type = 'gasoline'
    WHERE a.Buy = 1 AND a.Date = '2022-01-01'
)
AND b.Brand IN (
    -- 筛选electric类型+2022-01后0-3个月有销售的品牌
    SELECT a.Brand
    FROM a
    INNER JOIN b ON b.Brand = a.Brand AND b.Type = 'electric'
    WHERE a.Sale = 1 AND a.Date BETWEEN '2022-01-01' AND ADD_MONTHS('2022-01-01', 3)
)

方法2：使用EXISTS子查询

通过两个EXISTS子句分别验证品牌是否满足两个条件，性能通常优于IN子查询：

SELECT DISTINCT b.Brand
FROM b
WHERE EXISTS (
    SELECT 1
    FROM a
    INNER JOIN b b_gas ON b_gas.Brand = a.Brand AND b_gas.Type = 'gasoline'
    WHERE a.Buy = 1 AND a.Date = '2022-01-01' AND b_gas.Brand = b.Brand
)
AND EXISTS (
    SELECT 1
    FROM a
    INNER JOIN b b_elec ON b_elec.Brand = a.Brand AND b_elec.Type = 'electric'
    WHERE a.Sale = 1 AND a.Date BETWEEN '2022-01-01' AND ADD_MONTHS('2022-01-01', 3) AND b_elec.Brand = b.Brand
)

方法3：JOIN子查询结果

将两个子查询的结果作为临时表，通过INNER JOIN获取共同品牌，适合需要同时统计购买/销售数据的场景：

SELECT t1.Brand, t1.Bought, t2.Sold
FROM (
    SELECT a.Brand, COUNT(Contract) AS Bought
    FROM a
    INNER JOIN b ON b.Brand = a.Brand AND b.Type = 'gasoline'
    WHERE a.Buy = 1 AND a.Date = '2022-01-01'
    GROUP BY a.Brand
) t1
INNER JOIN (
    SELECT a.Brand, COUNT(Contract) AS Sold
    FROM a
    INNER JOIN b ON b.Brand = a.Brand AND b.Type = 'electric'
    WHERE a.Sale = 1 AND a.Date BETWEEN '2022-01-01' AND ADD_MONTHS('2022-01-01', 3)
    GROUP BY a.Brand
) t2 ON t1.Brand = t2.Brand

注意事项 #

• 表a中的Date示例格式为22-02，但查询中使用的是2022-01-01，请确保实际数据的日期格式与查询条件一致，避免过滤错误。
• 如果品牌可能对应多种类型（如Fiat同时属于gasoline和diesel），上述查询会自动匹配对应类型的条件，无需额外处理。

内容的提问来源于stack exchange，提问作者prog-amateur