mysql去掉两个最高分_MySQL超好用的函数合集（持续更新）

工作中遇到的实际问题，应用比较多，比较好用的函数合集：

1.【GROUP_CONCAT】

①GROUP_CONCAT(列名)：连接列里的所有非 NULL 的字符串，以逗号为默认的连接字符；

②GROUP_CONCAT(DISTINCT 列名 ORDER BY 列名 DESC SEPARATOR '$')：可以用DISTINCT 去掉重复值，可以加入ORDER BY进行排序，还可以用SEPARATOR指定不同的分隔符；

③GROUP_CONCAT(列名1,"$",列名2 SEPARATOR "$")：将多行多列数据，根据指定分隔符连接成一行一列，再根据需要分列，实际应用如下图：

原始表：

应用：

这个例子是此前工作中实际碰到的一个需求(数据已变更)，需要将同个分组下的所有厂家对应的各自最低价，放到同一行。

此时的话就需要先将多行、多列的数据合并成一行一列，在利用分列拆分成一行多列。

注意点:

因为厂家的名字中可能会有“，”，所以分隔符不能使用默认的逗号，需要另外通过”SEPARATOR“进行设置，这样在分列的时候就可以根据新设置的分隔符进行分列，不会造成处理数据时部分数据错乱。

④注意点：最大长度限制默认值1024，当合并的总长度达到1024之后，后面的值就被截断了，可以进行如下设置：

1)SET GLOBAL group_concat_max_len = 102400；

2)SET SESSION group_concat_max_len = 102400。

-- -----以下更新于----- 20200925

2.【排名函数 rank、row_number、dense_rank、NTILE】

①区别：

rank：值相同的归为一组，名次相同，排序不会连续，遇到下一个不同的值，会跳到总的排名；

row_number：排名时序号连续不重复，也就是说，相同的值，也是按照连续数字进行排序，不重复；

dense_rank：值相同的归为一组，名次相同，但排序是连续的，遇到下一个不同的值，也还是按照连续的数字进行排名；

NTILE：把所有需要排名的数值，根据数值大小，平均分成n个组。

②应用：

1)原始数据：NameSeasonPoint

Wade200630

Wade200736

Wade200831

Wade200933

Wade201029

Wade201128

Wade201228

Kobe200635

Kobe200733

Kobe200831

Kobe200930

Kobe201033

Kobe201129

Kobe201228

2)代码：

SELECT

a.`Name`,

a.Season,

a.Point,

rank () over ( PARTITION BY a.`Name` ORDER BY a.Point DESC ) rank1,-- 值相同的归为一组，排序不会连续row_number () over ( PARTITION BY a.`Name` ORDER BY a.Point DESC ) row_number2,-- 排名时序号连续不重复dense_rank () over ( PARTITION BY a.`Name` ORDER BY a.Point DESC ) dense_rank3,-- 值相同的归为一组，排序是连续的NTILE ( 2 ) over ( PARTITION BY a.`Name` ORDER BY a.Point DESC ) NTILE4 -- 把所有记录分成n个组，每组序号一样

FROM

`point` a

ORDER BY

a.`Name` DESC,

a.Point DESC

3)结果：

从上图的结果中，可以明显看出这几个排名函数的具体区别，根据需求选用合适的即可。

3.【聚合函数与OVER PARTITION BY相结合的妙用】

1)需求：根据上图结果新增一列，判断哪个赛季是最高分赛季，哪个赛季是最低分赛季，哪个赛季是其他赛季，分别打上标签；

2)思考：如果用CASE WHEN 排名= 1 ---> '最高分赛季'，看起来好像没毛病，但是如果每个分组的个数是不定的，不相同的，没法确定最末尾的数字是多少(此例子比较特殊，个数都是7，但如果用的是rank或者dense_rank，排名尾数也是不一样的，也是没办法用CASE WHEN 来批量判断排名要等于多少才是对应的最低分赛季)，因此就需要用到聚合函数与OVER PARTITION BY相结合；

3)代码：

WITH `RANK` AS (

SELECT

a.`Name`,

a.Season,

a.Point,

rank () over ( PARTITION BY a.`Name` ORDER BY a.Point DESC ) rank_no

FROM

`point` a

ORDER BY

a.`Name` DESC,

a.Season ASC

) SELECT

a.`Name`,

a.Season,

a.Point,

CASE

WHEN a.rank_no = MIN( a.rank_no ) over ( PARTITION BY a.`Name` ) THEN

'最高分赛季'

WHEN a.rank_no = MAX( a.rank_no ) over ( PARTITION BY a.`Name` ) THEN

'最低分赛季' ELSE '其他赛季'

END 备注

FROM

`RANK` a

4)结果：

5)Tips：第三小点的代码中有用到WITH Table Name AS (代码块)，这个就是比较常应用于，当你的这段代码块需要重复使用的时候，需要利用他关联好几个地方，关联好几个表，就可以使用这个语句，避免过多的子查询，用一个表名即可解决，让代码看起来比较简洁，排查定位问题也比较精准。

-- -----以下更新于-----20200926

4.【日期时间函数】

①DATE_ADD(date,INTERVAL expr unit)

1)解释：将日期加上一个指定的间隔时间

date：合法的日期；

expr：时间间隔(可正可负，负的时候相当于date_sub()函数)；

unit：用的比较多的就是HOUR、DAY、WEEK、MONTH、QUARTER、YEAR。

2)代码：

SELECT DATE_ADD(NOW(),INTERVAL 1 DAY) tomorrow -- 明天此时-- tomorrow-- 2020-09-27 17:01:29SELECT DATE_ADD(NOW(),INTERVAL -1 DAY) yesterday -- 昨天此时-- yesterday-- 2020-09-25 17:01:51

--------------------------------------------

②DATE_SUB(date,INTERVAL expr unit)

与date_add同样的用法，当expr为正数时，add是加，sub是减；当expr为负数时，add是减，sub是加。

③DATEDIFF(expr1,expr2)

1)解释：返回两个日期之间的天数(date1 - date2)

2)代码：

SELECT DATEDIFF('2020-12-31',NOW()) 距今年结束还有多少天

-- 距今年结束还有多少天-- 96

--------------------------------------------

④TIMEDIFF(expr1,expr2)

1)解释：返回两个时间之间的时间差(time1 - time2)

2)代码：

SELECT TIMEDIFF('2020-12-31 23:59:59',NOW()) 距今年结束还多久

-- 距今年结束还多久-- 838:59:59

--------------------------------------------

接下来这个时间函数应用是最多的！！！！！因为他功能比较强大。

⑤TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)

1)解释：返回两个日期之间的数值(datetime_expr2 - datetime_expr1)

unit：是类型，返回可以是DAY、WEEK、MONTH、QUARTER、YEAR

2)代码：

SELECT TIMESTAMPDIFF(DAY,NOW(),'2021-12-31 23:59:59') 距明年结束还几天;

-- 距明年结束还几天-- 461SELECT TIMESTAMPDIFF(WEEK,NOW(),'2021-12-31 23:59:59') 距明年结束还几周;

-- 距明年结束还几周-- 65SELECT TIMESTAMPDIFF(MONTH,NOW(),'2021-12-31 23:59:59') 距明年结束还几月;

-- 距明年结束还几月-- 15SELECT TIMESTAMPDIFF(QUARTER,NOW(),'2021-12-31 23:59:59') 距明年结束还几季度;

-- 距明年结束还几季度-- 5SELECT TIMESTAMPDIFF(YEAR,NOW(),'2021-12-31 23:59:59') 距明年结束还几年

-- 距明年结束还几年-- 1

--------------------------------------------

⑥TIMESTAMPADD(unit,interval,datetime_expr)

1)解释：相当于date_add()函数

2)代码：

SELECT TIMESTAMPADD(DAY,1,NOW()) tomorrow

-- tomorrow-- 2020-09-27 17:14:06