SQL statement to capture similar rows with different 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 开发者_如何学JAVA1 AND 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

SELECT * INTO #TEMP1
FROM T
WHERE TERM_ID = 1

SELECT * INTO #TEMP2
FROM T
WHERE TERM_ID = 2

SELECT DISTINCT COURSE_ID
FROM #TEMP1
WHERE COURSE_ID IN (SELECT DISTINCT COURSE_ID FROM #TEMP2)

Martin's answers are concise and clever, but I'll throw in two more approaches which may be easier to parse, depending on what you're used to reading of course.

Without making any assumptions about your schema:

SELECT DISTINCT
  course_id
FROM
  MyTable AS T1
WHERE
      term_id = 1
  AND EXISTS (SELECT * FROM MyTable AS T2 WHERE T1.course_Id = T2.course_id AND T2.term_id = 2)

If, as I expect, { course_id, term_id } is unique, you can also do this:

SELECT
  T1.course_id
FROM
  MyTable AS T1
  INNER JOIN MyTable AS T2 ON T1.course_id = T2.course_id
WHERE
      T1.term_id = 1
  AND T2.term_id = 2

The more general case, to get all courses in multiple terms:

select course_id
from foo
group by course_id
having count(term_id) > 1