sql - oracle查询根据条件删除重复行

Question

我有如下表的数据。

+------+----------+----------+
| type |  date1   |  date2   |
+------+----------+----------+
| IT1  | 05/01/15 | 08/01/15 |
| IT1  | 05/01/15 |          |
| IT1  | 04/01/15 |          |
| IT1  |          | 03/02/15 |
| IT1  | 06/01/15 | 03/02/15 |
| IT1  |          | 04/02/15 |
| IT2  | 05/01/15 |          |
| IT2  | 05/01/15 | 04/01/15 |
| IT2  | 03/01/15 |          |
| IT2  |          | 09/01/15 |
+------+----------+----------+

我需要去除重复的行，结果如下

+------+----------+----------+
| type |  date1   |  date2   |
+------+----------+----------+
| IT1  | 05/01/15 | 08/01/15 |
| IT1  | 04/01/15 |          |
| IT1  | 06/01/15 | 03/02/15 |
| IT1  |          | 04/02/15 |
| IT2  | 05/01/15 | 04/01/15 |
| IT2  | 03/01/15 |          |
| IT2  |          | 09/01/15 |
+------+----------+----------+    

对于特定类型，

1. 如果 date1 存在相应的 date2，则选择该行并删除其他 date1
2. 如果对应的date2不存在则保留

同样，

1. 如果 date2 存在相应的 date1，则选择该行并删除其他 date2
2. 如果对应的date1不存在则保留

是否可以为此编写一个 oracle 查询？谁能帮我做这件事？

Answer 1

试试这个查询:

select *
from table1 t1
WHERE
  "date2" IS NOT NULL
  AND
  "date1" IS NOT NULL
  OR
  "date2" IS NULL 
  AND NOT EXISTS (
    SELECT 1 FROM table1 t2
    WHERE t1."type" = t2."type"
      AND t1."date1" = t2."date1"
      AND t2."date2" IS NOT NULL
   )
   OR
  "date1" IS NULL 
  AND NOT EXISTS(
    SELECT 1 FROM table1 t2
    WHERE t1."type" = t2."type"
      AND t1."date2" = t2."date2"
      AND t2."date1" IS NOT NULL
   )  

演示:http://sqlfiddle.com/#!4/12b8b/8