先请看一组数据:
id userName email Name1 Name2 birthday addressLine
153 5-62 [email protected] I D
311 5-61 [email protected] I D 可以看到这2组数据明显是相同的userName email Name1 Name2 birthday。需求 找出用户表里相同email和birthday的 用户。
我用的sql是
select a.* from User a
inner join(select b.email,b.birthday from User b
where trim(ifnull(b.email,'')) <>''
group by b.email,b.birthday having count(*)>=2) b
on a.email=b.email
and a.birthday=b.birthday
order by a.email这个语句看起来没有问题,但是实际他丢掉上面的一组 (birthday为空的记录 )
问应该如何修改sql才能包含email相同birthday为空的 一组用户?(最好不要用union)
id userName email Name1 Name2 birthday addressLine
153 5-62 [email protected] I D
311 5-61 [email protected] I D 可以看到这2组数据明显是相同的userName email Name1 Name2 birthday。需求 找出用户表里相同email和birthday的 用户。
我用的sql是
select a.* from User a
inner join(select b.email,b.birthday from User b
where trim(ifnull(b.email,'')) <>''
group by b.email,b.birthday having count(*)>=2) b
on a.email=b.email
and a.birthday=b.birthday
order by a.email这个语句看起来没有问题,但是实际他丢掉上面的一组 (birthday为空的记录 )
问应该如何修改sql才能包含email相同birthday为空的 一组用户?(最好不要用union)
inner join (select b.email,b.birthday from User b
where trim(ifnull(b.email,'')) <>''
group by b.email,b.birthday having count(*)>=2) b
on a.email=b.email
and trim(a.birthday)=trim(b.birthday)
order by a.email
还是一样 不能包含上面列举的数据。
select a.* from User a
inner join
(select b.email,b.birthday from User b
where trim(ifnull(b.email,'')) <>''
group by b.email,b.birthday having count(*)>=2) b
on a.email=b.email
and a.birthday=b.birthday
order by a.email
实际上等于
select a.* from User a
inner join
(select b.email,b.birthday from User b
where trim(ifnull(b.email,'')) <>''
group by b.email,b.birthday having count(*)>=2) b
on a.email=b.email
and a.birthday is not null and b.birthday is not null
and a.birthday=b.birthday
order by a.email
2者查询结果都是一样的 。如果是这样他必须unionselect a.* from User a
inner join
(select b.email,b.birthday from User b
where trim(ifnull(b.email,'')) <>''
group by b.email,b.birthday having count(*)>=2) b
on a.email=b.email
and a.birthday is null and b.birthday is null
order by a.email
inner join
(select b.email,b.birthday from User b
where trim(ifnull(b.email,'')) <>''
group by b.email,b.birthday having count(*)>=2) b
on a.email=b.email
and a.birthday is not null and b.birthday is not null
and a.birthday=b.birthday
order by a.email
union
select a.* from User a
inner join
(select b.email,b.birthday from User b
where trim(ifnull(b.email,'')) <>''
group by b.email,b.birthday having count(*)>=2) b
on a.email=b.email
and a.birthday is null and b.birthday is null
order by a.email 他的结果为230 说明还是不同的哦
这样我直接用你的数据在本机可以做测试。