需求: project_info表(别名info表)与project_info_comment (别名com表)是1对多的关系,info表的autoId与com表task_id相关联,当info表中task_no不等于null,status=postponed或者status=closed 时 且 com表的create_time为最新时,找出info.task_no,info.status,com.comment
错误的示范:
SELECT info.`auto_id`,info.task_no,info.status,com.`comment`
FROM project_info AS info LEFT JOIN project_info_comment AS com ON info.auto_id = com.task_id
WHERE info.task_no IS NOT NULL AND (info.status='CLOSED' OR info.status='POSTPONED')
GROUP BY info.auto_id
ORDER BY com.`create_time` DESC;
一开始我是这么写的,发现出来的数据不对,然后在网上查了一下,如果group by 之后又用order by ,实际上是对group by之后的所有结果进行排序,并且没有过滤多条数据,数量不正确。
正确的示范:
SELECT info.auto_id AS task_id, info.task_no,info.status,com.`comment`
FROM project_info info LEFT JOIN project_info_comment com
ON info.auto_id = com.task_id AND com.`create_time`=(
SELECT MAX(com_in.create_time) FROM project_info_comment com_in WHERE com_in.`task_id` = info.`auto_id`
)
WHERE info.task_no IS NOT NULL AND (info.status='CLOSED' OR info.status='POSTPONED');
这个相当于是info表及com表关联查询之后,又将其中create_time为最大的数据筛选出来