需求:
查询出每个人2018年收藏量最高的专题信息和专题数量
select t1.creatorId as puid,t1.course_Id,t1.`name`,resSubCount as 收藏量,MAX(t2.resSubCount)
from mooc_subject t1
join mooc_subject_subscribeinfo t2 on t2.courseId =CONCAT(t1.course_Id)
where published = 1 and status = 0 and createtime > '2018-01-01 00:00:00' and t2.cataid = 100000001
GROUP BY t1.creatorId;
虽然也可以查询出最大resSubCount,但是数值和其他自动根本对应不上,因为group by返回分组后的组内第一条数据信息,max函数对应的是最大数量。
解决方法:
()括号内的sql按resSubCount DESC排序,外层sql分组得到第一条数据,即为最大数量数据,问题解决
select * from (select t1.creatorId as puid,t1.course_Id,t1.`name`,resSubCount as 收藏量 from mooc_subject t1
join mooc_subject_subscribeinfo t2 on t2.courseId =CONCAT(t1.course_Id)
where published = 1 and status = 0 and createtime > '2018-01-01 00:00:00' and t2.cataid = 100000001
ORDER BY t2.resSubCount DESC) as b
GROUP BY b.puid;