mysql 按日期查找不同表中的记录数

Question

我工作到深夜，今天花了很大一部分时间试图编写这个查询，但可惜的是，没有运气。希望这里有人能看到我所缺少的东西!

我正在尝试将另一个表的结果添加到我已经运行的查询中。这是工作查询:

`SELECT B.Usr_Sb_Grp, DATE(CONVERT_TZ(A.TimeStamp,'UTC','America/Chicago')) AS Day, B.ID, B.Qstn, 
SUM(IF(A.AnswerID = '0', 1, 0)) AS 'NULL', 
SUM(IF(A.AnswerID = '1', 1, 0)) AS 'Poor', 
SUM(IF(A.AnswerID = '2', 1, 0)) AS 'Good', 
SUM(IF(A.AnswerID = '3', 1, 0)) AS 'Great' 
FROM User_Responses AS A 
LEFT JOIN Question AS B ON A.QID = B.ID 
LEFT JOIN Cat_X_Ques AS C ON B.ID = C.QuesID 
LEFT JOIN Question_Categories AS D ON C.CatID = D.ID 
WHERE B.Usr_Sb_Grp='2' AND D.QType='7'AND MONTH(DATE(CONVERT_TZ(A.TimeStamp,'UTC','America/Chicago'))) = MONTH(NOW()) 
GROUP BY TO_DAYS(Day), B.ID`

它输出以下列:

Usr_Sb_Grp | Day | ID | Qstn | NULL | Poor | Good | Great

结果是准确的。

现在，我有另一个表要添加结果，特别是当前结果中的日期和 ID。意思是，我想扩展(Poor、Good、Great)的计数字段，并从同一日期的另一个表的结果中添加更多计数列。

这是我目前所处的位置，查询无济于事:

`SELECT B.Usr_Sb_Grp, DATE(CONVERT_TZ(A.TimeStamp,'UTC','America/Chicago')) AS Day, 
B.ID, B.Qstn, 
SUM(IF(A.AnswerID = '0', 1, 0)) AS 'NULL', 
SUM(IF(A.AnswerID = '1', 1, 0)) AS 'Poor', 
SUM(IF(A.AnswerID = '2', 1, 0)) AS 'Good', 
SUM(IF(A.AnswerID = '3', 1, 0)) AS 'Great', 
SUM(if(USB.AID='0', 1, 0)) AS 'NULL', 
SUM(if(USB.AID='21',1,0)) AS 'Service', 
SUM(if(USB.AID='22',1,0)) AS 'Menu Selection', 
SUM(if(USB.AID='23',1,0)) AS 'Taste', 
SUM(if(USB.AID='24',1,0)) AS 'Food Temperature' 
FROM User_Responses AS A 
LEFT JOIN Question AS B ON A.QID = B.ID 
LEFT JOIN Cat_X_Ques AS C ON B.ID = C.QuesID 
LEFT JOIN Question_Categories AS D ON C.CatID = D.ID 
LEFT JOIN User_Sub_Responses AS USB ON A.TimeStamp = DATE(USB.TimeStamp) LEFT JOIN Question_Child_Questions AS CQ ON USB.AID = CQ.ID 
LEFT JOIN Question_Sub_Questions AS SQ ON CQ.Parent_ID = SQ.ID 
WHERE B.Usr_Sb_Grp='2' AND D.QType='7'AND MONTH(DATE(CONVERT_TZ(A.TimeStamp,'UTC','America/Chicago'))) = MONTH(NOW()) 
GROUP BY TO_DAYS(Day), B.ID`

这会输出正确的格式:

Usr_Sb_Grp | Day | ID | Qstn | NULL | Poor | Good | Great | NULL | Service | Menu Selection | Taste | Food Temperature

...但是添加列的结果都是 0，即使检查 User_Sub_Responses 表，也肯定有与相应 QID 相关的本月结果。

我尝试了上述查询的许多不同变体，但没有成功。任何帮助将非常感激!

Answer 1

这个

A.TimeStamp = DATE(USB.TimeStamp)

应该是

A.TimeStamp = USB.TimeStamp

应用 DATE() 函数时，时间将从时间戳字段中删除，并且表链接将无法解析。如果还有其他原因需要应用此功能，例如您的数据类型不同，您可能需要首先修复该问题以确保完整性。