Mysql statement:

Detection of repeat number, price low state of the state is set to 0,
Keep only the highest price that a 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

CodePudding user response:

Price for repeat, how to keep a

CodePudding user response:

Regardless of the repeated case, so to write

 
The update tb_test A 
The JOIN (SELECT * FROM (SELECT haoma, MAX (price) AS price FROM tb_test GROUP BY haoma) AS S) AS B 
ON A.h aoma=B.h aoma 
The SET A.S Tate=CASE WHEN Amy polumbo rice=p. rice THEN 1 ELSE 0 END; 

CodePudding user response:

The UPDATE TTTT AS A
LEFT the JOIN (SELECT haoma, MAX (price) AS price FROM TTTT GROUP BY haoma) AS B
ON A.h aoma=B.h aoma
SET a. ` state `=CASE WHEN a. ` price `=p. rice THEN 1 ELSE 0 END

CodePudding user response:

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

CodePudding user response:

reference 4 floor generation princess reply:

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

0, 1 write inside out

CodePudding user response:

Thanks brother! The last one. Thank you very much!