sql - 根据条件删除重复项

Question

我的表值:

COLUMN1  COLUMN2 COLUMN3
WF1      Email   1640
WF1      Email   1641
WF1      Email   N/A
WF3      Email   N/A

预期结果:

COLUMN1  COLUMN2 COLUMN3
WF1      Email   1640
WF3      Email   N/A

我需要检索 column2 = 'Email' 的所有记录，如果 column1 包含重复值，我必须选择 column3 <> 'N/A' 的记录。

我阅读了有关分区依据的教程，但仍然不确定如何获得结果。

感谢任何帮助。

CREATE TABLE TABLE1 
    (

     COLUMN1 varchar2(20), 
     COLUMN2 varchar2(20), 
     COLUMN3 varchar2(20) 
    );

INSERT INTO TABLE1
(COLUMN1, COLUMN2, COLUMN3)
VALUES
('WF1', 'Email', '1640');

INSERT INTO TABLE1
(COLUMN1, COLUMN2, COLUMN3)
VALUES
('WF1', 'Email', '1641');

INSERT INTO TABLE1
(COLUMN1, COLUMN2, COLUMN3)
VALUES
('WF1', 'Email', 'N/A');

INSERT INTO TABLE1
(COLUMN1, COLUMN2, COLUMN3)
VALUES
('WF3', 'Email', 'N/A');

Answer 1

尝试这样的事情:

SELECT column1, column2, column3
from(
SELECT column1, column2, column3,
row_number() over (partition BY column1, column2 ORDER BY CASE WHEN column3 = 'N/A' THEN 999999999 ELSE to_number(column3) END ) rn
FROM table1)
WHERE rn = 1

Here is a sqlfiddle