gtrNFS2000 于 2015.06.05 17:25 提问
- MySQL查询求帮助快绝望了
-
所有的查询都在USER表，我分解开了条件，第一个SELECT * FROM USER u WHERE u.
channelId='301040';
根据查出来userDeviceid的值查，第二条件
想得到userDeviced字段相同的值大于2条和2条以上所对应的用户
想最终得到，这种数据，每条用户的名字Code不一样,但是userDeviceId是一样的
-
-
danielinbiti
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 回复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 )
- 接近 3 年之前 回复
-
- gtrNFS2000 回复danielinbiti: danielinbiti大神，查出这些可以批量修改channelId的值吗
- 接近 3 年之前 回复
-
- gtrNFS2000 danielinbiti大神，查出这些可以批量修改channelId的值吗
- 接近 3 年之前 回复
-
- 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 )
- 接近 3 年之前 回复
-
- gtrNFS2000 想最终得到，这种数据，每条用户的名字Code不一样,但是userDeviceId是一样的图3
- 接近 3 年之前 回复
-
- edouardzyc 2015.06.05 17:37
SELECT channelId FROM USER u
group by u.channelId
having count(*) > 1
-
- 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 2015.06.05 17:45
select distinct * from user where userDeviceid IN(
select * from user group by channelId having count(*)>=2)
-
- 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
2015.06.10 15:25
SELECT channelId FROM USER u group by u.channelId having count(*) > 1
准确详细的回答，更有利于被提问者采纳，从而获得C币。复制、灌水、广告等回答会被删除，是时候展现真正的技术了！