我的目标是取出重复生日字段值的记录
sql如下
select distinct a.id,a.name,a.birthdate
from a t1, a t2
where t1.birthdate=t2.birthdate
and t1.id != t2.id
order by a.birthdate但是发现很多的字段值为空的记录被选出,如何去掉
后来我加了is not null 但是发现还是很多的空记录被选出,sql如下
select distinct a.id,a.name,a.birthdate
from a t1, a t2
where
t1.birthdate is not null and t2.birthdate is not null and
t1.birthdate=t2.birthdate
and t1.id != t2.id
order by a.birthdate
请问如何改写 sql 让很多的空记录如何去掉?
sql如下
select distinct a.id,a.name,a.birthdate
from a t1, a t2
where t1.birthdate=t2.birthdate
and t1.id != t2.id
order by a.birthdate但是发现很多的字段值为空的记录被选出,如何去掉
后来我加了is not null 但是发现还是很多的空记录被选出,sql如下
select distinct a.id,a.name,a.birthdate
from a t1, a t2
where
t1.birthdate is not null and t2.birthdate is not null and
t1.birthdate=t2.birthdate
and t1.id != t2.id
order by a.birthdate
请问如何改写 sql 让很多的空记录如何去掉?
select * from tb_name a where exists (select 1 from tb_name b where b.birthdate=a.birthdate and b.id <>a.id) and id=(select max(id) from tb_name c where c.birthdate=a.birthdate) ordr by a.birthdate
a.id=b.id
??---
exists (select 1 from tb_name b where b.birthdate=a.birthdate and b.id <>a.id) ordr by a.birthdate
我必须另外加1个表 如何改写这个sql
我已经加了限制条件
但是结果却是还是有空的字段值 , 很奇特??
问题就在这儿, NULL <> '' <> ' 'select distinct a.id,a.name,a.birthdate
from a t1, a t2
where t1.birthdate is not null
and t2.birthdate is not null
and trim(t1.birthdate) != ''
and trim(t2.birthdate) != ''
and t1.birthdate=t2.birthdate
and t1.id != t2.id
order by a.birthdate
当您的问题得到解答后请及时结贴.
http://topic.csdn.net/u/20090501/15/7548d251-aec2-4975-a9bf-ca09a5551ba5.html
from a t1, a t2
where trim(ifnull(t1.birthdate,''))<>''
and trim(ifnull(t2.birthdate,''))<>''
and t1.birthdate=t2.birthdate
and t1.id != t2.id
order by a.birthdate