Oracle SQL：如何根据不同的行值筛选出行

bvn4nwqk 于 2023-08-03 发布在 Oracle

关注(0)|答案(3)|浏览(135)

我想根据以下条件过滤我的数据

对于一个ID，如果一个部件在特定的Types A和B下出现两次或两次以上，那么我想过滤该部件上的数据。

data个

| ID  | TYPE | PART |
|-----|------|------|
| 101 | A    | 10   |
| 101 | B    | 10   |
| 101 | B    | 10   |
| 101 | B    | 20   |
| 101 | C    | 30   |
| 102 | A    | 10   |
| 102 | B    | 25   |
| 103 | A    | 25   |
| 103 | B    | 25   |

字符串
output个

| ID  | Type | Part |
|-----|------|------|
| 101 | A    | 10   |
| 101 | B    | 10   |
| 101 | B    | 10   |
| 103 | A    | 25   |
| 103 | B    | 25   |

WITH data AS (
    SELECT 101 id, 'A' type, 10 part FROM dual UNION ALL
    SELECT 101 id, 'B' type, 10 part FROM dual UNION ALL
    SELECT 101 id, 'B' type, 10 part FROM dual UNION ALL
    SELECT 101 id, 'B' type, 20 part FROM dual UNION ALL
    SELECT 101 id, 'C' type, 30 part FROM dual UNION ALL
    SELECT 102 id, 'A' type, 10 part FROM dual UNION ALL
    SELECT 102 id, 'B' type, 25 part FROM dual UNION ALL
    SELECT 103 id, 'A' type, 25 part FROM dual UNION ALL
    SELECT 103 id, 'B' type, 25 part FROM dual
)
SELECT * FROM data;

的数据
another solution个

WITH data AS (
    SELECT 101 id, 'A' type, 10 part FROM dual UNION ALL
    SELECT 101 id, 'B' type, 10 part FROM dual UNION ALL
    SELECT 101 id, 'B' type, 10 part FROM dual UNION ALL
    SELECT 101 id, 'B' type, 20 part FROM dual UNION ALL
    SELECT 101 id, 'C' type, 30 part FROM dual UNION ALL
    SELECT 102 id, 'A' type, 10 part FROM dual UNION ALL
    SELECT 102 id, 'B' type, 25 part FROM dual UNION ALL
    SELECT 103 id, 'A' type, 25 part FROM dual UNION ALL
    SELECT 103 id, 'B' type, 25 part FROM dual UNION ALL
    SELECT 104 id, 'C' type, 30 part FROM dual UNION ALL
    SELECT 104 id, 'D' type, 30 part FROM dual
), data2 AS (
    SELECT data.*, COUNT(DISTINCT type) OVER (PARTITION BY id, part) cnt
    FROM data
    WHERE type IN ('A','B')
)
SELECT id, type, part
FROM data2
WHERE cnt > 1;

型

oracle

来源：https://stackoverflow.com/questions/76814234/oracle-sql-how-to-filter-out-rows-based-on-different-row-values

3条答案

按热度按时间

yhqotfr81#

试试这个（我在这里修复了104/103记录，假设与您的初始数据匹配）

WITH data AS (
    SELECT 101 id, 'A' type, 10 part FROM dual UNION ALL
    SELECT 101 id, 'B' type, 10 part FROM dual UNION ALL
    SELECT 101 id, 'B' type, 10 part FROM dual UNION ALL
    SELECT 101 id, 'B' type, 20 part FROM dual UNION ALL
    SELECT 101 id, 'C' type, 30 part FROM dual UNION ALL
    SELECT 102 id, 'A' type, 10 part FROM dual UNION ALL
    SELECT 102 id, 'B' type, 25 part FROM dual UNION ALL
    SELECT 103 id, 'A' type, 25 part FROM dual UNION ALL
    SELECT 103 id, 'B' type, 25 part FROM dual
   ),
   w_cnt as
      ( SELECT id, part, count(*)
         FROM data
         group by id, part
         having count(*) > 1
       )
select *
from data   d,
     w_cnt   w
where d.id = w.id
  and d.part = w.part
/

        ID T       PART         ID       PART   COUNT(*)
---------- - ---------- ---------- ---------- ----------
       103 B         25        103         25          2
       103 A         25        103         25          2
       101 B         10        101         10          3
       101 B         10        101         10          3
       101 A         10        101         10          3

5 rows selected.

字符串

赞(0）回复(0）举报 2023-08-03

pkbketx92#

这样应该就行了

select *
from data d
right join (
  select id, part
      from data
  group by id, part
    having count(part)>=2
) s on s.id = d.id and s.part = d.part

字符串

赞(0）回复(0）举报 2023-08-03

o3imoua43#

只是为了多样化，因为通常有很多方法来解决一个问题。这是一个使用分析的解决方案。（我并不是说这比其他人表现得更好或更差。为此，请测试/验证和基准;））
但它确实给予了另一种选择。

WITH data AS (
    SELECT 101 id, 'A' type, 10 part FROM dual UNION ALL
    SELECT 101 id, 'B' type, 10 part FROM dual UNION ALL
    SELECT 101 id, 'B' type, 10 part FROM dual UNION ALL
    SELECT 101 id, 'B' type, 20 part FROM dual UNION ALL
    SELECT 101 id, 'C' type, 30 part FROM dual UNION ALL
    SELECT 102 id, 'A' type, 10 part FROM dual UNION ALL
    SELECT 102 id, 'B' type, 25 part FROM dual UNION ALL
    SELECT 103 id, 'A' type, 25 part FROM dual UNION ALL
    SELECT 103 id, 'B' type, 25 part FROM dual
   ),
   sub as (
      Select id, type, part,
             count(*) over (partition by id, part) c
      from data
   )
Select id, type, part
from sub
where c > 1
/

        ID T       PART
---------- - ----------
       101 A         10
       101 B         10
       101 B         10
       103 A         25
       103 B         25

5 rows selected.

字符串

赞(0）回复(0）举报 2023-08-03

我来回答

Oracle SQL：如何根据不同的行值筛选出行

3条答案

相关问题

热门标签

最新问答