选择具有相同外键但另一列具有一组值的行

Question

我设计了一个数据库，它以这样的格式存储数据，其中两列都是指向不同表的外键。这是一个简化的版本。

RNA_id | Experiment_id |
   1   |       a       |
   1   |       b       |
   2   |       a       |
   2   |       b       |
   2   |       c       |
   3   |       b       |
   4   |       a       |
   4   |       c       |

我希望选择具有所有三个实验ID的行。在本例中，结果应该是

RNA_id | Experiment_id |
   2   |       a       |
   2   |       b       |
   2   |       c       |

我试过了

GROUP BY RNA_id HAVING COUNT (DISTINCT Experiment_id)=3

但这只是导致我有一个看似随机分类的行。

数据库已经相当大了，我的查询已经相当复杂了，另外，我可能想要扩展到4个或更多的Experiment_ids。

下面是我的查询的一个净化版本，因为它很复杂，我不想解释我的整个数据结构

我现有的查询是：

SELECT RNA_id, Experiment_id, <data values>
FROM data
LEFT JOIN ref1
LEFT JOIN ref2
LEFT JOIN ref3
LEFT JOIN ref4
WHERE <required data parameters>
ORDER BY RNA_id
LIMIT 0,5000;

它应该返回大约700个值，但是当我将ORDER BY更改为前面的GROUP BY命令时，它返回了9个值，所有这些值都具有唯一的RNA_ids，并且应该返回大约100个值。

我的RNA_id实际上有3列，所以我可能只需要重做整个数据库就可以让这些解决方案中的任何一个生效。

我的解决方案

我想出了如何使用3列标识符

AND (RNA_id1, RNA_id2, RNA_id3) IN (SELECT RNA_id1, RNA_id2, RNA_id3
FROM data
WHERE <parameter>
GROUP BY RNA_id1, RNA_id2, RNA_id3
HAVING COUNT (DISTINCT Experiment_id)=3)

这也适用于不同数量的Experiment_ids

Answer 1

我建议只返回同时具有这三个属性的RNA_id：

select RNA_id
from t
where Experiment_id in ('a', 'b', 'c')
group by RNA_id
having count(*) = 3;

如果您可以有重复项，则使用count(distinct experiment_id)。

如果您需要原始行，那么在MySQL 8+中，您可以使用窗口函数对其进行调整：

select t.*
from (select t.*, count(*) over (partition by RNA_id) as cnt
      from t
      where Experiment_id in ('a', 'b', 'c')
     ) t
where cnt = 3;

Answer 2

SELECT *
FROM Table1 t1
WHERE ( SELECT COUNT(DISTINCT `Experiment_id`)
        FROM Table1 t2
        WHERE t2.`RNA_id` = t1.`RNA_id`
          AND t2.Experiment_id in ('a', 'b', 'c') -- if you have more than 3 experiment
      )  = 3

Answer 3

如果a、b和c是Experiment_id的唯一可能值，那么您需要将查询放在WHERE子句中，如下所示：

select *
from tablename
where RNA_id in (
  select RNA_id from tablename
  group by RNA_id
  having count(distinct Experiment_id) = 3
)

如果还有其他值：

select *
from tablename
where
  Experiment_id in ('a', 'b', 'c') 
  and RNA_id in (
  select RNA_id from tablename
  where Experiment_id in ('a', 'b', 'c')
  group by RNA_id
  having count(distinct Experiment_id) = 3  
)

如果您希望扩展到3个以上的值，则可以很容易地更改这些查询。