1、查询第N条记录
limit语句
set global log_bin_trust_function_creators=TRUE;
CREATE FUNCTION getNthHot ( N INT ) RETURNS INT BEGIN
DECLARE
m INT;
SET m = N - 1;
RETURN (SELECT IFNULL(( SELECT DISTINCT hot FROM t_article WHERE hot > 300 ORDER BY hot DESC LIMIT 1, 1 ),1) );
END;
SELECT getNthHot(10)
2、排名连续
// 方法1
SELECT
aa.id,
aa.hot,
@rank := @rank + 1 AS rank
FROM
(
( SELECT id, hot FROM t_article ORDER BY hot DESC ) aa,(
SELECT
@rank := 0
) tt)
// 方法2 mysql 8.0内置函数
SELECT age,ROW_NUMBER() over( order by age desc) as `rank`
FROM `t_user`;
3、排名不连续 如 1,2,2,4
// 方法 1
SELECT
aa.id,
aa.hot,
@rownum := @rownum + 1 AS rownum,
IF
( @hot = aa.hot, @rank, @rank := @rownum ) AS rank,
@hot := aa.hot
FROM
(
( SELECT id, hot FROM t_article ORDER BY hot DESC ) aa,(
SELECT
@rank := 0,
@rownum := 0,
@hot := NULL
) tt)
// 方法2 mysql 8.0内置函数
SELECT age,DENSE_RANK() over( order by age desc) as `rank`
FROM `t_user`;
4、排名不连续 如 1,2,2,3
// 方法1
SELECT
aa.id,
aa.hot,
@rownum := @rownum + 1 AS rownum,
IF
( @hot = aa.hot, @rank, @rank := @rank + 1 ) AS rank,
@hot := aa.hot
FROM
(
( SELECT id, hot FROM t_article ORDER BY hot DESC ) aa,(
SELECT
@rank := 0,
@rownum := 0,
@hot := NULL
) tt)
// 方法2 mysql 8.0内置函数
SELECT age,RANK() over( order by age desc) as `rank`
FROM `t_user`;
5、连续三次出现的年龄
// 方法1
select b.age from(
SELECT
t.age,@cnt:=if(@pre=t.age,@cnt+1,1) cnt,
@pre:=t.age pre
from t_user t,
(SELECT @pre:=null,@cnt:=0) a) b
WHERE b.cnt >= 3
// 方法2 mysql 8.0内置函数
SELECT age,count(age) from
(
SELECT id,age,(RANK() over( order by id asc) - ROW_NUMBER() over(PARTITION by age order by id asc) )as `rank`
FROM `t_user` ) as aa
GROUP BY aa.age,aa.`rank`
HAVING count(age) >= 3
6、部门前三的薪水(包括一样的)
// 方法1
SELECT e1.salary,department.`name` as dept,e1.name as userName
from employee as e1,department
WHERE
e1.department_id = department.id and
3 > (
SELECT count(DISTINCT e2.salary)
from employee as e2
WHERE e1.salary < e2.salary and e1.department_id = e2.department_id
)
order BY department.name,e1.salary desc
// 方法2 mysql 8.0内置函数
SELECT *
from(
SELECT e1.salary,e1.name as userName,DENSE_RANK() over (PARTITION by e1.department_id ORDER BY salary desc) as serial_num,e1.department_id
from employee e1 ) a
WHERE serial_num <= 3
7、删除重复的名字,保留id最小的数据
// 自连接
delete e1
from employee e1,employee e2
WHERE e1.name = e2.name and e1.id > e2.id
// 方法2 mysql 8.0窗口函数
DELETE from employee WHERE id in(
SELECT aa.id from(
SELECT
dense_rank() over (PARTITION by name order by id asc) as row_num,id,name
from employee
) as aa
WHERE aa.row_num > 1
)
// 愚蠢的办法,练练变量怎么用而已
delete from employee WHERE id in(
SELECT id from(
SELECT e1.*,
@row_num:=IF(@pre=e1.name,@row_num+1,1) as rowNumber,
@pre:=e1.name
from employee e1,
(
SELECT @row_num:=0 ,@pre:= null
) as bb
order by e1.name,e1.id) cc WHERE cc.rowNumber> 1
)
8、查询最新的一条数据
SELECT *
from(
SELECT
r1.*,
-- r1.report_id,r1.date_created,
@row_num:=IF(@pre=report_id,@row_num+1,1) as rowNumber,
@pre:=report_id
from t_electronic_vision_report r1,
(select @row_num := 0,@pre =null) as b
WHERE r1.left_logmar is not null
and user_id in ('d49e3b3d107b','a0bb3ed2dba1')
ORDER BY r1.report_id,r1.date_created desc
) c WHERE c.rowNumber = 1
ORDER BY id
9、随机产生数字和字母的字符串
select substring(MD5(RAND()),1,6);
10、产生随机的