MySQL查询

Question

CREATE TABLE IF NOT EXISTS t_stu(
    id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    gmt_create DATETIME,
    gmt_modified DATETIME,
    name VARCHAR(20),
    num VARCHAR(20),
    sex CHAR(2),
    born DATE,
    cla_id BIGINT UNSIGNED
);

INSERT INTO t_stu(name, num, sex, born, cla_id, gmt_create) VALUES('汤','20130090221','男','1991-10-22', 1, NOW());
INSERT INTO t_stu(name, num, sex, born, cla_id, gmt_create) VALUES('暴','20130090222','女','1989-06-10', 1, NOW());
INSERT INTO t_stu(name, num, sex, born, cla_id, gmt_create) VALUES('凤','20130090223','女','1993-07-04', 1, NOW());
INSERT INTO t_stu(name, num, sex, born, cla_id, gmt_create) VALUES('佴','20130090224','男','1990-09-10', 1, NOW());
INSERT INTO t_stu(name, num, sex, born, cla_id, gmt_create) VALUES('宁','20130090225','女','1991-07-07', 2, NOW());
INSERT INTO t_stu(name, num, sex, born, cla_id, gmt_create) VALUES('瀚','20130090226','男','1992-04-01', 2, NOW());
INSERT INTO t_stu(name, num, sex, born, cla_id, gmt_create) VALUES('汲','20130090227','女','1995-05-26', 2, NOW());
INSERT INTO t_stu(name, num, sex, born, cla_id, gmt_create) VALUES('童','20130090228','男','1988-02-09', 2, NOW());
INSERT INTO t_stu(name, num, sex, born, cla_id, gmt_create) VALUES('佩','20130090229','女','1991-01-27', 2, NOW());
INSERT INTO t_stu(name, num, sex, born, cla_id, gmt_create) VALUES('充','20130090230','女','1993-07-12', 2, NOW());

1.　查询每个班年龄最小的2个学生
2.　查询每个班年龄第二小的学生

这两个查询怎么写？没找到思路

Answer 1

1 查询每个班年龄最小的2个学生

思路： 先根据 cla_id 和 born 排序，然后设置两个变量，一个记录上一条记录的 cla_id, 一个记录当前记录属于该班排名第几。遍历该表，如果当前 cla_id = 上一条 cla_id，那么就是同一个班，变量 rn + 1，这样就得到一个记录所有班级的学生排名的子表，要取每班最小 n 个，只需要扫描这个子表并加上 排名 <= n 这个条件

SELECT
    sub_table.id,
    sub_table.gmt_create,
    sub_table.gmt_modified,
    sub_table.name,
    sub_table.num,
    sub_table.sex,
    sub_table.born,
    sub_table.cla_id
FROM
    (
        SELECT
            *, CASE
        WHEN @last = cla_id THEN
            @rn := @rn + 1
        ELSE
            @rn := 1
        END AS rn,
        @last := cla_id
    FROM
        (SELECT @last := NULL, @rn := 1) vars,
        t_stu
    ORDER BY
        cla_id,
        born desc
    ) sub_table
WHERE
    rn <= 2;

2 排名第二小的，直接设置条件为 rn = 2

Answer 2

参考：https://www.jianshu.com/p/1e5...

1.select a.* from t_stu a
where (select count(*) from t_stu where cla_id=a.cla_id and born > a.born ) <2
order by a.born desc;

2.基于第一个的结果再筛选

Answer 3

from 子查询,case when

Answer 4

问1、先得到每个班最小的生日，后再找该生日的详细信息
SELECT * FROM t_stu where born in (SELECT max(born) FROM t_stu GROUP BY cla_id);
问2、先得到每个班最小的生日后过滤，然后再找，即倒数第二小的
SELECT * FROM t_stu where born in (SELECT max(born) from t_stu where born not in (select max(born) FROM t_stu GROUP BY cla_id) group by cla_id);