mysql - 使用别名 (AS) 的 MySQL 查询中的 SQL 错误

Question

我在 MySQL Workbench 中执行此 SQL 语句时遇到问题。有人能为我指出问题吗？

SELECT concat(LocationName,' - ', StationName) AS LocationStation, EmpName AS '0000-0100', EmpName AS '0100-0200', EmpName AS '0200-0300', EmpName AS '0300-0400', EmpName AS '0400-0500', EmpName AS '0500-0600', EmpName AS '0600-0700', EmpName AS '0700-0800', EmpName AS '0800-0900', EmpName AS '0900-1000', EmpName AS '1000-1100', EmpName AS '1100-1200', FROM satsschema.employeeslot
WHERE LocationName = 'T2 PML'
and AllocationDate = '10-Aug' 
and (EmpTime >= '00:00:00' and EmpTime <= '12:00:00')

上面的语句提示了我这个错误，“您的 SQL 语法有错误，请检查与您的 MySQL 服务器版本相对应的手册，以获取正确的语法以使用整洁的 'FROM satsschema.employeeslot WHERE LocationName = 'T2 PML'和 AllocationDate = ' 在第 1 行。"

其次，除了输入所有内容之外，还有其他方法可以缩短这一行吗？

EmpName AS '0000-0100', EmpName AS '0100-0200', EmpName AS '0200-0300', EmpName AS '0300-0400', EmpName AS '0400-0500', EmpName AS '0500-0600', EmpName AS '0600-0700', EmpName AS '0700-0800', EmpName AS '0800-0900', EmpName AS '0900-1000', EmpName AS '1000-1100', EmpName AS '1100-1200', FROM satsschema.employeeslot

我们将非常感谢您的帮助。

Answer 1

删除FROM之前的逗号。它会导致错误。

SELECT concat(LocationName,' - ', StationName) AS LocationStation, EmpName AS '0000-0100', EmpName AS '0100-0200', EmpName AS '0200-0300', EmpName AS '0300-0400', EmpName AS '0400-0500', EmpName AS '0500-0600', EmpName AS '0600-0700', EmpName AS '0700-0800', EmpName AS '0800-0900', EmpName AS '0900-1000', EmpName AS '1000-1100', EmpName AS '1100-1200' FROM satsschema.employeeslot
WHERE LocationName = 'T2 PML'
and AllocationDate = '10-Aug' 
and (EmpTime >= '00:00:00' and EmpTime <= '12:00:00')

请注意，错误消息准确说明了错误所在。

在 MySQL 中，AS 关键字对于列别名是可选的，因此您可以编写 EmpName '0000-0100' 来代替 EmpName AS '0000- 0100'。我认为没有一种简短的解决方案可以为一列提供多个别名，因为这实际上并不是经常需要的。