求大神一句 MySQL 语句搞定

mysql语句实现:

检测重复号码,售价低的state状态设置为0。
只保留售价最高的那一条state=1

haoma price state

11111111 100 1
22222222 200 1
33333333 300 1
44444444 400 1

11111111 500 1
22222222 600 1
33333333 700 1
44444444 800 1

11111111 900 1
22222222 1000 1
33333333 1100 1
44444444 1200 1

zhangatle
最佳答案
update haoma set state=(case when price in (select price from (select max(price) as price from haoma GROUP BY haoma) temp_table)  then 1 else 0 end)
3个月前 评论
讨论数量: 6

能力有限写不出来

支持
update haomac set state = 0 where id not in (1,3,4)
支持
select d.id from haomac as d where d.id not in (Select a.id From haomac a Where a.price In (Select max(b.price) From haomac b Group By b.haoma));

不支持
update haomac as d set d.state = 0 where d.id not in (Select a.id From haomac a Where a.price In (Select max(b.price) From haomac b Group By b.haoma));

4个月前 评论

UPDATE ghw_qqhaoma2 SET state=0 where id in ( select id from ( select * from ghw_qqhaoma2 where haoma in ( select haoma from ghw_qqhaoma2 group by haoma having count(haoma) >1
) and ptprice in (select min(ptprice) from ghw_qqhaoma2 group by haoma having count(haoma)>1) ) a ) and state=1;

我做了一个这个,感觉还有点不太对!

4个月前 评论

mysql不支持数组类型的变量,所以本人建议用php配合mysql来实现一下,下面是基本思路

-- 第一步,分组查出大于1每个号码、数量
select count(1) co,haoma from `table` group by haoma having co>1;
-- 第二步,遍历一遍该数组,用每项里面haoma字段查询按价格排序的最大值,作为下一步的第二个where条件;
select price from `table` where haoma=`{$haoma}` order by price desc limit 1;
-- 第三部,同在循环当中,拼接该update语句即可;
update `table` set state=0 where haoma=`{$haoma}` and price<>`{$price}`;
4个月前 评论
zhangatle
update haoma set state=(case when price in (select price from (select max(price) as price from haoma GROUP BY haoma) temp_table)  then 1 else 0 end)
3个月前 评论

@zhangatle 测试了一下特别好使!十分感谢! :+1:

3个月前 评论

请勿发布不友善或者负能量的内容。与人为善,比聪明更重要!