Query to exclude row based on another row's filter
I'm using Oracle 10g.
Question: How can I write query to return just ID only if ALL the codes for that ID end in 6?
I don't want ID=1 beca开发者_Go百科use not all its codes end in 6.
TABLE_A
ID Code
===============
1 100
1 106
2 206
3 316
3 326
4 444
Desired Result:
ID
==
2
3
You simply want each ID where the count of rows for that id is the same as the count of rows where the third digit is six.
SELECT ID
FROM TABLE_A
GROUP BY ID
HAVING COUNT(*) = COUNT(CASE WHEN SUBSTR(code,3,1) = '6' THEN 1 END)
Try this:
SELECT DISTINCT b.id
FROM (
SELECT id,
COUNT(1) cnt
FROM table_a
GROUP BY id
) a,
(
SELECT id,
COUNT(1) cnt
FROM table_a
WHERE CODE LIKE '%6'
GROUP BY id
)b
WHERE a.id = b.id
AND a.cnt = b.cnt
Alternative using ANALYTIC functions:
SELECT DISTINCT id
FROM
(
SELECT id,
COUNT(1) OVER(PARTITION BY id) cnt,
SUM(CASE WHEN code LIKE '%6' THEN 1 ELSE 0 END) OVER(PARTITION BY id) sm
FROM table_a
)
WHERE cnt = sm
精彩评论