问题描述

MySQL查询求帮助快绝望了

所有的查询都在USER表，我分解开了条件，第一个SELECT * FROM USER u WHERE u.channelId='301040';
根据查出来userDeviceid的值查，第二条件
想得到userDeviced字段相同的值大于2条和2条以上所对应的用户

想最终得到，这种数据，每条用户的名字Code不一样,但是userDeviceId是一样的

解决方案

 select * from user  where userDeviceid in(
SELECT userDeviceid FROM user where channelId='301040' GROUP BY userDeviceid having count(*)>=2
)

解决方案二：

SELECT channelId FROM USER u
group by u.channelId
having count(*) > 1

解决方案三：

= =
不知道理解的对不对
先找出userDeviced数量大于2条的，然后join user，得到对应的数据

 select a.* from
(select * from user )a join
(select  userDeviced,count(1) from user group by userDeviced having count(1)>=2)b on a.userDeviced=b.userDeviced

解决方案四：

 select distinct * from user where userDeviceid IN(
select * from user group by  channelId having count(*)>=2)

解决方案五：

select * from USER  where userDeviceid  in (select userDeviceid  from USER where USER.channelId='301040') group by userDeviceid HAVING count(*) >=2;

解决方案六：

SELECT channelId FROM USER u group by u.channelId having count(*) > 1