数据库查询时出了个以前没见过的问题:
sql语句:
SELECT
question.id AS 'question.id',
question.company_id AS 'question.company_id',
question.robot_id AS 'question.robot_id',
question.category_id AS 'question.category_id',
question.content AS 'question.content',
question.type AS 'question.type',
question.available_time_type AS 'question.availableTimeType',
question.start_time AS 'question.start_time',
question.end_time AS 'question.end_time',
question.is_switch_staff AS 'question.is_switch_staff',
question.suggest_type AS 'question.suggest_type',
question.keyword AS 'question.keyword',
question.session_cnt AS 'question.session_cnt',
question.create_time AS 'question.create_time',
question.update_time AS 'question.update_time',
question.update_time_biz AS 'question.update_time_biz',
similarQuestion.id AS "similar_id",
similarQuestion.content AS "similar_content",
answer.id AS 'answer.id',
answer.content AS 'answer.content',
answer.type AS 'answer.type'
FROM
question_question question
LEFT JOIN answer_answer answer ON answer.question_id = question.id
LEFT JOIN question_question_channel questionChannel ON questionChannel.question_id = question.id
LEFT JOIN question_question similarQuestion ON similarQuestion.delete_flag = 0
AND similarQuestion.main_id = question.id
AND similarQuestion.type = 2
WHERE
1 = 1
AND question.delete_flag = 0
AND question.robot_id = 123
AND question.category_id IN ( 424, 423 )
AND question.type != 2
GROUP BY
question.id
ORDER BY
similarQuestion.id
LIMIT 0,
10
执行报错,错误信息:
1055 - Expression #17 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘udesk_km.similarQuestion.id’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by, Time: 0.001000s
百度翻译如下:
1055-select list的表达式17不在group by子句中,并且包含非聚合列’udesk_km.similarQuestion.id’,该列在功能上不依赖group by子句中的列;这与sql_mode=only_full_group_by不兼容
猜测是数据库版本问题。
将sql改为:
SELECT
question.id AS 'question.id',
question.company_id AS 'question.company_id',
question.robot_id AS 'question.robot_id',
question.category_id AS 'question.category_id',
question.content AS 'question.content',
question.type AS 'question.type',
question.available_time_type AS 'question.availableTimeType',
question.start_time AS 'question.start_time',
question.end_time AS 'question.end_time',
question.is_switch_staff AS 'question.is_switch_staff',
question.suggest_type AS 'question.suggest_type',
question.keyword AS 'question.keyword',
question.session_cnt AS 'question.session_cnt',
question.create_time AS 'question.create_time',
question.update_time AS 'question.update_time',
question.update_time_biz AS 'question.update_time_biz',
similarQuestion.id AS "similar_id",
similarQuestion.content AS "similar_content",
answer.id AS 'answer.id',
answer.content AS 'answer.content',
answer.type AS 'answer.type'
FROM
question_question question
LEFT JOIN answer_answer answer ON answer.question_id = question.id
LEFT JOIN question_question_channel questionChannel ON questionChannel.question_id = question.id
LEFT JOIN question_question similarQuestion ON similarQuestion.delete_flag = 0
AND similarQuestion.main_id = question.id
AND similarQuestion.type = 2
WHERE
1 = 1
AND question.delete_flag = 0
AND question.robot_id = 123
AND question.category_id IN ( 424, 423 )
AND question.type != 2
GROUP BY
question.id,
similarQuestion.id,
answer.id
ORDER BY
FIELD( question.id, similarQuestion.id )
执行正常!