最近在看《SQL進階教程》，在1-2 自連接用法 章節的習題中遇到一個問題。具體如下：
練習題1-2-3，有如下表格：
/* 練習題1-2-3：更新位次 */
CREATE TABLE DistrictProducts2
(district VARCHAR(16) NOT NULL,
name VARCHAR(16) NOT NULL,
price INTEGER NOT NULL,
ranking INTEGER,
PRIMARY KEY(district, name));
INSERT INTO DistrictProducts2 VALUES('東北', '橘子', 100, NULL);
INSERT INTO DistrictProducts2 VALUES('東北', '蘋果', 50 , NULL);
INSERT INTO DistrictProducts2 VALUES('東北', '葡萄', 50 , NULL);
INSERT INTO DistrictProducts2 VALUES('東北', '檸檬', 30 , NULL);
INSERT INTO DistrictProducts2 VALUES('關東', '檸檬', 100, NULL);
INSERT INTO DistrictProducts2 VALUES('關東', '菠蘿', 100, NULL);
INSERT INTO DistrictProducts2 VALUES('關東', '蘋果', 100, NULL);
INSERT INTO DistrictProducts2 VALUES('關東', '葡萄', 70 , NULL);
INSERT INTO DistrictProducts2 VALUES('關西', '檸檬', 70 , NULL);
INSERT INTO DistrictProducts2 VALUES('關西', '西瓜', 30 , NULL);
INSERT INTO DistrictProducts2 VALUES('關西', '蘋果', 20 , NULL);
題目要求向Ranking列插入分組后的排名。
我寫的陳述句如下：
UPDATE districtproducts2
SET ranking =
SELECT count(d2.`name`) + 1 as rank1
from districtproducts2 as d1 left JOIN districtproducts2 as d2
on d1.district = d2.district and d1.price < d2.price
GROUP BY d1.district, d1.name
ORDER BY d1.district, rank1
結果報錯了：
> 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT count(d2.`name`) + 1 as rank1
from districtproducts2 as d1 left JOIN dis' at line 3
請問各位大佬，為什么會出現這種情況，該如何修改陳述句呢？
uj5u.com熱心網友回復：
select陳述句加個括號uj5u.com熱心網友回復：
謝謝大佬回復。
UPDATE districtproducts2
SET ranking =
(SELECT count(d2.`name`) + 1 as rank1
from districtproducts2 as d1 left JOIN districtproducts2 as d2
on d1.district = d2.district and d1.price < d2.price
GROUP BY d1.district, d1.name
ORDER BY d1.district, rank1)
是這樣加吧。
但是報這樣的錯了
You can't specify target table 'districtproducts2' for update in FROM clause
我網上查了一下這個錯誤，說是需要再SELECT一遍：
UPDATE districtproducts2
SET ranking =
(
SELECT t.rank1 FROM
(SELECT count(d2.`name`) + 1 as rank1
from districtproducts2 as d1 left JOIN districtproducts2 as d2
on d1.district = d2.district and d1.price < d2.price
GROUP BY d1.district, d1.name
ORDER BY d1.district, rank1) as t
)
這樣又有新的錯誤：
1242 - Subquery returns more than 1 row
針對這種錯誤，有人說家limit 1就行，但這樣豈不是得不到原來想要的結果了嗎？
uj5u.com熱心網友回復：
謝謝大佬回復。我沒參考好。回復的在上一樓。
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/186545.html
標籤：MySQL
上一篇：sql陳述句報錯，哪里錯了呢請教
下一篇：Mysql日常運維命令分享