我自己编写的SQL片段显然与以下URL中指定的语法一致:
MySQL WITH子句简介
WITH(通用表表达式(
你能发现给定的SQL代码段出了什么问题吗?
顺便说一句,该解决方案已被尝试应用于从黑客银行-竞赛排行榜中摘录的问题
WITH
MAX_POINT
AS
(
SELECT HACKER_ID, CHALLENGE_ID, MAX(SCORE)
FROM SUBMISSIONS
GROUP BY HACKER_ID, CHALLENGE_ID
),
HACKER_TOTAL_POINTS
AS
(
SELECT HACKER_ID, SUM(SCORE) AS TOTAL_POINT
FROM MAX_POINT
GROUP BY HACKER_ID
HAVING SUM(SCORE) > 0
)
SELECT T.HACKER_ID, H.NAME,T.TOTAL_POINT
FROM HACKER_TOTAL_POINTS T
INNER JOIN HACKERS H
ON H.HACKER_ID = T.HACKER_ID
ORDER BY T.TOTAL_POINT DESC, T.HACKER_ID;
ERROR 1064 (42000) at line 5: 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 'MAX_POINT
AS
(
SELECT HACKER_ID, CHALLENGE_ID, MAX(SCORE)
FROM SUBMISSI' at line 2
-
乍一看,代码中的WITH子句看起来是正确的。然而,我没有详细检查,所以你不应该在这个意义上依赖我的答案。
-
为了回答你问题的第二部分,即为什么你会从HackerBank得到这个错误,我的理解是:Hackerbank的MySQL版本不支持CTE(即WITH子句(。如果你运行
SELECT Version();
它打印出5.7.27。根据这篇文章,MySQL 5.7不支持CTE
如果要创建CTE,则要创建一个具有列名的psuedo表。
在您的第一个CTE中,您没有给MAX(SCORE(一个列名(或别名(。第二次CTE很好。
更改:-
WITH
MAX_POINT
AS
(
SELECT HACKER_ID, CHALLENGE_ID, MAX(SCORE)
FROM SUBMISSIONS
GROUP BY HACKER_ID, CHALLENGE_ID
)
至
WITH
MAX_POINT
AS
(
SELECT HACKER_ID, CHALLENGE_ID, MAX(SCORE) AS HIGHSCORE
FROM SUBMISSIONS
GROUP BY HACKER_ID, CHALLENGE_ID
)