MySQL不支持INTERSECT：改写查询获取大西洋地区购表客户及数量

Got it, since MySQL doesn't support the INTERSECT operator, we don't even need to simulate it here—we can combine all your filtering conditions into a single, efficient join query that gets exactly what you need.

Here's the corrected, MySQL-compatible query: #

SELECT 
    c.Customer_name,
    COUNT(m.Prod_id) AS no_of_tables_purchased
FROM 
    cust_dimen c
INNER JOIN 
    market_fact m ON m.Cust_id = c.Cust_id
INNER JOIN 
    prod_dimen p ON p.Prod_id = m.Prod_id
WHERE 
    c.Region = 'ATLANTIC'
    AND p.Product_Category = 'TABLES' -- Adjust this field name if your product category column has a different name
GROUP BY 
    c.Customer_name;

Key Notes: #

• I replaced the old comma-separated joins with explicit INNER JOIN syntax—it's more readable and avoids accidental cross-joins.
• The original query had a mistake in the GROUP BY clause: grouping by m.prod_id would give you counts per product, not per customer. We need to group by c.Customer_name to get each customer's total table purchases.
• We don't need INTERSECT here because our WHERE clause directly enforces both conditions: customers from the Atlantic region and customers who purchased tables.
• If you want to count distinct orders (instead of total table units purchased), swap COUNT(m.Prod_id) with COUNT(DISTINCT m.Ord_id).

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