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
uj5u.com熱心網友回復：
price售價重復呢，怎么保留一條uj5u.com熱心網友回復：
不考慮重復情況的話，可以這么寫
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;
uj5u.com熱心網友回復：
UPDATE tttt AS ALEFT 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
uj5u.com熱心網友回復：
UPDATE tttt AS AINNER 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
uj5u.com熱心網友回復：
0， 1 寫反了
uj5u.com熱心網友回復：
謝謝兄弟!最后一個搞定了！非常感謝！
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/18274.html
標籤：MySQL