查询各个学科成绩最低分的学生(含并列最低分)
建表语句和示例数据
1-- mysql 建表
2CREATE TABLE `student_score` (
3 `session_id` bigint(20) NOT NULL COMMENT '考试场次ID',
4 `student_id` bigint(20) NOT NULL COMMENT '学生ID',
5 `subject_id` int(11) NOT NULL COMMENT '学科ID',
6 `score` decimal(6,2) NOT NULL COMMENT '成绩',
7 PRIMARY KEY (`session_id`,`student_id`,`subject_id`)
8) COMMENT='学生学科成绩';
9
10-- postgresql 建表
11CREATE TABLE "student_score" (
12 "session_id" int8 NOT NULL,
13 "student_id" int8 NOT NULL,
14 "subject_id" int4 NOT NULL,
15 "score" numeric(6,2) NOT NULL,
16 PRIMARY KEY ("session_id", "student_id", "subject_id")
17);
18COMMENT ON COLUMN "student_score"."session_id" IS '考试场次ID';
19COMMENT ON COLUMN "student_score"."student_id" IS '学生ID';
20COMMENT ON COLUMN "student_score"."subject_id" IS '学科ID';
21COMMENT ON COLUMN "student_score"."score" IS '成绩';
22COMMENT ON TABLE "student_score" IS '学生学科成绩';
23
24-- 示例数据
25INSERT INTO student_score (session_id, student_id, subject_id, score) VALUES
26(202406200001, 20151001001, 1001, 80.00),
27(202406200001, 20151001001, 1002, 68.00),
28(202406200001, 20151001001, 1003, 66.00),
29(202406200001, 20151001001, 1004, 94.00),
30(202406200001, 20151001001, 1005, 78.00),
31(202406200001, 20151001001, 1006, 72.00),
32(202406200001, 20151001001, 1007, 90.00),
33(202406200001, 20151001001, 1008, 84.00),
34(202406200001, 20151001002, 1001, 90.00),
35(202406200001, 20151001002, 1002, 68.00),
36(202406200001, 20151001002, 1003, 94.00),
37(202406200001, 20151001002, 1004, 80.00),
38(202406200001, 20151001002, 1005, 84.00),
39(202406200001, 20151001002, 1006, 68.00),
40(202406200001, 20151001002, 1007, 76.00),
41(202406200001, 20151001002, 1008, 94.00),
42(202406200001, 20151001003, 1001, 66.00),
43(202406200001, 20151001003, 1002, 100.00),
44(202406200001, 20151001003, 1003, 92.00),
45(202406200001, 20151001003, 1004, 76.00),
46(202406200001, 20151001003, 1005, 78.00),
47(202406200001, 20151001003, 1006, 70.00),
48(202406200001, 20151001003, 1007, 92.00),
49(202406200001, 20151001003, 1008, 70.00),
50(202406200001, 20151001004, 1001, 82.00),
51(202406200001, 20151001004, 1002, 100.00),
52(202406200001, 20151001004, 1003, 70.00),
53(202406200001, 20151001004, 1004, 88.00),
54(202406200001, 20151001004, 1005, 80.00),
55(202406200001, 20151001004, 1006, 66.00),
56(202406200001, 20151001004, 1007, 74.00),
57(202406200001, 20151001004, 1008, 64.00),
58(202406200001, 20151001005, 1001, 78.00),
59(202406200001, 20151001005, 1002, 80.00),
60(202406200001, 20151001005, 1003, 74.00),
61(202406200001, 20151001005, 1004, 62.00),
62(202406200001, 20151001005, 1005, 82.00),
63(202406200001, 20151001005, 1006, 86.00),
64(202406200001, 20151001005, 1007, 84.00),
65(202406200001, 20151001005, 1008, 94.00),
66(202406200001, 20151001006, 1001, 70.00),
67(202406200001, 20151001006, 1002, 88.00),
68(202406200001, 20151001006, 1003, 84.00),
69(202406200001, 20151001006, 1004, 94.00),
70(202406200001, 20151001006, 1005, 90.00),
71(202406200001, 20151001006, 1006, 82.00),
72(202406200001, 20151001006, 1007, 84.00),
73(202406200001, 20151001006, 1008, 74.00),
74(202406200001, 20151001007, 1001, 70.00),
75(202406200001, 20151001007, 1002, 94.00),
76(202406200001, 20151001007, 1003, 58.00),
77(202406200001, 20151001007, 1004, 76.00),
78(202406200001, 20151001007, 1005, 88.00),
79(202406200001, 20151001007, 1006, 70.00),
80(202406200001, 20151001007, 1007, 92.00),
81(202406200001, 20151001007, 1008, 82.00),
82(202406200001, 20151001008, 1001, 66.00),
83(202406200001, 20151001008, 1002, 100.00),
84(202406200001, 20151001008, 1003, 74.00),
85(202406200001, 20151001008, 1004, 94.00),
86(202406200001, 20151001008, 1005, 92.00),
87(202406200001, 20151001008, 1006, 70.00),
88(202406200001, 20151001008, 1007, 88.00),
89(202406200001, 20151001008, 1008, 98.00),
90(202406200001, 20151001009, 1001, 70.00),
91(202406200001, 20151001009, 1002, 80.00),
92(202406200001, 20151001009, 1003, 68.00),
93(202406200001, 20151001009, 1004, 76.00),
94(202406200001, 20151001009, 1005, 82.00),
95(202406200001, 20151001009, 1006, 66.00),
96(202406200001, 20151001009, 1007, 80.00),
97(202406200001, 20151001009, 1008, 88.00),
98(202406200001, 20151001010, 1001, 80.00),
99(202406200001, 20151001010, 1002, 76.00),
100(202406200001, 20151001010, 1003, 82.00),
101(202406200001, 20151001010, 1004, 84.00),
102(202406200001, 20151001010, 1005, 86.00),
103(202406200001, 20151001010, 1006, 84.00),
104(202406200001, 20151001010, 1007, 80.00),
105(202406200001, 20151001010, 1008, 78.00),
106(202406200001, 20151001011, 1001, 80.00),
107(202406200001, 20151001011, 1002, 90.00),
108(202406200001, 20151001011, 1003, 98.00),
109(202406200001, 20151001011, 1004, 76.00),
110(202406200001, 20151001011, 1005, 88.00),
111(202406200001, 20151001011, 1006, 76.00),
112(202406200001, 20151001011, 1007, 84.00),
113(202406200001, 20151001011, 1008, 98.00),
114(202406200001, 20151001012, 1001, 84.00),
115(202406200001, 20151001012, 1002, 76.00),
116(202406200001, 20151001012, 1003, 92.00),
117(202406200001, 20151001012, 1004, 68.00),
118(202406200001, 20151001012, 1005, 68.00),
119(202406200001, 20151001012, 1006, 78.00),
120(202406200001, 20151001012, 1007, 72.00),
121(202406200001, 20151001012, 1008, 72.00),
122(202406200001, 20151001013, 1001, 92.00),
123(202406200001, 20151001013, 1002, 88.00),
124(202406200001, 20151001013, 1003, 68.00),
125(202406200001, 20151001013, 1004, 92.00),
126(202406200001, 20151001013, 1005, 90.00),
127(202406200001, 20151001013, 1006, 78.00),
128(202406200001, 20151001013, 1007, 98.00),
129(202406200001, 20151001013, 1008, 88.00),
130(202406200001, 20151001014, 1001, 90.00),
131(202406200001, 20151001014, 1002, 100.00),
132(202406200001, 20151001014, 1003, 66.00),
133(202406200001, 20151001014, 1004, 80.00),
134(202406200001, 20151001014, 1005, 72.00),
135(202406200001, 20151001014, 1006, 80.00),
136(202406200001, 20151001014, 1007, 80.00),
137(202406200001, 20151001014, 1008, 82.00),
138(202406200001, 20151001015, 1001, 86.00),
139(202406200001, 20151001015, 1002, 64.00),
140(202406200001, 20151001015, 1003, 72.00),
141(202406200001, 20151001015, 1004, 80.00),
142(202406200001, 20151001015, 1005, 86.00),
143(202406200001, 20151001015, 1006, 68.00),
144(202406200001, 20151001015, 1007, 80.00),
145(202406200001, 20151001015, 1008, 90.00),
146(202406200001, 20151001016, 1001, 100.00),
147(202406200001, 20151001016, 1002, 70.00),
148(202406200001, 20151001016, 1003, 92.00),
149(202406200001, 20151001016, 1004, 98.00),
150(202406200001, 20151001016, 1005, 88.00),
151(202406200001, 20151001016, 1006, 90.00),
152(202406200001, 20151001016, 1007, 92.00),
153(202406200001, 20151001016, 1008, 74.00),
154(202406200001, 20151001017, 1001, 80.00),
155(202406200001, 20151001017, 1002, 94.00),
156(202406200001, 20151001017, 1003, 76.00),
157(202406200001, 20151001017, 1004, 80.00),
158(202406200001, 20151001017, 1005, 100.00),
159(202406200001, 20151001017, 1006, 84.00),
160(202406200001, 20151001017, 1007, 78.00),
161(202406200001, 20151001017, 1008, 76.00),
162(202406200001, 20151001018, 1001, 82.00),
163(202406200001, 20151001018, 1002, 64.00),
164(202406200001, 20151001018, 1003, 76.00),
165(202406200001, 20151001018, 1004, 66.00),
166(202406200001, 20151001018, 1005, 78.00),
167(202406200001, 20151001018, 1006, 76.00),
168(202406200001, 20151001018, 1007, 86.00),
169(202406200001, 20151001018, 1008, 72.00),
170(202406200001, 20151001019, 1001, 94.00),
171(202406200001, 20151001019, 1002, 76.00),
172(202406200001, 20151001019, 1003, 94.00),
173(202406200001, 20151001019, 1004, 90.00),
174(202406200001, 20151001019, 1005, 84.00),
175(202406200001, 20151001019, 1006, 66.00),
176(202406200001, 20151001019, 1007, 92.00),
177(202406200001, 20151001019, 1008, 88.00),
178(202406200001, 20151001020, 1001, 86.00),
179(202406200001, 20151001020, 1002, 96.00),
180(202406200001, 20151001020, 1003, 84.00),
181(202406200001, 20151001020, 1004, 74.00),
182(202406200001, 20151001020, 1005, 68.00),
183(202406200001, 20151001020, 1006, 86.00),
184(202406200001, 20151001020, 1007, 90.00),
185(202406200001, 20151001020, 1008, 100.00),
186(202506230001, 20151001001, 1001, 80.00),
187(202506230001, 20151001001, 1002, 78.00),
188(202506230001, 20151001001, 1003, 74.00),
189(202506230001, 20151001001, 1004, 68.00),
190(202506230001, 20151001001, 1005, 82.00),
191(202506230001, 20151001001, 1006, 78.00),
192(202506230001, 20151001001, 1007, 74.00),
193(202506230001, 20151001001, 1008, 92.00),
194(202506230001, 20151001002, 1001, 72.00),
195(202506230001, 20151001002, 1002, 74.00),
196(202506230001, 20151001002, 1003, 86.00),
197(202506230001, 20151001002, 1004, 84.00),
198(202506230001, 20151001002, 1005, 92.00),
199(202506230001, 20151001002, 1006, 74.00),
200(202506230001, 20151001002, 1007, 86.00),
201(202506230001, 20151001002, 1008, 96.00),
202(202506230001, 20151001003, 1001, 70.00),
203(202506230001, 20151001003, 1002, 84.00),
204(202506230001, 20151001003, 1003, 90.00),
205(202506230001, 20151001003, 1004, 66.00),
206(202506230001, 20151001003, 1005, 76.00),
207(202506230001, 20151001003, 1006, 96.00),
208(202506230001, 20151001003, 1007, 74.00),
209(202506230001, 20151001003, 1008, 96.00),
210(202506230001, 20151001004, 1001, 90.00),
211(202506230001, 20151001004, 1002, 64.00),
212(202506230001, 20151001004, 1003, 82.00),
213(202506230001, 20151001004, 1004, 80.00),
214(202506230001, 20151001004, 1005, 92.00),
215(202506230001, 20151001004, 1006, 70.00),
216(202506230001, 20151001004, 1007, 80.00),
217(202506230001, 20151001004, 1008, 60.00),
218(202506230001, 20151001005, 1001, 92.00),
219(202506230001, 20151001005, 1002, 82.00),
220(202506230001, 20151001005, 1003, 86.00),
221(202506230001, 20151001005, 1004, 68.00),
222(202506230001, 20151001005, 1005, 94.00),
223(202506230001, 20151001005, 1006, 82.00),
224(202506230001, 20151001005, 1007, 88.00),
225(202506230001, 20151001005, 1008, 84.00),
226(202506230001, 20151001006, 1001, 84.00),
227(202506230001, 20151001006, 1002, 82.00),
228(202506230001, 20151001006, 1003, 66.00),
229(202506230001, 20151001006, 1004, 92.00),
230(202506230001, 20151001006, 1005, 72.00),
231(202506230001, 20151001006, 1006, 74.00),
232(202506230001, 20151001006, 1007, 80.00),
233(202506230001, 20151001006, 1008, 64.00),
234(202506230001, 20151001007, 1001, 76.00),
235(202506230001, 20151001007, 1002, 68.00),
236(202506230001, 20151001007, 1003, 88.00),
237(202506230001, 20151001007, 1004, 76.00),
238(202506230001, 20151001007, 1005, 88.00),
239(202506230001, 20151001007, 1006, 86.00),
240(202506230001, 20151001007, 1007, 74.00),
241(202506230001, 20151001007, 1008, 74.00),
242(202506230001, 20151001008, 1001, 100.00),
243(202506230001, 20151001008, 1002, 80.00),
244(202506230001, 20151001008, 1003, 86.00),
245(202506230001, 20151001008, 1004, 88.00),
246(202506230001, 20151001008, 1005, 80.00),
247(202506230001, 20151001008, 1006, 92.00),
248(202506230001, 20151001008, 1007, 78.00),
249(202506230001, 20151001008, 1008, 100.00),
250(202506230001, 20151001009, 1001, 86.00),
251(202506230001, 20151001009, 1002, 62.00),
252(202506230001, 20151001009, 1003, 80.00),
253(202506230001, 20151001009, 1004, 90.00),
254(202506230001, 20151001009, 1005, 90.00),
255(202506230001, 20151001009, 1006, 94.00),
256(202506230001, 20151001009, 1007, 74.00),
257(202506230001, 20151001009, 1008, 80.00),
258(202506230001, 20151001010, 1001, 72.00),
259(202506230001, 20151001010, 1002, 84.00),
260(202506230001, 20151001010, 1003, 82.00),
261(202506230001, 20151001010, 1004, 92.00),
262(202506230001, 20151001010, 1005, 84.00),
263(202506230001, 20151001010, 1006, 60.00),
264(202506230001, 20151001010, 1007, 84.00),
265(202506230001, 20151001010, 1008, 76.00),
266(202506230001, 20151001011, 1001, 72.00),
267(202506230001, 20151001011, 1002, 84.00),
268(202506230001, 20151001011, 1003, 88.00),
269(202506230001, 20151001011, 1004, 78.00),
270(202506230001, 20151001011, 1005, 84.00),
271(202506230001, 20151001011, 1006, 82.00),
272(202506230001, 20151001011, 1007, 86.00),
273(202506230001, 20151001011, 1008, 98.00),
274(202506230001, 20151001012, 1001, 100.00),
275(202506230001, 20151001012, 1002, 92.00),
276(202506230001, 20151001012, 1003, 72.00),
277(202506230001, 20151001012, 1004, 84.00),
278(202506230001, 20151001012, 1005, 76.00),
279(202506230001, 20151001012, 1006, 74.00),
280(202506230001, 20151001012, 1007, 66.00),
281(202506230001, 20151001012, 1008, 88.00),
282(202506230001, 20151001013, 1001, 78.00),
283(202506230001, 20151001013, 1002, 74.00),
284(202506230001, 20151001013, 1003, 82.00),
285(202506230001, 20151001013, 1004, 100.00),
286(202506230001, 20151001013, 1005, 68.00),
287(202506230001, 20151001013, 1006, 96.00),
288(202506230001, 20151001013, 1007, 76.00),
289(202506230001, 20151001013, 1008, 76.00),
290(202506230001, 20151001014, 1001, 76.00),
291(202506230001, 20151001014, 1002, 82.00),
292(202506230001, 20151001014, 1003, 92.00),
293(202506230001, 20151001014, 1004, 86.00),
294(202506230001, 20151001014, 1005, 72.00),
295(202506230001, 20151001014, 1006, 88.00),
296(202506230001, 20151001014, 1007, 82.00),
297(202506230001, 20151001014, 1008, 70.00),
298(202506230001, 20151001015, 1001, 90.00),
299(202506230001, 20151001015, 1002, 94.00),
300(202506230001, 20151001015, 1003, 62.00),
301(202506230001, 20151001015, 1004, 70.00),
302(202506230001, 20151001015, 1005, 96.00),
303(202506230001, 20151001015, 1006, 76.00),
304(202506230001, 20151001015, 1007, 86.00),
305(202506230001, 20151001015, 1008, 88.00),
306(202506230001, 20151001016, 1001, 98.00),
307(202506230001, 20151001016, 1002, 82.00),
308(202506230001, 20151001016, 1003, 98.00),
309(202506230001, 20151001016, 1004, 90.00),
310(202506230001, 20151001016, 1005, 90.00),
311(202506230001, 20151001016, 1006, 86.00),
312(202506230001, 20151001016, 1007, 84.00),
313(202506230001, 20151001016, 1008, 86.00),
314(202506230001, 20151001017, 1001, 86.00),
315(202506230001, 20151001017, 1002, 74.00),
316(202506230001, 20151001017, 1003, 84.00),
317(202506230001, 20151001017, 1004, 72.00),
318(202506230001, 20151001017, 1005, 76.00),
319(202506230001, 20151001017, 1006, 96.00),
320(202506230001, 20151001017, 1007, 94.00),
321(202506230001, 20151001017, 1008, 68.00),
322(202506230001, 20151001018, 1001, 80.00),
323(202506230001, 20151001018, 1002, 76.00),
324(202506230001, 20151001018, 1003, 82.00),
325(202506230001, 20151001018, 1004, 80.00),
326(202506230001, 20151001018, 1005, 80.00),
327(202506230001, 20151001018, 1006, 72.00),
328(202506230001, 20151001018, 1007, 96.00),
329(202506230001, 20151001018, 1008, 92.00),
330(202506230001, 20151001019, 1001, 96.00),
331(202506230001, 20151001019, 1002, 68.00),
332(202506230001, 20151001019, 1003, 96.00),
333(202506230001, 20151001019, 1004, 72.00),
334(202506230001, 20151001019, 1005, 88.00),
335(202506230001, 20151001019, 1006, 64.00),
336(202506230001, 20151001019, 1007, 100.00),
337(202506230001, 20151001019, 1008, 84.00),
338(202506230001, 20151001020, 1001, 74.00),
339(202506230001, 20151001020, 1002, 78.00),
340(202506230001, 20151001020, 1003, 88.00),
341(202506230001, 20151001020, 1004, 70.00),
342(202506230001, 20151001020, 1005, 68.00),
343(202506230001, 20151001020, 1006, 78.00),
344(202506230001, 20151001020, 1007, 76.00),
345(202506230001, 20151001020, 1008, 86.00);
mysql 查询各个学科成绩最低分的学生(含并列最低分)
1-- 通用查询方式（应该适用于所有的数据库的所有版本）
2SELECT s1.subject_id, s1.student_id, s1.score
3FROM student_score s1
4INNER JOIN (
5 SELECT subject_id, MIN(score) AS min_score
6 FROM student_score
7 WHERE session_id = 202406200001
8 GROUP BY subject_id
9) s2 ON s1.subject_id = s2.subject_id AND s1.score = s2.min_score
10WHERE s1.session_id = 202406200001
11ORDER BY s1.subject_id;
12
13-- 使用rank函数(mysql8之前的版本不支持)
14SELECT subject_id, student_id, score
15FROM (
16 SELECT subject_id, student_id, score,
17 RANK() OVER(PARTITION BY subject_id ORDER BY score ASC) AS sort
18 FROM student_score
19 WHERE session_id = 202406200001
20) ranked_scores
21WHERE sort = 1;
22
23-- 使用公用表达式(WITH AS子句)配合rank函数(mysql8之前的版本不支持)
24WITH ranked_scores AS (
25 SELECT subject_id, student_id, score,
26 RANK() OVER (PARTITION BY subject_id ORDER BY score ASC) AS sort,
27 COUNT(*) OVER (PARTITION BY subject_id ORDER BY score ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS without_same_sort
28 FROM student_score
29 WHERE session_id = 202406200001
30)
31SELECT subject_id, student_id, score, sort, without_same_sort
32FROM ranked_scores
33WHERE sort = 1;
postgresql 查询各个学科成绩最低分的学生(含并列最低分)
1-- 和mysql8类似，使用公用表达式(WITH AS子句)配合rank函数
2WITH temp_table AS (
3 SELECT subject_id, student_id, score,
4 RANK() OVER (PARTITION BY subject_id ORDER BY score ASC) AS sort
5 FROM student_score
6 WHERE session_id = 202406200001
7)
8SELECT subject_id, student_id, score, sort
9FROM temp_table
10WHERE sort = 1;
评论