SQL答疑一例

问题来自论坛网友提问，如下：

有网友给出回答如下：
UPDATE c1 AS a
INNER JOIN 
(SELECT haoma,MAX(price) AS bprice FROM c1 GROUP BY haoma) AS b
ON a.`haoma`=b.haoma
SET state= CASE WHEN a.`price` = B.bprice THEN 1 ELSE 0 END

这个SQL没问题。

本着自找麻烦的精神，捉摸着如何写出不一样的答案。

首先，如果确定state原值都是1，可以用如下：

UPDATE c1 AS a
INNER JOIN 
(SELECT haoma,MAX(price) AS bprice FROM c1 GROUP BY haoma) AS b
ON a.`haoma`=b.haoma
SET state=0 WHERE b.bprice>a.price

这个答案存在缺陷，就是如果同一haoma的state的最大值有0值时不可用。

于是乎，改成了如下：

UPDATE c1 AS d 
INNER JOIN
(SELECT a.id,IFNULL(b.bst,0) AS bst,a.price,b.bprice FROM c1 AS a
LEFT JOIN 
(SELECT haoma,MAX(price) AS bprice,1 AS bst FROM c1 GROUP BY haoma) AS b
ON a.haoma=b.haoma AND a.`price`=b.bprice) c
ON d.id=c.id
SET d.state=c.bst