如何基于单表多子查询结果的交集执行SELECT查询？

当然可以实现！要找到同时满足这三个查询条件的id（也就是三个结果集的交集），我整理了几种实用的方案，你可以根据自己使用的数据库来选择：

1. 用INTERSECT关键字（最简洁直观） #

如果你的数据库支持INTERSECT（比如PostgreSQL、SQL Server、Oracle 10g及以上版本），直接把三个查询用INTERSECT连接就行——它会自动帮你提取三个结果里的共同id：

SELECT id FROM ( SELECT * FROM my_table ORDER BY columnA DESC LIMIT 600 ) AS aliasA WHERE revenue > 10000
INTERSECT
SELECT id FROM ( SELECT * FROM my_table ORDER BY columnB DESC LIMIT 400, 999999999999 ) AS aliasB WHERE revenue > 10000
INTERSECT
SELECT id FROM ( SELECT * FROM my_table ORDER BY columnC DESC LIMIT 800 ) AS aliasC WHERE revenue > 10000;

2. 嵌套IN子句（兼容更多数据库） #

要是你的数据库不支持INTERSECT（比如MySQL 5.x及更早版本），可以用IN子句来嵌套查询，确保id同时出现在三个筛选结果里：

SELECT id 
FROM ( SELECT * FROM my_table ORDER BY columnA DESC LIMIT 600 ) AS aliasA 
WHERE revenue > 10000
AND id IN (
    SELECT id FROM ( SELECT * FROM my_table ORDER BY columnB DESC LIMIT 400, 999999999999 ) AS aliasB WHERE revenue > 10000
)
AND id IN (
    SELECT id FROM ( SELECT * FROM my_table ORDER BY columnC DESC LIMIT 800 ) AS aliasC WHERE revenue > 10000
);

3. 用JOIN关联（大数据量下更高效） #

如果你的表数据量比较大，用JOIN关联三个子查询的结果可能性能更好，这种方式通过匹配id来找到交集：

SELECT a.id
FROM (
    SELECT id FROM ( SELECT * FROM my_table ORDER BY columnA DESC LIMIT 600 ) AS sub WHERE revenue > 10000
) AS a
JOIN (
    SELECT id FROM ( SELECT * FROM my_table ORDER BY columnB DESC LIMIT 400, 999999999999 ) AS sub WHERE revenue > 10000
) AS b ON a.id = b.id
JOIN (
    SELECT id FROM ( SELECT * FROM my_table ORDER BY columnC DESC LIMIT 800 ) AS sub WHERE revenue > 10000
) AS c ON a.id = c.id;

小提示 #

• 关于LIMIT 400, 999999999999：有些数据库（比如PostgreSQL）支持更优雅的写法ORDER BY columnB DESC OFFSET 400，不需要写超大的LIMIT值，你可以根据自己的数据库语法调整。
• 如果需要保留重复的id（虽然id一般是主键不会重复），部分数据库支持INTERSECT ALL，它会保留重复项的交集，而不是自动去重。

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