如何用 SQL 求出「都关注了某100人」和「都收藏了某100帖子」的所有用户？
@无名啊，而且我觉得单个复杂SQL语句的可读性也没有拼接SQL来的清晰。还有，如果你使用封装好的数据库操作类，那么背后肯定是拼接SQL，不会是预先生成的复杂语句。
比如
function 搜索($名称, $手机) {
if (empty($名称) && empty($手机)) {
return null;
}
$db = new 数据库操作类();
$table = $db->select('用户信息表');
if (!empty($名称)) {
$table->where('名称', '=', $名称);
}
if (!empty($手机)) {
$table->where('手机', '=', $手机);
}
return $table->query();
}
这样的代码背后肯定是拼接SQL+预处理，没有单个复杂SQL的存在。
@无名啊，我只是想指出，实际的搜索词应该不会有100个那么多。所以拼接SQL带来的性能影响应该可以忽略不计。
然后再看你的限定条件：
注意：
尽量不要使用存储过程/函数、其他编程语言，以及 MySQL 8.0.17+ 的多值索引。
尽量使用 B+ 树索引（好像PostgreSQL有针对json的高效索引）
好像符合这个条件的也只有
SELECT * FROM 员工信息表 WHERE
员工ID IN (SELECT 员工ID FROM 员工技能表 WHERE 技能 = '技能1')
AND 员工ID IN (SELECT 员工ID FROM 员工技能表 WHERE 技能 = '技能2')
AND 员工ID IN (SELECT 员工ID FROM 员工手机表 WHERE 手机 = '手机1')
AND 员工ID IN (SELECT 员工ID FROM 员工手机表 WHERE 手机 = '手机2');
因为JSON_CONTAINS是函数，并且使用多值索引
又是JOIN又是GROUP BY的，它真的能高效运行吗？
WITH
除以要求技能表(员工ID) AS (
SELECT DISTINCT 员工ID
FROM 员工技能表 AS ES1
WHERE NOT EXISTS (
SELECT *
FROM 要求技能表
WHERE NOT EXISTS (
SELECT *
FROM 员工技能表 AS ES2
WHERE ES2.员工ID = ES1.员工ID
AND ES2.技能 = 要求技能表.技能 ))
),
除以要求手机表(员工ID) AS (
SELECT DISTINCT 员工ID
FROM 员工手机表 AS ES1
WHERE NOT EXISTS (
SELECT *
FROM 要求手机表
WHERE NOT EXISTS (
SELECT *
FROM 员工手机表 AS ES2
WHERE ES2.员工ID = ES1.员工ID
AND ES2.手机 = 要求手机表.手机 ))
)
-- 如果要显示员工详细信息的话
SELECT 员工表.*
FROM 除以要求技能表
JOIN 除以要求手机表 USING(员工ID)
JOIN 员工表 USING(员工ID)
GROUP BY 员工ID;
@老虎会游泳，《SQL 解惑（第二版）》谜题 21 飞机与飞行员，也提到过类似问题。但他是只有一个条件的：
有一个表包含飞行员和他们能够驾驶的飞机，还有一个表包含停在飞机棚中的飞机。需要找出能够驾驶飞机棚中每一架飞机的飞行员姓名。
别人除了用『关系除法』外，还用了另一种简短一些的方法
SELECT 飞行员
FROM 飞行员会开啥飞机表 AS PS, 停在飞机棚的飞机表 AS H
WHERE PS.飞机 = H.飞机
GROUP BY PS.飞行员
HAVING COUNT(PS.飞机) = (SELECT COUNT(*) FROM 停在飞机棚的飞机表)
哪个方法快？别人实验总结：
COUNT(结果)<COUNT(飞行员会开啥飞机表)* 0.25 时，『关系除法』较好，否则上面的方法好
| 飞行员 | 飞机 |
|---|---|
| Celko | Piper Cub |
| Higgins | B-52 Bomber |
| Higgins | F-14 Fighter |
| Higgins | Piper Cub |
| Jones | B-52 Bomber |
| Jones | F-14 Fighter |
| Smith | B-1 Bomber |
| Smith | B-52 Bomber |
| Smith | F-14 Fighter |
| Wilson | B-1 Bomber |
| Wilson | B-52 Bomber |
| Wilson | F-14 Fighter |
| Wilson | F-17 Fighter |
| 飞机 |
|---|
| B-1 Bomber |
| B-52 Bomber |
| F-14 Fighter |
| 飞行员 |
|---|
| Smith |
| Wilson |
在公共聊天室和文件上传发过言，并且在超级灌水和网页插件版块发过帖的用户：
SELECT c3.uid
FROM
(
SELECT uid FROM
(
SELECT uid, count(*) as c FROM
(
SELECT DISTINCT uid, room
FROM hu60_addin_chat_data
WHERE room IN ('公共聊天室', '文件上传')
) c1
GROUP BY uid
) c2
WHERE c = 2
) c3
INNER JOIN
(
SELECT uid FROM
(
SELECT uid, count(*) as c FROM
(
SELECT DISTINCT uid, forum_id
FROM hu60_bbs_topic_meta
WHERE forum_id IN (
SELECT id
FROM hu60_bbs_forum_meta
WHERE name IN ('超级灌水', '网页插件')
)
) f1
GROUP BY uid
) f2
WHERE c = 2
) f3
ON
c3.uid=f3.uid
用HAVING子句减少一层嵌套：
在公共聊天室和文件上传发过言，并且在超级灌水和网页插件版块发过帖的用户：
SELECT c2.uid
FROM
(
SELECT uid FROM
(
SELECT DISTINCT uid, room
FROM hu60_addin_chat_data
WHERE room IN ('公共聊天室', '文件上传')
) c1
GROUP BY uid
HAVING count(*) = 2
) c2
INNER JOIN
(
SELECT uid FROM
(
SELECT DISTINCT uid, forum_id
FROM hu60_bbs_topic_meta
WHERE forum_id IN (
SELECT id
FROM hu60_bbs_forum_meta
WHERE name IN ('超级灌水', '网页插件')
)
) f1
GROUP BY uid
HAVING count(*) = 2
) f2
ON
c2.uid=f2.uid
比这种写法慢很多啊：
SELECT uid, name FROM hu60_user WHERE
uid IN (SELECT DISTINCT uid FROM hu60_addin_chat_data WHERE room = '公共聊天室')
AND uid IN (SELECT DISTINCT uid FROM hu60_addin_chat_data WHERE room = '文件上传')
AND uid IN (SELECT DISTINCT uid FROM hu60_bbs_topic_meta WHERE forum_id = (
SELECT id FROM hu60_bbs_forum_meta WHERE name = '超级灌水'
))
AND uid IN (SELECT DISTINCT uid FROM hu60_bbs_topic_meta WHERE forum_id = (
SELECT id FROM hu60_bbs_forum_meta WHERE name = '网页插件'
));
@无名啊，执行优化可能不蠢，但只是可能，调查研究需要时间。所以我何不把可能性掐灭在萌芽中，给它一定不蠢的SQL语句呢？