思路: 拿到前 25% 的数据,首先得排序,然后最后的条件还有某个列小于 总体排序后个数的 25%
这个列要随着排的表自动增长,这里使用了 自定义变量@row_num,然后还有 round() 函数,这两样之前都没有接触过. 自定义变量的赋值要用 := 赋值.
因为是 MYSQL, 不支持这种写法:
SELECT TOP 50 PERCENT * from (SELECT * FROM tb_student ORDER BY age DESC) a;
进入正题:
tb_student(sid,NAME,age,sex,mobilephone,address)
SQL:
SELECT student.* ,(@row_num:=@row_num + 1) rid FROM
(SELECT @row_num:=0) t1,
(SELECT * FROM tb_student ORDER BY age DESC) student
WHERE (@row_num)<(SELECT ROUND(COUNT(*)/4) FROM tb_student);
或者这样:
SELECT a.*, @rownum FROM (SELECT @rownum:=0) rid JOIN (SELECT * FROM tb_student ORDER BY age DESC) a
WHERE (@rownum:=@rownum+1)<=(SELECT ROUND(COUNT(*)*0.25) FROM tb_student);
虽然拆开看我都知道什么意思,也能自己写出来,但是 我还是不太明白 它这个自定义变量怎