@老虎会游泳，补充测试完了

修改了下结果列表，增加了方法二耗时对比

基本结论

1. 加不加DISTINCT耗时都一样
2. 新增user表，再将方法一换成你的方法后，耗时更像方法二了。。

结果

用到的SQL

0. 新增user表

CREATE TABLE u(uid INTEGER PRIMARY KEY) WITHOUT ROWID;
INSERT INTO u SELECT value FROM generate_series(1, 10000000);

1. 每表1个参数，按照你的SQL测试

SELECT count(*) FROM u
WHERE uid IN (SELECT uid FROM a WHERE num = 50)
  AND uid IN (SELECT uid FROM b WHERE num = 100)

2. 每表2个参数，按照你的SQL测试

SELECT count(*) FROM u
WHERE uid IN (SELECT uid FROM a WHERE num = 10)
  AND uid IN (SELECT uid FROM a WHERE num = 20)
  AND uid IN (SELECT uid FROM b WHERE num = 60)
  AND uid IN (SELECT uid FROM b WHERE num = 70)

3. 每表2个参数，添加DISTINCT

SELECT count(*) FROM u
WHERE uid IN (SELECT DISTINCT uid FROM a WHERE num = 30)
  AND uid IN (SELECT DISTINCT uid FROM a WHERE num = 40)
  AND uid IN (SELECT DISTINCT uid FROM b WHERE num = 80)
  AND uid IN (SELECT DISTINCT uid FROM b WHERE num = 90)

4. 每表3个参数，按照你的SQL测试

SELECT count(*) FROM u
WHERE uid IN (SELECT uid FROM a WHERE num = 200)
  AND uid IN (SELECT uid FROM a WHERE num = 210)
  AND uid IN (SELECT uid FROM a WHERE num = 220)
  AND uid IN (SELECT uid FROM b WHERE num = 230)
  AND uid IN (SELECT uid FROM b WHERE num = 240)
  AND uid IN (SELECT uid FROM b WHERE num = 250)

@无名啊，嗯，所以我们能不能说，拼接JOIN SELECT WHERE语句是最佳解决方案？

SELECT count(*)
  FROM (SELECT uid FROM a WHERE num = 10)
  JOIN (SELECT uid FROM b WHERE num = 60) USING(uid)
  JOIN (SELECT uid FROM a WHERE num = 20) USING(uid)
  JOIN (SELECT uid FROM b WHERE num = 70) USING(uid)

@老虎会游泳，

嗯，所以我们能不能说，拼接JOIN SELECT WHERE语句是最佳解决方案？

目前看来，SQLite上成立

所以在“员工信息表”里不断排除范围可能比让其他表互相做JOIN更快。

我原本也是这个思路：根据num取得uid，然后不断缩小uid数量

但仔细想了想，觉得目前这个帖子里，没有一个SQL是按照这个思路执行的。

因为，所有的表结构，都是(num, uid)。上一步积累的uid，没法用到下一步的过滤

（我觉得B表可以改成(uid, num)，来利用上A表查到的uid结果列表）

（我再想想。。）

不这么干，而直接全部变成(uid, num)，就等着扫全表，来一个个用户检查“是否有num”吧

（SQLite倒是有个“跳跃扫描”，可以在一定程度上不遵循“最左原则”。但这样也是 1kw 次 uid: 1~1kw, num = ? 次匹配，和扫全表比，谁快还真的说不定）

@爱特，

1. 链表是啥？这样么？

   主键ID	uid	num	下一节点ID
   1	1	1	3
   2	2	1	0
   3	1	2	0

2. 还是没懂你的“链表”，等你回复

3. “添加缓存”是指啥？如：num = 1时，有uid: [1, 2]？