问题描述
我有下面的测试表，表中有以下几行.
I have the below test table with the following rows in the table.
table: test_set(id (pk), pid int, fc int, fl int)
pid fc fl
1 7 30
1 8 31
1 9 35
2 7 39
2 8 40
现在如果我跑
SELECT pid FROM test_set WHERE fl=30 OR fl=35 GROUP BY pid;
#Result :
pid
---
1
正如预期的那样，但如果我想运行
As expected but if I want to run
SELECT pid FROM test_set WHERE fl=30 AND fl=35 GROUP BY pid;
#Result :
result set (0) # Nothing matched!
这太符合预期了，但我想在这里超出预期.我的逻辑是 fl=30 和 fl=35 都有 pid=1 共同点，即当它们相交时，它们产生 pid=1
This is too as expected but I want to go beyond expectation here. My logic here is that fl=30 and fl=35 both have pid=1 in common i.e. when they are intersected they yeilds pid=1
所以具体来说，我需要具有一个或多个 pid 的 fl 列的多个值的结果.
So to be specific I need the result of multiple values of fl column that have one or more pid in common.
我已经阅读了这个这个并且也有评论.
I have already read this this and commented there too.
推荐答案
关于:
SELECT pid, COUNT(DISTINCT fl) AS count, GROUP_CONCAT(DISTINCT fl) list
FROM test_set
GROUP BY pid HAVING count > 1;
?
输出:
+------+-----------+------------------+
| pid | count | list |
+------+-----------+------------------+
| 1 | 3 | 30,31,35 |
| 2 | 2 | 39,40 |
+------+-----------+------------------+
给定两个 fl 值:
SELECT pid, COUNT(DISTINCT fl) AS count
FROM test_set
WHERE fl IN (30, 35)
GROUP BY pid HAVING count = 2;
输出:
+------+-------+
| pid | count |
+------+-------+
| 1 | 2 |
+------+-------+
给定三个 fl 值:
SELECT pid, COUNT(DISTINCT fl) AS count
FROM test_set
WHERE fl IN (30, 31, 35)
GROUP BY pid HAVING count = 3;
输出:
+------+-------+
| pid | count |
+------+-------+
| 1 | 3 |
+------+-------+
pid 和 fl 上有索引是很好的.
It's good to have indexes on pid and fl.
这篇关于MySQL 选择查询 ANDed 同一列的多个条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！