php - MySQL 加入 "tolerance"

Question

我想为以下查询添加某种“容差”。这意味着，我可以指定一个值来表示四个(子)选择中有多少返回行 > 0。因此，如果该值为 2，我只想连接这两个表。有没有办法实现这一点？

SELECT distinct(user_id) FROM 

(SELECT user_id FROM table1 WHERE ...) as t1
INNER JOIN
(SELECT user_id FROM table1 WHERE ...) as t2
ON t1.user_id=t2.user_id
INNER JOIN
(SELECT user_id FROM table1 WHERE ...) as t3
ON t1.user_id=t3.user_id
INNER JOIN
(SELECT user_id FROM table1 WHERE ...) as t4
ON t1.user_id=t4.user_id

编辑:每个子查询的可能结果如下:

t1 t2 t3 t4
 0  0  0
 1  1  1  1
 2  2  2  2
 3        3

如果将所有这些子结果连接起来，将得到:1,2。如果我添加容差因子 1，我希望结果为 0,1,2，因为只缺少一个“0”。如果因子为 2，则结果将为 0,1,2,3，因为缺少两个“3”和一个“0”。我希望这能让它更清楚。

Answer 1

如果我理解了您的问题，您可以在子选择和过滤器中添加一个变量:

SELECT distinct(user_id) FROM     
(SELECT user_id, 1 as table_from FROM table1 WHERE ...) as t1
INNER JOIN
(SELECT user_id, 2 as table_from FROM table1 WHERE ...) as t2
ON t1.user_id=t2.user_id
INNER JOIN
(SELECT user_id, 3 as table_from FROM table1 WHERE ...) as t3
ON t1.user_id=t3.user_id
INNER JOIN
(SELECT user_id, 4 as table_from FROM table1 WHERE ...) as t4
ON t1.user_id=t4.user_id
WHERE table_from <= 2;