记录一条sql,最近公司需要我做一个跟进数据管理功能。
要求 项目名称一致的放在一起,然后按照每个项目更新记录最新的时间排序,再组内排序
sql具体如下
select
u.`name` as gjUser,
u.id as gjUserId,
pf.project_name as projcetNum,
p.`name` as projectName,
pf.project_follow_time as gjTime,
et.project_name as gjType,
pf.project_follow_detail as gjDetail,
pt.maxtime
FROM project_follow pf
left JOIN excel_template et ON pf.excel_template_id = et.id
left JOIN `user` u ON pf.follow_user_id = u.id
LEFT JOIN project p ON pf.project_name = p.project_no
LEFT JOIN (select pe.project_name,MAX(pe.project_follow_time) as maxtime FROM project_follow pe
WHERE pe.follow_user_id =23
GROUP BY pe.project_name) pt ON pf.project_name =pt.project_name
where pf.follow_user_id=23
ORDER BY pt.maxtime DESC,pf.project_follow_time DESC,p.`name`
难点主要是按照每个项目的记录更新最薪时间排序,因为公司用的mysql5.7,只能用原始的关键字。所以我就多进行了一次连表,这次连表是为了取得每个项目的最新时间,所以用了group by
加max()关键字。连表成功后 最终order by 排序就好做了。
图片是这条sql执行的结果