求大神一句 MySQL 语句搞定

能力有限写不出来

支持
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));

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;

我做了一个这个，感觉还有点不太对！

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}`;

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)

@1234567890 嗯，收到谢谢！~

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