有NULL值的情况下如何去除重复数据？？？

[Xiao_Ma123456789]

create table sales(
empid varchar(20), --雇员ID
depid number, --部门ID
area varchar(20), --区域
salenum number)--销售额

insert into sales(EMPID,DEPID,AREA,SALENUM) values('ZHANGSAN','1','ASIA',500);
insert into sales(EMPID,DEPID,AREA,SALENUM) values('ZHANGSAN','2','ASIA',500);
insert into sales(EMPID,DEPID,AREA,SALENUM) values('ZHANGSAN','1','ASIA',500);
insert into sales(EMPID,DEPID,AREA,SALENUM) values('ZHANGSAN','1','ASIA',500);
insert into sales(EMPID,DEPID,AREA,SALENUM) values('ZHANGSAN','1','EUR',800);
insert into sales(EMPID,DEPID,AREA,SALENUM) values('ZHANGSAN',null,'EUR',null);
insert into sales(EMPID,DEPID,AREA,SALENUM) values('ZHANGSAN',null,'EUR',null);
insert into sales(EMPID,DEPID,AREA,SALENUM) values('ZHANGSAN',null,'EUR',800);
insert into sales(EMPID,DEPID,AREA,SALENUM) values('ZHANGSAN',null,'EUR',800);
insert into sales(EMPID,DEPID,AREA,SALENUM) values('ZHANGSAN','1','EUR',null);
insert into sales(EMPID,DEPID,AREA,SALENUM) values('ZHANGSAN','1','EUR',null);
insert into sales(EMPID,DEPID,AREA,SALENUM) values('LISI','1','EUR',800);
insert into sales(EMPID,DEPID,AREA,SALENUM) values('LISI','1','EUR',800);
insert into sales(EMPID,DEPID,AREA,SALENUM) values('LISI','2','EUR',1000);
insert into sales(EMPID,DEPID,AREA,SALENUM) values('LISI','2','EUR',1000);
insert into sales(EMPID,DEPID,AREA,SALENUM) values('LISI','2','EUR',800);
insert into sales(EMPID,DEPID,AREA,SALENUM) values('LISI','2','EUR',800);

delete from sales a where (a.DEPID,a.SALENUM) in(select depid,salenum from sales group by depid,salenum having count(*)>1)
and rowid not in(select min(rowid) from sales group by depid,salenum having count(*)>1)
用上面这条语句过滤的话，有NULL值的记录都还在，求正确的写法，谢谢！

[xiaoheixiaobai]

delete from sales a where (nvl(a.DEPID,-1), nvl(a.SALENUM,-1)) in ( select nvl(depid,-1), nvl(salenum,-1) from sales group by depid, salenum having count(*) > 1 ) and rowid not in (select min(rowid) from sales group by depid, salenum having count(*) > 1)

[华而不实]

关键在于null不能用in 操作，结果返回false，所以删除不掉

[Xiao_Ma123456789]

引用 2 楼 yangxuan992 的回复:

关键在于null不能用in 操作，结果返回false，所以删除不掉

大侠给个正确的写法啊！

[Xiao_Ma123456789]

我去，都没有大侠帮忙写个语句么？

[卖水果的net]

-- 看看这个能达到你的目的吗？

delete sales where rowid not in (
select max(rowid) 
from sales group by EMPID,DEPID,AREA,SALENUM
)

[Xiao_Ma123456789]

引用 5 楼 wmxcn2000 的回复:

-- 看看这个能达到你的目的吗？

delete sales where rowid not in (
select max(rowid) 
from sales group by EMPID,DEPID,AREA,SALENUM
)

大侠很明显不能啊，我你这里取的最大值，我上面写的最小值。。。

[卖水果的net]

你不是要去重复值吗？ 把你要的结果，也列一下吧，这样才知道你要保留哪些数据。

[Xiao_Ma123456789]

引用 7 楼 wmxcn2000 的回复:

你不是要去重复值吗？ 把你要的结果，也列一下吧，这样才知道你要保留哪些数据。

要的结果和distinct结果一样，里面包含两个字段都为null值以及depid为null和salenum为null的数据 如果用group by那种方法删除重复数据，所有为null值的数据都还在的。 select distinct depid,salenum from sales

[卖水果的net]

那我给你发的语句，你跑了吗 ？ PS：这里的 MAX 使用，你换成 MIN ，结果是同样的。

[Xiao_Ma123456789]

引用 9 楼 wmxcn2000 的回复:

那我给你发的语句，你跑了吗 ？ PS：这里的 MAX 使用，你换成 MIN ，结果是同样的。

额，大侠你看我发的第一个帖子，就下面的语句跟你的写法一样的啊，而且我只是用这个两个字段做过滤，不要其他的字段的 delete from sales a where (a.DEPID,a.SALENUM) in(select depid,salenum from sales group by depid,salenum having count(*)>1) and rowid not in(select min(rowid) from sales group by depid,salenum having count(*)>1)

[卖水果的net]

EMPID,DEPID 一样的，只留下一条 salnum 最大的那行 ？

[Xiao_Ma123456789]

引用 11 楼 wmxcn2000 的回复:

EMPID,DEPID 一样的，只留下一条 salnum 最大的那行 ？

是啊，大侠最终删除的结果和distinct的结果一样就是对的

[卖水果的net]

-- 少写两列就可以了，你简单的改一下。
delete sales where rowid not in (
select max(rowid) 
from sales group by EMPID,DEPID
);

-- PS ： 你好像比我还懒 。。。。

[Xiao_Ma123456789]

引用 13 楼 wmxcn2000 的回复:

-- 少写两列就可以了，你简单的改一下。
delete sales where rowid not in (
select max(rowid) 
from sales group by EMPID,DEPID
);

-- PS ： 你好像比我还懒 。。。。

大侠，我没搞清楚到底区别在哪里了？大侠给详解一下呗！ delete sales where rowid not in ( select max(rowid) from sales group by EMPID,DEPID ); delete from sales a where (a.DEPID,a.SALENUM) in(select depid,salenum from sales group by depid,salenum having count(*)>1) and rowid not in(select min(rowid) from sales group by depid,salenum having count(*)>1)

[yps19940203]

引用 14 楼 Xiao_Ma123456789 的回复:

[quote=引用 13 楼 wmxcn2000 的回复:]

-- 少写两列就可以了，你简单的改一下。
delete sales where rowid not in (
select max(rowid) 
from sales group by EMPID,DEPID
);

-- PS ： 你好像比我还懒 。。。。

大侠，我没搞清楚到底区别在哪里了？大侠给详解一下呗！
delete sales where rowid not in (
select max(rowid)
from sales group by EMPID,DEPID
);

delete from sales a where (a.DEPID,a.SALENUM) in(select depid,salenum from sales group by depid,salenum having count(*)>1)
and rowid not in(select min(rowid) from sales group by depid,salenum having count(*)>1)[/quote]
区别在于rowid 记录了空值 的地址 然后就能用not in 删除重复的了

[qq_33473158]

11

[liuzhe_521]

delete from sales a where exists (select null from (select max(rowid) rid, empid, nvl(depid, 0) depid from sales group by empid, nvl(depid, 0) having count(1) > 1) where empid = a.empid and depid = nvl(a.depid, 0) and a.rowid <> rid)

[baidu_33474847]

delete from sales t1
where rowid not in (select rowid
from (select empid,
depid,
area,
salenum,
row_number() over(partition by t.empid, t.depid, t.area, t.salenum order by t.empid) rn
from sales t)
where rn = '1');

[鸣鸣Amadues]

做法就有问题。 数据库在设计字段的时候，要保证所有字段都是not null。 然后插入数据的时候，如果某个字段没有值，那么初始值就是空字符串，也就是""。 这样做不会引起歧义，有利于开发简便。