MYSQL GROUP BY
mysql 我使用排序进行筛选,保证分组、排序之后我的第一条数据就是我要的数据
SELECT a.code ,
a.type AS 班型 ,MAX(a.num)
FROM
(
SELECT
*
FROM
cent_ylb_numclass
GROUP BY
CODE,
type
ORDER BY
CODE,
num DESC
) AS a
GROUP BY
`code`
ORACLE GROUP BY
oracle 使用开窗函数。根据序号取数即可
语法:
ROW_NUMBER() OVER(PARTITION BY COLUMN1 ORDER BY COLUMN2 [desc])
根据column1分组,根据column2排序
此次我们需要根据教师编码code进行分组,教师编码()code)和教师课程分组数(count(课程分组))降序排序
我们需要确定某个教师教的最多的课程是哪一个
SELECT
localtable.教师编码 as 教师编码,localtable.课程 as 分组
FROM
(SELECT
tablebiao.教师编码 as 教师编码,
tablebiao.教师姓名 as 教师姓名 ,
tablebiao.课程分组 as 课程,
COUNT(tablebiao.课程分组) AS num,
row_number () over (
PARTITION BY tablebiao.教师编码,
tablebiao.教师姓名
ORDER BY
tablebiao.教师编码,
COUNT(tablebiao.课程分组) DESC
) AS rn
FROM
tablebiao
GROUP BY
tablebiao.教师编码,
tablebiao.教师姓名,
tablebiao.课程分组
ORDER BY
tablebiao.教师编码,
COUNT(tablebiao.课程分组) DESC
) localtable
WHERE
localtable.rn = 1
如有更好的实现方法,或者指导意见请联系微信c243126035