求大神一句mysql搞定

[weixin_40243990]

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

[三年丿]

price售价重复呢，怎么保留一条

[三年丿]

不考虑重复情况的话，可以这么写

update tb_test A
JOIN (SELECT * FROM(SELECT haoma,MAX(price) AS price FROM tb_test GROUP BY haoma)AS S) AS B
ON A.haoma=B.haoma
SET A.State=CASE WHEN A.price=B.price THEN 1 ELSE 0 END;

[带我飞的云]

UPDATE tttt AS A
LEFT JOIN (SELECT haoma,MAX(price) AS price FROM tttt GROUP BY haoma) AS B
ON A.haoma=B.haoma
SET a.`state` = CASE WHEN a.`price` = B.price THEN 1 ELSE 0 END

[带我飞的云]

UPDATE tttt AS A
INNER JOIN (SELECT haoma,MAX(price) AS price FROM tttt GROUP BY haoma) AS B
ON A.haoma=B.haoma
SET a.`state` = CASE WHEN a.`price` = B.price THEN 0 ELSE 1 END

[带我飞的云]

引用 4 楼 阿代公主 的回复:

UPDATE tttt AS A
INNER JOIN (SELECT haoma,MAX(price) AS price FROM tttt GROUP BY haoma) AS B
ON A.haoma=B.haoma
SET a.`state` = CASE WHEN a.`price` = B.price THEN 0 ELSE 1 END

0， 1 写反了

[weixin_40243990]

谢谢兄弟!最后一个搞定了！非常感谢！