我有张表 userinfo
username mobile
张三13011731122
李四13011731122
张三13011731123
张三13011731135
王五13011731123
我的数据库是Oracle
现在我想统计 用户重复8次以及8次以上的,并且手机号码不相同的数据,请问这个如何写Sql 语句啊
我现在只能想到
select a.username,count(1) from userinfoa
where a.rowid>(select min(b.rowid) from userinfo b where b.username=a.username)
group by a.username having count(1)>7
这样这个手机号码不同条件,就没有反映出来.
select a.username,count(a.*)
from userinfo a
where a.username in
(select username from userinfo group by username having count(*)>7)
group by a.username
最初由 lastwinner 发布
[B]select username ,count(*) from (select distinct username,mobile from userinfo )
group by username
having count(*) >7 [/B]
楼主考虑一下这个