sql find最大计数

eivgtgni 于 2021-07-26 发布在 Java

关注(0)|答案(1)|浏览(451)

我想从这里得到单位代码

SELECT
    max(maxcount) 
FROM(
        SELECT
            COUNT(studid) maxcount,
            semester,
            e.unitcode,
            u.unitname,
            to_char(ofyear, 'yyyy')
        FROM
            uni.enrolment e Join uni.unit u 
        ON 
            e.unitcode = u.unitcode
        WHERE
            to_char(ofyear, 'yyyy') = '2013'
        GROUP BY
            semester,
            e.unitcode,
            u.unitname,
            to_char(ofyear, 'yyyy')
    );

但我无法找回它。我在找对应于单位代码的最大值。

sql mysql Join group-by Max

来源：https://stackoverflow.com/questions/62002156/sql-find-max-of-count

1条答案

按热度按时间

igsr9ssn1#

如果要获得每单位代码的最大值，请使用聚合：

SELECT unitcode, MAX(maxcount) 
FROM (SELECT COUNT(studid) as maxcount, semester, e.unitcode, u.unitname
      FROM uni.enrolment e JOIN
           uni.unit u 
           ON  e.unitcode = u.unitcode
      WHERE ofyear >= DATE '2013-01-01' AND ofyear < DATE '2014-01-01'
      GROUP BY semester, e.unitcode, u.unitname
     ) eu
GROUP BY unitcode;

根据您的评论，您希望：

SELECT COUNT(studid) as maxcount, semester, e.unitcode, u.unitname
FROM uni.enrolment e JOIN
     uni.unit u 
     ON e.unitcode = u.unitcode
WHERE ofyear >= DATE '2013-01-01' AND ofyear < DATE '2014-01-01'
GROUP BY semester, e.unitcode, u.unitname
ORDER BY maxcount DESC
LIMIT 1

赞(0）回复(0）举报 2021-07-26

我来回答

sql find最大计数

1条答案

相关问题

热门标签

最新问答