MySQL SQL语句：检索以"123"开头的查询字段的前三条记录信息

最编程 2024-08-14 21:50:53

...

查询各科成绩前三名的记录

解法（不考虑并列的情况）

该解法是不考虑并列的情况下
注意：此解法的前提是忽略成绩并列情况，否则出来的结果有问题。
先给结果sql，再讲思路：

SELECT
	a.s_id,
	a.c_id,
	a.s_score 
FROM
	score a
	LEFT JOIN score b ON a.c_id = b.c_id 
	AND a.s_score <= b.s_score 
GROUP BY
	a.s_id,
	a.c_id,
	a.s_score 
HAVING
	COUNT( b.s_id ) <= 3 
ORDER BY
	a.c_id,
	a.s_score DESC

思路：
1.首先我们可以看下表结构，如下

查询字段中前三个字符是123 mysql sql语句查询前三名的信息_结果集

两表进行关联后的结果：

SELECT
	a.s_id,
	a.c_id,
	a.s_score 
FROM
	score a
	LEFT JOIN score b ON a.c_id = b.c_id

查询字段中前三个字符是123 mysql sql语句查询前三名的信息_结果集_02

加入a.s_score<b.s_score然后接下来这个继续分析这个语句：

SELECT
	a.s_id,
	a.c_id,
	a.s_score 
FROM
	score a
	LEFT JOIN score b ON a.c_id = b.c_id AND a.s_score < b.s_score

也就是我们现在是a表的成绩，要小于或者等于关联的b表的成绩，我们拿a表一条数据来分析：

查询字段中前三个字符是123 mysql sql语句查询前三名的信息_表关联_03

关联后的表为：

查询字段中前三个字符是123 mysql sql语句查询前三名的信息_表结构_04

那么此时a.score = 90,执行a.score<=b.score ，则出来的结果只有1条：

查询字段中前三个字符是123 mysql sql语句查询前三名的信息_sql_05

我们拿课程1的第二名的记录来继续分析：

查询字段中前三个字符是123 mysql sql语句查询前三名的信息_结果集_06

表关联后的结果为：

查询字段中前三个字符是123 mysql sql语句查询前三名的信息_表结构_07

此时a.score = 80，则执行a.score<=b.score，过滤后的结果为2条记录，即

查询字段中前三个字符是123 mysql sql语句查询前三名的信息_sql_08

拿课程1的第三条记录来分析，则

查询字段中前三个字符是123 mysql sql语句查询前三名的信息_结果集_09

关联后的结果为：

查询字段中前三个字符是123 mysql sql语句查询前三名的信息_sql_10

此时a.score = 70,执行a.score<=b.socre，则过滤后的结果为3条记录：

查询字段中前三个字符是123 mysql sql语句查询前三名的信息_表关联_11

到这里我们可以知道，排名第一，过滤出一条，排名第二，过滤出2条，排名第三，过滤出第三条，依次类推，排名第四，过滤4条，排名第五，过滤5条…

接着分组，这个按a表的学生id、课程id、成绩分组即可。

然后我们要的是前三名的记录，我们根据我们推出的结果，也就是count(a.s_id)<=3即可得出前三名。

SELECT
	a.s_id,
	a.c_id,
	a.s_score 
FROM
	score a
	LEFT JOIN score b ON a.c_id = b.c_id 
	AND a.s_score <= b.s_score 
GROUP BY
	a.s_id,
	a.c_id,
	a.s_score 
HAVING
	COUNT( b.s_id ) <= 3

最后再按课程号进行排序，再按成绩进行降序显示即可

SELECT
	a.s_id,
	a.c_id,
	a.s_score 
FROM
	score a
	LEFT JOIN score b ON a.c_id = b.c_id 
	AND a.s_score <= b.s_score 
GROUP BY
	a.s_id,
	a.c_id,
	a.s_score 
HAVING
	COUNT( b.s_id ) <= 3 
ORDER BY
	a.c_id,
	a.s_score DESC

解法2（考虑并列的情况）

该解法是考虑并列的情况下

考虑并列的解法：

SELECT
	s1.*
FROM
	score s1
WHERE
	(
		SELECT
			count(1)
		FROM
			score s2
		WHERE
			s2.c_id = s1.c_id
		AND s2.s_score > s1.s_score
	) < 3
ORDER BY
	s1.c_id,
	s1.s_id

并列情况下的结果集举例：

查询字段中前三个字符是123 mysql sql语句查询前三名的信息_sql_12

查询各科成绩前二名的记录

学生成绩表：

CREATE TABLE `tabscore` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`stuId` int(11) DEFAULT NULL,
`stuName` varchar(255) DEFAULT NULL,
`course` varchar(255) DEFAULT NULL,
`score` float(3,1) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

INSERT INTO `tabscore` VALUES ('1', '1', '张三', '语文', '85.0');
INSERT INTO `tabscore` VALUES ('2', '1', '张三', '数学', '89.0');
INSERT INTO `tabscore` VALUES ('3', '2', '李四', '语文', '85.0');
INSERT INTO `tabscore` VALUES ('4', '2', '李四', '数学', '80.0');
INSERT INTO `tabscore` VALUES ('5', '3', '王五', '语文', '85.0');
INSERT INTO `tabscore` VALUES ('6', '3', '王五', '数学', '99.0');

查询字段中前三个字符是123 mysql sql语句查询前三名的信息_结果集_13

要求查询每科目前两名学生及成绩

select tabscore.* from tabscore
where 
(select count(1) from tabscore tb2 where tb2.course = tabscore.course and tb2.score>tabscore.score)<2

查询字段中前三个字符是123 mysql sql语句查询前三名的信息_sql_14

上一篇：《畅谈备考CDA Level Ⅰ（三）：小W和小L与你一起探索有趣知识》

下一篇：如何解决谷歌浏览器默认首页设置为好123的问题