如何使用SQL关键字NOT作为关键字

Question

我正在尝试转换一个数据表——我希望行变成列，列变成行(就像一个完整的透视图(。我正在使用这个答案的方法来做到这一点。

如果有什么不同的话，我正在使用PHPMyAdmin在Raspberry Pi上运行我自己的SQL服务器。

该表(被截断(如下所示：

Personality|Abby|Aidan|Brandon|Bobby
Agreeabl...|93  |65   |74     |68
Compassion |95  |65   |96     |96
Politeness |81  |9    |21     |12
Conscient..|45  |13   |99     |28

我正试图把它转换成这样：

Coach  |Agreeableness|Compassion|Politeness|Conscientiousness
Abby   |93           |95        |81        |45
Aidan  |65           |96        |9         |13 
Brandon|74           |96        |21        |99
Bobby  |68           |96        |12        |28

我的问题是：

SELECT Coach,
MAX(CASE WHEN Personality = 'Agreeableness' THEN value END) `Agreeableness`,
MAX(CASE WHEN Personality = 'Compassion' THEN value END) `Compassion`,
MAX(CASE WHEN Personality = 'Politeness' THEN value END) `Politeness`,
MAX(CASE WHEN Personality = 'Conscientiousness' THEN value END) `Conscientiousness`,
MAX(CASE WHEN Personality = 'Industriousness' THEN value END) `Industriousness`,
MAX(CASE WHEN Personality = 'Orderliness' THEN value END) `Orderliness`,
MAX(CASE WHEN Personality = 'Extraversion' THEN value END) `Extraversion`,
MAX(CASE WHEN Personality = 'Enthusiasm' THEN value END) `Enthusiasm`,
MAX(CASE WHEN Personality = 'Assertiveness' THEN value END) `Assertiveness`,
MAX(CASE WHEN Personality = 'Neuroticism' THEN value END) `Neuroticism`,
MAX(CASE WHEN Personality = 'Withdrawal' THEN value END) `Withdrawal`,
MAX(CASE WHEN Personality = 'Volatility' THEN value END) `Volatility`,
MAX(CASE WHEN Personality = 'Openness' THEN value END) `Openness`,
MAX(CASE WHEN Personality = 'Intellect' THEN value END) `Intellect`,
MAX(CASE WHEN Personality = 'Openness (Aspect)' THEN value END) `Openness (Aspect)`
FROM 
(
SELECT Personality, Coach,
CASE Coach
WHEN 'Abby' THEN Abby
WHEN 'Aidan' THEN Aidan
WHEN 'Brandon' THEN Brandon
WHEN 'Bobby' THEN Bobby
WHEN 'Carlos' THEN Carlos
WHEN 'Carrie' THEN Carrie
WHEN 'Chassidy' THEN Chassidy
WHEN 'Emily' THEN Emily
WHEN 'Galen' THEN Galen
WHEN 'Gavin' THEN Gavin
**WHEN 'Grant' THEN Grant** #part of interest
WHEN 'Greg' THEN Greg
WHEN 'Jack' THEN Jack
WHEN 'Jenn' THEN Jenn
WHEN 'Noah' THEN Noah
WHEN 'Mae' THEN Mae
WHEN 'Patrick' THEN Patrick
WHEN 'Titus' THEN Titus
END value 
FROM table1 t CROSS JOIN
(
SELECT 'Abby' Coach UNION ALL
SELECT 'Aidan' UNION ALL
SELECT 'Brandon' UNION ALL 
SELECT 'Bobby' UNION ALL
SELECT 'Carlos' UNION ALL
SELECT 'Carrie' UNION ALL
SELECT 'Chassidy' UNION ALL
SELECT 'Emily' UNION ALL
SELECT 'Galen' UNION ALL
SELECT 'Gavin' UNION ALL
SELECT 'Grant' UNION ALL
SELECT 'Greg' UNION ALL
SELECT 'Jack' UNION ALL
SELECT 'Jenn' UNION ALL
SELECT 'Noah' UNION ALL
SELECT 'Mae' UNION ALL
SELECT 'Patrick' UNION ALL
SELECT 'Titus' UNION ALL
) c    
) q 
GROUP BY Coach
ORDER BY FIELD(Coach, 'Abby', 'Aidan', 'Brandon', 'Bobby', 'Carlos', 'Carrie', 'Chassidy', 'Emily', 'Galen', 'Gavin', 'Grant', 'Greg', 'Jack', 'Jenn', 'Noah', 'Mae', 'Patrick', 'Titus');

在代码的一部分中，我需要写WHEN‘Grant’THEN Grant，但第二个未引用的Grant变成了一个关键字，扰乱了我的代码。当我把Grant放在单引号中时，它会让我在写"(c"的地方出现语法错误，但我怀疑我的小欺骗可能与此有关。我该怎么办？

Answer 1

你似乎在暗示Grant是一个列名——这是一个糟糕的选择，但你坚持了下来。转义名称的标准方法是使用双引号：

WHEN 'Grant' THEN "Grant"

一些数据库使用反勾号或方括号：

WHEN 'Grant' THEN `Grant`
WHEN 'Grant' THEN [Grant]