mysql - 如何选择所有行都具有的行的 ID

Question

有一张 table :

+-------------+----------+
|stud_group_id|subject_id|
+-------------+----------+
|    1g       |   1s     |
+-------------+----------+
|    1g       |   2s     |
+-------------+----------+
|    1g       |   3s     |
+-------------+----------+
|    2g       |   1s     |
+-------------+----------+
|    2g       |   2s     |
+-------------+----------+
|    3g       |   1s     |
+-------------+----------+
|    3g       |   2s     |
+-------------+----------+
|    3g       |   4s     |
+-------------+----------+

我需要选择所有 stud_group_id 学习的 subject_id。我期待

+----------+
|subject_id|
+----------+
|   1s     |
+----------+
|   2s     |
+----------+

我该怎么做？谢谢

Answer 1

试试这个:

select 
    subject_id,
    count(distinct stud_group_id) as cnt
from
    <your_table>
group by
    subject_id
having cnt=(select count(distinct stud_group_id) from <your table>)