mysql - 连接两个子查询引发错误

以下查询出了什么问题？

SELECT * FROM
  (SELECT DATE_FORMAT(Start, '%d-%M-%Y') AS Date1,
   SUM(TIMESTAMPDIFF(SECOND, Start, End)) / 3600 AS Hours
   FROM timeslot WHERE UserID = 1
   GROUP BY Date1) AS A

LEFT OUTER JOIN

  (SELECT DATE_FORMAT(TakenAt, '%d-%M-%Y') AS Date2
   FROM snapshot WHERE Deleted = 1
   GROUP BY Date2) AS B

ON A.Date1 = B.Date2

它没有运行。 VS稍微更改了查询（分别将GROUP BY Date1和GROUP BY Date2分别更改为GROUP BY A.Date1和GROUP BY B.Date2），并告诉我有关group语句中未知列A.Date1的信息。

最佳答案

我相信这是由于MySQL reserved words 导致的，请尝试使用反勾`：

SELECT * FROM
  (SELECT DATE_FORMAT(`Start`, '%d-%M-%Y') AS Date1,
   SUM(TIMESTAMPDIFF(SECOND, `Start`, `End`)) / 3600 AS Hours
   FROM timeslot WHERE UserID = 1
   GROUP BY DATE_FORMAT(`Start`, '%d-%M-%Y')) A
LEFT OUTER JOIN
  (SELECT DATE_FORMAT(TakenAt, '%d-%M-%Y') AS Date2
   FROM snapshot WHERE Deleted = 1
   GROUP BY DATE_FORMAT(TakenAt, '%d-%M-%Y')) B
ON A.Date1 = B.Date2

或者只是为每件事都加上别名：

SELECT * FROM
  (SELECT DATE_FORMAT(t.Start, '%d-%M-%Y') AS Date1,
   SUM(TIMESTAMPDIFF(SECOND, t.Start, t.End)) / 3600 AS Hours
   FROM timeslot t WHERE t.UserID = 1
   GROUP BY DATE_FORMAT(t.Start, '%d-%M-%Y') ) A
LEFT OUTER JOIN
  (SELECT DATE_FORMAT(s.TakenAt, '%d-%M-%Y') AS Date2
   FROM snapshot s WHERE s.Deleted = 1
   GROUP BY DATE_FORMAT(s.TakenAt, '%d-%M-%Y')) B
ON A.Date1 = B.Date2