mysql - 解决 mysql 中的 intersect 关键字

Question

我需要在 mysql 数据库中获取一些结果的交集。但是google了一下才知道没有mysql intersect关键字可用。以下是我的示例表。

gene table
+------+--------+---------+
| id   | symbol | test_id |
+------+--------+---------+
|   -1 | A      |      -1 |
|    8 | A      |       3 |
|    9 | G      |       3 |
|   -1 | A      |      -1 |
|   -2 | B      |      -1 |
|   -3 | C      |      -1 |
|    1 | A      |       1 |
|    2 | B      |       1 |
|    3 | C      |       1 |
|    4 | B      |       2 |
|    5 | C      |       2 |
|    6 | D      |       2 |
|    7 | E      |       2 |
|    8 | A      |       3 |
|    9 | G      |       3 |
|   10 | F      |       3 |
|   11 | C      |       3 |
|   12 | C      |       4 |
|   13 | G      |       4 |
|   14 | F      |       4 |
|   15 | M      |       4 |
|   16 | N      |       4 |
+------+--------+---------+

test table
+------+-------+
| id   | name  |
+------+-------+
|   -1 | test0 |
|    3 | test3 |
|   -1 | test0 |
|    1 | test1 |
|    2 | test2 |
|    3 | test3 |
|    4 | test4 |
+------+-------+

现在我想制定一个查询，它会给我提供的基因的常见测试。例如我将提供基因 A、B、C，我应该得到以下结果:

 id    name   id    symbol 
---------------------------    
 -1 | test0 |   -1 | A
 -1 | test0 |   -2 | B
 -1 | test0 |   -3 | C
  1 | test1 |    1 | A
  1 | test1 |    2 | B
  1 | test1 |    3 | C

select distinct t.id, t.name, g.id, g.symbol from tests t 
join genes g on t.id = g.test_id 
where g.symbol = 'A' and g.symbol='B' and g.symbol='C';

请帮我构造查询。

Answer 1

诀窍是根据您的条件过滤记录，然后按 test.id 分组以检查它是否符合所有条件:

SELECT  t.id
FROM    tests AS t
        INNER JOIN genes AS g
            ON t.id = g.test_id 
WHERE   g.symbol in ('A','B','C')
GROUP BY t.id
HAVING COUNT(DISTINCT g.symbol) = 3;

所以关键行在这里:

HAVING COUNT(DISTINCT g.symbol) = 3;

如果像测试 2 一样，只有“B”匹配，则计数将返回 1，测试将被排除。您要检查的项目数量必须与 HAVING 子句中的数量相匹配。

如果您随后需要获取完整的数据，您只需要重新连接到您的表:

SELECT  t.id, t.name, g.id, g.symbol
FROM    genes AS g
        INNER JOIN
        (   SELECT  t.id, t.name
            FROM    tests AS t
                    INNER JOIN genes AS g
                        ON t.id = g.test_id 
            WHERE   g.symbol in ('A','B','C')
            GROUP BY t.id, t.name
            HAVING COUNT(DISTINCT g.symbol) = 3
        ) t
            ON t.id = g.test_id;

Example on SQL Fiddle