如何将IN条件的查询结果定义为变量复用？附DB-Fiddle示例

Hey there! Let's work through fixing this issue so you don't have to repeat that subquery over and over. The problem with your original CTE attempt is that you can't directly reference the CTE name inside IN() — you need to explicitly select the column from it instead. Here are a few solid, practical solutions:

1. Correctly Use a CTE (The Cleanest Approach) #

CTEs are made for reusing query logic, you just need to properly query the CTE in each IN clause. I simplified your original query a bit too, since the inner subquery was redundant:

WITH high_sales_products AS (
    SELECT product 
    FROM sales 
    WHERE sales_quantity > 600
)
SELECT product, sales_quantity 
FROM sales 
WHERE product IN (SELECT product FROM high_sales_products)
GROUP BY product, sales_quantity;

If you ever need to reuse this filtered product list in multiple places (like different WHERE clauses or joins), this pattern works perfectly — just drop (SELECT product FROM high_sales_products) wherever you need that list.

2. Replace IN with a JOIN (Better for Large Datasets) #

Another great option is swapping IN for a JOIN — this often performs better with larger tables, and it naturally reuses your filtered product list without extra subqueries:

WITH high_sales_products AS (
    SELECT product 
    FROM sales 
    WHERE sales_quantity > 600
)
SELECT s.product, s.sales_quantity
FROM sales s
INNER JOIN high_sales_products hsp 
    ON s.product = hsp.product
GROUP BY s.product, s.sales_quantity;

3. Use a User Variable (MySQL-Specific Trick) #

If you're working with MySQL, you can store the product list in a user variable. Keep in mind this uses GROUP_CONCAT, which has a default length limit, so it's best for smaller datasets:

-- First, store the comma-separated list of products
SET @high_sales_products = (
    SELECT GROUP_CONCAT(DISTINCT product) 
    FROM sales 
    WHERE sales_quantity > 600
);

-- Then filter using FIND_IN_SET
SELECT product, sales_quantity
FROM sales
WHERE FIND_IN_SET(product, @high_sales_products)
GROUP BY product, sales_quantity;

Why Your Original Query Failed #

When you wrote WHERE product IN (cte), the database doesn't know how to interpret the CTE as a list of values. IN() expects either a comma-separated list of values or a subquery that returns a single column — that's why you need to specify (SELECT product FROM cte) instead.

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