编辑代码

CREATE DATABASE dept_emp;
use dept_emp;
CREATE TABLE dept_emp (
    id VARCHAR(10),
    dept_id VARCHAR(10),
    name VARCHAR(50),
    gender VARCHAR(10),
    birth DATE
);
INSERT INTO dept_emp VALUES 
('0001', 'D1', '张三', '男', '1998-07-12'),
('0002', 'D2', '李四', '男', '1985-04-22'),
('0003', 'D3', '李娜', '女','1995-02-04'),
('0004', 'D4', '陆薇', '女','2001-09-19');

SELECT 
    dept_id,
    COUNT(id) AS cnt
FROM 
    dept_emp
GROUP BY 
    dept_id;
 
SELECT 
    dept_id,
    cnt,
    ROW_NUMBER() OVER (ORDER BY cnt DESC) AS rank
FROM (
    SELECT 
        dept_id,
        COUNT(id) AS cnt
    FROM 
        dept_emp
    GROUP BY 
        dept_id
) AS dept_counts;