【Sql笔记】

1. 窗口函数

rank() : 总数不变,会出现1 1 3情况
row_number() : 排序不会重复,总数不变
dense_rank(): 总数会变少,出现1 1 2情况

order by count(*)

select IFNULL(target, ‘否则’)

IFNULL(select distinct target …)

/--------------------判断连续出现的数↓↓-------------------------------/
SELECT DISTINCT Num AS ConsecutiveNums
FROM
( SELECT Num,
CASE
WHEN @prev = num THEN @COUNT := @COUNT + 1
WHEN ( @prev := num ) THEN @COUNT := 1
END AS CNT
FROM Logs,
( SELECT @prev := NULL,@COUNT := NULL ) AS T
) AS TEMP
WHERE
TEMP.CNT >= 3
/--------------------↑↑-------------------------------------------------/