問題描述

在閱讀Mick《SQL進(jìn)階教程》時(shí)看到這樣一道習(xí)題：
使用UPDATE語句，往下面表格中ranking一列填入恰當(dāng)?shù)男蛱?hào)。排序的要求是在每個(gè)地區(qū)內(nèi)部按照價(jià)格從高到低的順序排列。例如，東北-橘子這一行的ranking列應(yīng)該填入1，東北-蘋果填入2，東北-葡萄填入2，東北-檸檬填入4，關(guān)東-檸檬填入1……

各地區(qū)水果價(jià)格表(DistrictProducts)

錯(cuò)誤的SQL語句

為了排序，自然而然地想到了關(guān)聯(lián)子查詢。把原表跟自身關(guān)聯(lián)，然后數(shù)出來每個(gè)地區(qū)集合中有多少個(gè)價(jià)格高于當(dāng)前記錄價(jià)格。如果有0個(gè)高于當(dāng)前記錄價(jià)格，就說明當(dāng)前記錄是該地區(qū)集合中價(jià)格最高的，ranking里就應(yīng)該填入1;如果有1個(gè)高于當(dāng)前記錄價(jià)格，就說明當(dāng)前記錄的價(jià)格在該地區(qū)集合中排名第2……以此類推?？梢钥吹?，使用COUNT數(shù)出來高于當(dāng)前記錄價(jià)格的價(jià)格數(shù)后，還要再加上1，才能得到當(dāng)前記錄的ranking。
按照上面的思路很容易編寫出以下SQL語句：

UPDATE DistrictProducts AS D1
SET D1.ranking = (SELECT COUNT(D2.price)+1
                  FROM DistrictProducts AS D2
                  WHERE D1.district=D2.district AND D1.price<D2.price);

從邏輯上講沒有問題，在其他的一些數(shù)據(jù)庫中也確實(shí)可以完成對(duì)數(shù)據(jù)表的更新，但是在MySQL中執(zhí)行會(huì)報(bào)錯(cuò)，報(bào)錯(cuò)信息如下：

報(bào)錯(cuò)信息

這是因?yàn)镸ySQL不支持在子查詢中引用更新目標(biāo)表。
我們要更新的就是DistrictProducts這張表，而在子查詢的FROM語句中還從這張表查詢出數(shù)據(jù)，這在MySQL中是不被允許的。之所以有這個(gè)規(guī)定，也是考慮到了數(shù)據(jù)安全。

解決方法

1. 多嵌套版本

既然不允許在FROM子句里引用更新目標(biāo)表，那我們就對(duì)從目標(biāo)表里查詢出來的數(shù)據(jù)再查詢一次，相當(dāng)于生成一個(gè)臨時(shí)表。從外層來看，F(xiàn)ROM子句里引用的是另外一張表（臨時(shí)表），這就不會(huì)違背剛才提到的原則。
最終寫出來的SQL語句如下：

UPDATE DistrictProducts AS D0
SET D0.ranking = (SELECT rank1
                  FROM (SELECT D1.district,D1.name,(SELECT COUNT(D2.price)+1
                                                    FROM DistrictProducts AS D2
                                                    WHERE D1.district=D2.district AND D1.price<D2.price) AS rank1
                                                    FROM DistrictProducts AS D1) AS tt

                  WHERE D0.district=tt.district AND D0.name=tt.name);

上面的語句存在不少嵌套，可以先看里面(SELECT D1.district……) AS tt這部分。這個(gè)tt就是我們給臨時(shí)表取的名字，它有三列，分別是district,name和rank1.其中，rank1就對(duì)應(yīng)著目標(biāo)表的ranking列。知道了tt的結(jié)構(gòu)，我們就可以在頭腦里把這一大串SQL語句簡(jiǎn)化為：

UPDATE DistrictProducts AS D0
SET D0.ranking = (SELECT rank1
                  FROM tt
                  WHERE D0.district=tt.district AND D0.name=tt.name);

tt里就包含著我們要填寫到ranking列的數(shù)據(jù)，我們只要對(duì)目標(biāo)表和tt也來一個(gè)關(guān)聯(lián)子查詢，關(guān)聯(lián)條件是district和name都相同，這樣就能找到相應(yīng)的正確ranking值填入數(shù)據(jù)表。

2. 多表更新減少嵌套版本

如果覺得上面的SQL語句嵌套實(shí)在太多了，還可以把生詞臨時(shí)表tt的語句挪到UPDATE子句中：

UPDATE DistrictProducts AS D0,(SELECT D1.district,D1.name,(SELECT COUNT(D2.price)+1
                                                           FROM DistrictProducts AS D2
                                                           WHERE D1.district=D2.district AND D1.price<D2.price) AS rank1
                               FROM DistrictProducts AS D1) AS tt
SET D0.ranking = tt.rank1
WHERE D0.district=tt.district AND D0.name=tt.name;

這回SQL語句的整體結(jié)構(gòu)變成了多表更新。我們會(huì)目標(biāo)表寫入數(shù)據(jù)，另外一個(gè)臨時(shí)表tt是用來提供數(shù)據(jù)的。
我們還是可以在頭腦里把這些語句簡(jiǎn)化一下：

UPDATE DistrictProducts AS D0,tt
SET D0.ranking = tt.rank1
WHERE D0.district=tt.district AND D0.name=tt.name;

這樣一看，結(jié)構(gòu)一目了然。

如果這篇博文幫到了你，就請(qǐng)給我點(diǎn)個(gè)贊吧~