2 gtrnfs2000 gtrNFS2000 于 2015.06.05 17:25 提问

MySQL查询求帮助快绝望了

所有的查询都在USER表,我分解开了条件,第一个SELECT * FROM USER u WHERE u.channelId='301040';图片说明
根据查出来userDeviceid的值查,第二条件图片说明
想得到userDeviced字段相同的值大于2条和2条以上所对应的用户
图片说明
想最终得到,这种数据,每条用户的名字Code不一样,但是userDeviceId是一样的

6个回答

danielinbiti
danielinbiti   Ds   Rxr 2015.06.05 17:38
已采纳
 select * from user  where userDeviceid in(
SELECT userDeviceid FROM user where channelId='301040' GROUP BY userDeviceid having count(*)>=2
)
danielinbiti
danielinbiti 回复gtrNFS2000: update不就行了嘛 update user set channelId='新值' where userDeviceid in( SELECT userDeviceid FROM (select distinct code,userDeviceid from user) u where channelId='301040' GROUP BY userDeviceid having count(*)>=2 )
2 年多之前 回复
gtrNFS2000
gtrNFS2000 回复danielinbiti: danielinbiti大神,查出这些可以批量修改channelId的值吗
2 年多之前 回复
gtrNFS2000
gtrNFS2000 danielinbiti大神,查出这些可以批量修改channelId的值吗
2 年多之前 回复
danielinbiti
danielinbiti 回复gtrNFS2000: select * from user where userDeviceid in( SELECT userDeviceid FROM (select distinct code,userDeviceid from user) u where channelId='301040' GROUP BY userDeviceid having count(*)>=2 )
2 年多之前 回复
gtrNFS2000
gtrNFS2000 想最终得到,这种数据,每条用户的名字Code不一样,但是userDeviceId是一样的图3
2 年多之前 回复
edouardzyc
edouardzyc   2015.06.05 17:37

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

qq_17246605
qq_17246605   2015.06.05 17:42

= =
不知道理解的对不对
先找出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
Bruce_why
Bruce_why   2015.06.05 17:45
 select distinct * from user where userDeviceid IN(
select * from user group by  channelId having count(*)>=2)
tongyi55555
tongyi55555   2015.06.05 18:01
select * from USER  where userDeviceid  in (select userDeviceid  from USER where USER.channelId='301040') group by userDeviceid HAVING count(*) >=2;

datou431
datou431   Rxr 2015.06.10 15:25

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

Csdn user default icon
上传中...
上传图片
插入图片