[mysql]加入两个子查询抛错误

发布时间: 2017/1/13 4:43:11
注意事项: 本文中文内容可能为机器翻译,如要查看英文原文请点击上面连接.

下面的查询出了什么错误?

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 Date1GROUP BY Date2GROUP BY A.Date1GROUP BY B.Date2 分别) 和我讲述了未知的列组语句中的 A.Date1

解决方法 1:

我相信这因为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 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
官方微信
官方QQ群
31647020