查询以根据另一行的过滤器排除行

发布于 2024-11-15 06:44:18 字数 238 浏览 5 评论 0原文

我正在使用Oracle 10g。

问题：如果该 ID 的所有代码都以 6 结尾，如何编写查询以仅返回 ID？

我不希望 ID=1，因为并非所有代码都以 6 结尾。

TABLE_A

ID  Code
===============
1   100  
1   106
2   206
3   316
3   326
4   444

Desired Result:
ID
==
2
3

原文

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 because 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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

沉溺在你眼里的海 2024-11-22 06:44:18

您只需要每个 ID 的行数与第三位数字为 6 的行数相同。

SELECT ID
FROM TABLE_A
GROUP BY ID
HAVING COUNT(*) = COUNT(CASE WHEN SUBSTR(code,3,1) = '6' THEN 1 END)

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)

回复收藏 0 原文

寻找一个思念的角度 2024-11-22 06:44:18

试试这个：

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

使用分析函数的替代方案：

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

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

回复收藏 0 原文

~没有更多了~