問(wèn)題:
[SQL] SELECT pi.paper_interviewee_id, pi.batch, pi.total_time,pi.use_time, pq.question_score, sum(pq.question_score), q.classification_id, qc.classification_name
from t_paper_interviewee pi, t_paper_question pq, t_question q, t_question_classification qc? WHERE pi.interviewee_id="1" and pi.correct = 1
and q.question_id = pq.question_id and qc.classification_id = q.classification_id
and pi.paper_id = pq.paper_id GROUP BY q.classification_id
[Err] 1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'clh-zq.pi.paper_interviewee_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by粪糙、
原因:
數(shù)據(jù)庫(kù)版本的問(wèn)題
MySQL 5.7.5和up實(shí)現(xiàn)了對(duì)功能依賴的檢測(cè)往衷。如果啟用了only_full_group_by SQL模式(在默認(rèn)情況下是這樣)筋栋,那么MySQL就會(huì)拒絕選擇列表、條件或順序列表引用的查詢蕴潦,這些查詢將引用組中未命名的非聚合列,而不是在功能上依賴于它們撞蜂。(在5.7.5之前焦读,MySQL沒(méi)有檢測(cè)到功能依賴項(xiàng),only_full_group_by在默認(rèn)情況下是不啟用的睬涧。關(guān)于前5.7.5行為的描述募胃,請(qǐng)參閱MySQL 5.6參考手冊(cè)旗唁。)。
解決:
SELECT sum(pq.question_score), pi.paper_interviewee_id, pi.batch, pi.total_time,pi.use_time, q.classification_id, qc.classification_name
from t_paper_interviewee pi, t_paper_question pq, t_question q, t_question_classification qc? WHERE pi.interviewee_id="1" and pi.correct = 1
and q.question_id = pq.question_id and qc.classification_id = q.classification_id
and pi.paper_id = pq.paper_id GROUP BY pi.paper_interviewee_id, pi.batch, qc.classification_name ,q.classification_id