How to check group for containing certain elements in SQL?

I have a simple table:

id num

1 7
1 5
1 4

2 5
2 4
2 7

3 4
3 7开发者_开发百科

How to select ids having num 5 as well as 7 and 4

For this example ids: 1, 2

---

SELECT `id` FROM `table`
WHERE `num` IN (4, 5, 7)
GROUP BY `id`
HAVING COUNT(*) = 3

---

This is a very slightly amended version of zerkms' answer:

SELECT `id` FROM `table` 
WHERE `num` IN (4, 5, 7) 
GROUP BY `id` 
HAVING COUNT(DISTINCT `num`) = 3 

---

How about

;WITH T AS
(
    SELECT ID, NUM, COUNT(*) OVER(PARTITION BY ID) AS ROWNO,
           SUM(NUM) OVER(PARTITION BY ID) AS SUMNUM
    FROM TABLE
)
SELECT ID, NUM
FROM T
WHERE ROWNO = 3 AND SUMNUM = 16