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、产生随机的