-- 生成数据
2 DROP TABLE t1;
3 CREATE TABLE t1 (
4 id
INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
5 grade
TINYINT,
6 `name`
VARCHAR(
20),
7 val
INT);
8
9 INSERT INTO t1(grade,`name`,val)
10 VALUES
11 (
1,
‘x1‘,
91),
12 (
1,
‘x2‘,
44),
13 (
1,
‘x3‘,
78),
14 (
1,
‘x4‘,
90),
15 (
1,
‘x5‘,
32),
16 (
1,
‘x6‘,
1),
17
18 (
2,
‘y1‘,
10),
19 (
2,
‘y2‘,
55),
20 (
2,
‘y3‘,
11),
21 (
2,
‘y4‘,
99),
22 (
2,
‘y5‘,
79),
23 (
2,
‘y6‘,
100),
24
25 (
3,
‘z1‘,
100),
26 (
3,
‘z2‘,
20),
27 (
3,
‘z3‘,
10),
28 (
3,
‘z4‘,
60),
29 (
3,
‘z5‘,
55),
30 (
3,
‘z6‘,
90);
31
32 -- 获取结果
33 SELECT grade,`name`,val
34 FROM (
35 SELECT t.
*,
IF(
@var=grade,
@rank:
=@rank+1,
@rank:
=1)
AS rank,
@var:
=grade
36 FROM t1 t,(
SELECT @rank:
=0,
@var:
=0) v
37 ORDER BY t.`grade`,t.`val`
DESC
38 ) a
39 WHERE rank
<4;
返回结果：
grade name val
------ ------ --------
1 x1 91
1 x4 90
1 x3 78
2 y6 100
2 y4 99
2 y5 79
3 z1 100
3 z6 90
3 z4 60
最近群里有人说面试中会遇到mysql这样的坑
标签：class var 返回结果 prim 生成 weight desc har 遇到
