SQL语句捕获具有不同ID的相似行(SQL statement to capture similar rows with different ids)

SQL语句捕获具有不同ID的相似行(SQL statement to capture similar rows with different ids)

我有一张桌子：

term_id course_id 1 592 1 603 2 592 2 603 2 700

如何创建一个查询来选择第1和第2项中的所有course_ids？

I have a table with:

term_id course_id 1 592 1 603 2 592 2 603 2 700

How can I create a query to select ALL course_ids that are in term 1 AND 2?

最满意答案

SELECT course_id FROM T WHERE term_id=1 INTERSECT SELECT course_id FROM T WHERE term_id=2

要么

SELECT course_id FROM T WHERE term_id IN (1,2) GROUP BY course_id HAVING COUNT(DISTINCT term_id) = 2 SELECT course_id FROM T WHERE term_id=1 INTERSECT SELECT course_id FROM T WHERE term_id=2

Or

SELECT course_id FROM T WHERE term_id IN (1,2) GROUP BY course_id HAVING COUNT(DISTINCT term_id) = 2