记录一个sql：mysql表根据自身数据来更新自身

目的是要算同样的地区内，水果的排名并且更新自己ranking列：
于是如下sql：

UPDATE DistrictProducts2 P1 SET ranking = ( SELECT COUNT(P2.price) + 1 FROM DistrictProducts2 P2 WHERE P1.district = P2.district AND P2.price > P1.price );

但是这条语句在mysql上执行会报错：

[Err] 1093 - You can't specify target table 'P1' for update in FROM clause

因为mysql是不支持在子查询中引用更新目标表的数据库，简而言之，就是不允许拿自己的数据来更新自己。
怎么办呢，嵌套一层子查询，就是迷惑mysql，所以要先把各水果在自己地区内的排名先算出来：

SELECT p1.district, p1.price, COUNT(p2.name) + 1 AS rank FROM DistrictProducts2 p1 LEFT JOIN DistrictProducts2 p2 ON p1.district = p2.district AND p2.price > p1.price GROUP BY p1.district, p1.name ORDER BY p1.district, rank;

这个sql就是根据地区和名字分组，然后和自己自连接，算出比自己价格大的个数，这个查询结果是这样的：

其实就是更新后的表，只是现在要从这个结果中取数据去更新自己：

UPDATE DistrictProducts2 pout SET ranking = ( SELECT pin.rank FROM ( SELECT p1.district AS district, p1.name AS name, p1.price , COUNT(p2.name) + 1 AS rank FROM DistrictProducts2 p1 LEFT JOIN DistrictProducts2 p2 ON p1.district = p2.district AND p2.price > p1.price GROUP BY p1.district, p1.name ORDER BY p1.district, rank ) pin WHERE pin.district = pout.district AND pin.name = pout.name );

表更新之后：

这个和oracle的set子句不允许使用开窗函数rank一样，都需要包装一层。