mysql - 错误代码: 1111 Invalid use of group function

Question

您好，我有这个查询

SELECT perf_prog.date, MAX(perf_prog.end_time) , ADDTIME((shift_time.out), "01:00:00") FROM perf_prog 
INNER JOIN Shifts ON perf_prog.emp_id = Shifts.emp_id 
INNER JOIN shift_time ON Shifts.id = shift_time.id

WHERE perf_prog.emp_id = 920   GROUP BY perf_prog.date  

这是查询的输出

但我只想列出那些大于 shift_time.out 的 end_time 值

所以我这样做了

SELECT perf_prog.date, MAX(perf_prog.end_time) , ADDTIME((shift_time.out), "01:00:00") FROM perf_prog 
INNER JOIN Shifts ON perf_prog.emp_id = Shifts.emp_id 
INNER JOIN shift_time ON Shifts.id = shift_time.id

WHERE perf_prog.emp_id = 920 AND MAX(perf_prog.end_time) > ADDTIME((shift_time.out), "01:00:00")   GROUP BY perf_prog.date  

但我收到错误

Error Code: 1111
Invalid use of group function

在搜索 stackoverflow 时，解决问题的答案是将条件移至having子句

所以我得到了这个

SELECT perf_prog.date, MAX(perf_prog.end_time) , ADDTIME((shift_time.out), "01:00:00") FROM perf_prog 
INNER JOIN Shifts ON perf_prog.emp_id = Shifts.emp_id 
INNER JOIN shift_time ON Shifts.id = shift_time.id

WHERE perf_prog.emp_id = 920 GROUP BY perf_prog.date  
HAVING MAX(perf_prog.end_time)   > ADDTIME((shift_time.out), "01:00:00")

现在的问题是“having Clause”中的未知列“shift_time.out”

我是否遵循满足我需求的正确解决方案？谢谢。我一直在尝试找出解决方案，但无法使其发挥作用

Answer 1

您需要对列进行聚合。我想这可能就是你想要的:

SELECT pp.date, MAX(pp.end_time),
       MAX(ADDTIME((st.out), '01:00:00'))
FROM perf_prog pp INNER JOIN
     Shifts s
     ON pp.emp_id = s.emp_id INNER JOIN
     shift_time st
     ON s.id = st.id
WHERE pp.emp_id = 920
GROUP BY pp.date  
HAVING MAX(pp.end_time) > MAX(ADDTIME((st.out), '01:00:00))