mysql按类别查询前几的记录 - pz_ww

举个例子：

mysql的表结构如下：

班级表（tclass）: id ,name

课程表（tcourse）: id,name

学生表（tstudent）: id,code,name,classid

成绩表（tscore）: id,studentid,courseid,score

1、查询出各科成绩前三的学生

SELECT
    s1.courseid,
    s1.studentid,
    s1.score 
FROM
    tscore s1
    LEFT JOIN ( SELECT DISTINCT courseid, score FROM tscore ) s2 ON s1.courseid = s2.courseid 
    AND s1.score < s2.score 
GROUP BY
    s1.courseid,
    s1.studentid,
    s1.score 
HAVING
    COUNT(*)< 3 
ORDER BY
    s1.courseid,
    s1.score DESC;

sql如上，可以查询各个课程前三的记录

首先要理解一下下面这句sql

SELECT
    s1.courseid,
    s1.studentid,
    s1.score 
FROM
    tscore s1
    LEFT JOIN ( SELECT DISTINCT courseid, score FROM tscore ) s2 ON s1.courseid = s2.courseid 
    AND s1.score < s2.score;

以成绩表为主表左连接 SELECT DISTINCT courseid, score FROM tscore查出来的各科成绩去重记录表（不去重也可以，最好去重减少从表的数据量），连接条件是主表和从表的课程id相等，并且主表的分数小于从表分数，

这样得到的结果是：每科成绩最高的数据只有一条（主表本身有一条数据，从表没有符合的，因为左连接，所以还是有一条主表数据），剩下的每科的各个分数数据条数跟处于第几位有关（主表本身一条，从表分数比主表分数高的都符合，多少条符合就有多少条数据），可以加排序看一下结果，如下：

SELECT
    s1.courseid,
    s1.studentid,
    s1.score,
    s2.score
FROM
    tscore s1
    LEFT JOIN ( SELECT DISTINCT courseid, score FROM tscore ) s2 ON s1.courseid = s2.courseid 
    AND s1.score < s2.score
ORDER BY
    s1.courseid,
    s1.score DESC;

除了主表最高分是第一，其他的有n条数据就是第n+1高分

然后再分组，以及通过having筛选出前3的分数

GROUP BY
    s1.courseid,
    s1.studentid,
    s1.score 
HAVING
    COUNT(*)< 3

之后如果再需要学生编号、学生名字、课程名字，则再通过内联学生表和课程表拿到对应的数据就可以了。

2、查询总成绩年级前五的学生

其实跟上面的思路是一样的，只不过多了求和

SELECT
    zt1.studentid,
    zt1.zscore 
FROM
    ( SELECT studentid, SUM( score ) AS zscore FROM tscore GROUP BY studentid ) AS zt1
    LEFT JOIN ( SELECT DISTINCT SUM( score ) AS zscore FROM tscore GROUP BY studentid ) AS zt2 ON zt1.zscore < zt2.zscore 
GROUP BY
    zt1.studentid,
    zt1.zscore 
HAVING
    COUNT(*)< 5 
ORDER BY
    zt1.zscore DESC;

3、查询各班级总成绩前三的学生

查询前几的思路都跟第1的思路一样

SELECT
    zt1.classid,
    zt1.studentid,
    zt1.zscore 
FROM
    (
    SELECT
        studentid,
        SUM( score ) AS zscore,
        st.classid,
        st.`code`,
        st.`name` 
    FROM
        tscore
        INNER JOIN tstudent st ON studentid = st.id 
    GROUP BY
        studentid 
    ) zt1
    LEFT JOIN (
    SELECT DISTINCT
        SUM( score ) AS zscore,
        st.classid 
    FROM
        tscore
        INNER JOIN tstudent st ON studentid = st.id 
    GROUP BY
        studentid,
        st.classid 
    ) zt2 ON zt1.classid = zt2.classid 
    AND zt1.zscore < zt2.zscore 
GROUP BY
    zt1.studentid,
    zt1.zscore,
    zt1.classid 
HAVING
    count(*)< 3 
ORDER BY
    zt1.classid,
    zt1.zscore DESC;

4、提供上述问题的创表和创数据sql

CREATE TABLE `tclass` (
  `id` int NOT NULL,
  `name` varchar(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

CREATE TABLE `tcourse` (
  `id` int NOT NULL,
  `name` varchar(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

CREATE TABLE `tstudent` (
  `id` int NOT NULL,
  `code` varchar(10) NOT NULL,
  `name` varchar(10) NOT NULL,
  `classid` int NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

CREATE TABLE `tscore` (
  `id` int NOT NULL,
  `studentid` int NOT NULL,
  `courseid` int NOT NULL,
  `score` int NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

INSERT INTO `tclass` (`id`, `name`) VALUES (1001, '班级6-1'),(1002, '班级6-2'),(1003, '班级6-3');
INSERT INTO `tcourse` (`id`, `name`) VALUES (2001, '语文'),(2002, '数学'),(2003, '英语');

INSERT INTO `tstudent` (`id`, `code`, `name`, `classid`) VALUES (3001, '20230001', '张一', 1001),(3002, '20230002', '李二', 1001),(3003, '20230003', '王三', 1001),(3004, '20230004', '赵四', 1001),
(3005, '20230005', '刘五', 1001),(3006, '20230006', '唐六', 1001),(3007, '20230007', '宋七', 1001),(3008, '20230008', '元八', 1001),(3009, '20230009', '明九', 1001),(3010, '20230010', '清十', 1001),
(3011, '20230011', '张进', 1002),(3012, '20230012', '李说', 1002),(3013, '20230013', '王时', 1002),(3014, '20230014', '赵舒', 1002),(3015, '20230015', '刘好', 1002),(3016, '20230016', '唐阿', 1002),
(3017, '20230017', '宋公', 1002),(3018, '20230018', '元感', 1002),(3019, '20230019', '明更', 1002),(3020, '20230020', '清案', 1002),(3021, '20230021', '张互动', 1003),(3022, '20230022', '李积分', 1003),
(3023, '20230023', '王哇', 1003),(3024, '20230024', '赵发', 1003),(3025, '20230025', '刘缺', 1003),(3026, '20230026', '唐测', 1003),(3027, '20230027', '宋那', 1003),(3028, '20230028', '元真', 1003),
(3029, '20230029', '明不', 1003),(3030, '20230030', '清阿文', 1003);

INSERT INTO `tscore` (`id`, `studentid`, `courseid`, `score`) VALUES (4001, 3001, 2001, 80),(4002, 3001, 2002, 81),(4003, 3001, 2003, 71),(4004, 3002, 2001, 76),(4005, 3002, 2002, 87),
(4006, 3002, 2003, 41),(4007, 3003, 2001, 89),(4008, 3003, 2002, 65),(4009, 3003, 2003, 90),(4010, 3004, 2001, 65),(4011, 3004, 2002, 58),(4012, 3004, 2003, 78),(4013, 3005, 2001, 67),
(4014, 3005, 2002, 54),(4015, 3005, 2003, 79),(4016, 3006, 2001, 87),(4017, 3006, 2002, 90),(4018, 3006, 2003, 88),(4019, 3007, 2001, 66),(4020, 3007, 2002, 73),(4021, 3007, 2003, 77),
(4022, 3008, 2001, 27),(4023, 3008, 2002, 76),(4024, 3008, 2003, 60),(4025, 3009, 2001, 76),(4026, 3009, 2002, 77),(4027, 3009, 2003, 83),(4028, 3010, 2001, 56),(4029, 3010, 2002, 68),
(4030, 3010, 2003, 83),(4031, 3011, 2001, 70),(4032, 3011, 2002, 61),(4033, 3011, 2003, 91),(4034, 3012, 2001, 74),(4035, 3012, 2002, 83),(4036, 3012, 2003, 71),(4037, 3013, 2001, 79),
(4038, 3013, 2002, 85),(4039, 3013, 2003, 60),(4040, 3014, 2001, 35),(4041, 3014, 2002, 68),(4042, 3014, 2003, 58),(4043, 3015, 2001, 77),(4044, 3015, 2002, 64),(4045, 3015, 2003, 89),
(4046, 3016, 2001, 47),(4047, 3016, 2002, 70),(4048, 3016, 2003, 98),(4049, 3017, 2001, 76),(4050, 3017, 2002, 53),(4051, 3017, 2003, 87),(4052, 3018, 2001, 87),(4053, 3018, 2002, 96),
(4054, 3018, 2003, 90),(4055, 3019, 2001, 66),(4056, 3019, 2002, 67),(4057, 3019, 2003, 83),(4058, 3020, 2001, 66),(4059, 3020, 2002, 58),(4060, 3020, 2003, 73),(4061, 3021, 2001, 70),
(4062, 3021, 2002, 71),(4063, 3021, 2003, 81),(4064, 3022, 2001, 76),(4065, 3022, 2002, 82),(4066, 3022, 2003, 75),(4067, 3023, 2001, 77),(4068, 3023, 2002, 87),(4069, 3023, 2003, 67),
(4070, 3024, 2001, 38),(4071, 3024, 2002, 67),(4072, 3024, 2003, 59),(4073, 3025, 2001, 87),(4074, 3025, 2002, 84),(4075, 3025, 2003, 79),(4076, 3026, 2001, 57),(4077, 3026, 2002, 60),
(4078, 3026, 2003, 78),(4079, 3027, 2001, 86),(4080, 3027, 2002, 63),(4081, 3027, 2003, 67),(4082, 3028, 2001, 77),(4083, 3028, 2002, 76),(4084, 3028, 2003, 90),(4085, 3029, 2001, 96),
(4086, 3029, 2002, 87),(4087, 3029, 2003, 83),(4088, 3030, 2001, 66),(4089, 3030, 2002, 68),(4090, 3030, 2003, 78);

5、记录一下mysql替换字符串

详情可查看：https://www.cnblogs.com/zlf2000/p/16741839.html

a、使用REPLACE()函数

REPLACE()函数用新的子字符串替换字符串中所有出现的子字符串。

注意：此函数执行区分大小写的替换。

REPLACE(string, from_string, new_string)

select REPLACE(`name`,'彭','清')as `name` from tstudent WHERE `name` like '彭%';

b、使用INSERT()函数

INSERT()函数在指定位置的字符串中插入一个字符串，并插入一定数量的字符。

INSERT(string, position, number, string2)

select INSERT(`name`,1,1,'清') as `name` from tstudent WHERE `name` like '彭%';

可以用在修改语句

UPDATE tstudent set `name`=REPLACE(`name`,'彭','清') WHERE `name` like '彭%';
UPDATE tstudent set `name`=INSERT(`name`,1,1,'清') WHERE `name` like '彭%';

发表于 2023-03-15 00:15 pz_ww 阅读(158) 评论(0) 编辑收藏举报