用test数据库中的test表(表中有两列a b),表如下:
a b
20 1.0
2 24.0
20 40.0
20 3.0
2 37.0
3 13.0
3 30.0
2 14.0
2 22.0
求第3大值。
SET @num1=0;
SET @aa=0;
SELECT a.a,SUM(a.B),MAX(c.b) FROM test a LEFT JOIN
(
SELECT a,b FROM (
SELECT *,@num1:=IF(@aa=a,@num1+1,1) AS pm,@aa:=a AS pm1 FROM test ORDER BY a,b DESC) a WHERE pm=3) c
ON a.a=c.a
GROUP BY a.a;
Query OK, 0 rows affectedQuery OK, 0 rows affected+----+----------+----------+
| a | SUM(a.B) | MAX(c.b) |
+----+----------+----------+
| 2 | 97.0 | 22 |
| 3 | 43.0 | NULL |
| 20 | 44.0 | 1 |
+----+----------+----------+
3 rows in set
看不懂SELECT *,@num1:=IF(@aa=a,@num1+1,1) AS pm,@aa:=a AS pm1 FROM test ORDER BY a,b DESC) a WHERE pm=3,请帮忙解释。谢谢
a b
20 1.0
2 24.0
20 40.0
20 3.0
2 37.0
3 13.0
3 30.0
2 14.0
2 22.0
求第3大值。
SET @num1=0;
SET @aa=0;
SELECT a.a,SUM(a.B),MAX(c.b) FROM test a LEFT JOIN
(
SELECT a,b FROM (
SELECT *,@num1:=IF(@aa=a,@num1+1,1) AS pm,@aa:=a AS pm1 FROM test ORDER BY a,b DESC) a WHERE pm=3) c
ON a.a=c.a
GROUP BY a.a;
Query OK, 0 rows affectedQuery OK, 0 rows affected+----+----------+----------+
| a | SUM(a.B) | MAX(c.b) |
+----+----------+----------+
| 2 | 97.0 | 22 |
| 3 | 43.0 | NULL |
| 20 | 44.0 | 1 |
+----+----------+----------+
3 rows in set
看不懂SELECT *,@num1:=IF(@aa=a,@num1+1,1) AS pm,@aa:=a AS pm1 FROM test ORDER BY a,b DESC) a WHERE pm=3,请帮忙解释。谢谢
@num 是一个SESSION级的用户变量,初始化为 @num1=0; 同样另一个变量@aa=0;
这样,在MYSQL处理记录中的每一行时,都会执行 @num1:=IF(@aa=a,@num1+1,1)
IF(@aa=a,@num1+1,1) 则是判断,@aa 与当前记录的 a是否相同,不相同,则重置 @num1= 1
2 37
2 24
2 22
2 14
3 30
3 13
20 40
20 3
20 1
然后建立一个新列pm1,内容是@aa:=a,也就是建立一个和a列一样的列,并且附值给@aa=pm1列的第一个值(2);再建立一个pm列,内容@num1,也是由IF(@aa=a,@num1+1,1)判断得出结果填入pm列;因为第一行是2,即0<>2,所以由IF(@aa=a,@num1+1,1)判断得出第一行的pm是1,第二行是2,第三行是3,第四行是4,第五行由于3<>2,所以,所以由IF(@aa=a,@num1+1,1)判断得出第六行是1。不知这样理解对不对。