MYSQL-高阶2

count()

统计时是否会计入空值。
count多条件计数，是指满足多条件行的计数，且count统计不计入空值。
## date_add()
此时day 的返回值非数值格式，不可直接运算，需改为数值格式才可运算。比如AVG(day()),max(day()).
date_add()
dateadd()
datesub
datediff()

实例应用

计算每月的天数

查询当月天数

SELECT DISTINCT MONTH( submit_time)mon ,
DAY ( LAST_DAY( submit_time ) ) day,  
DATEDIFF( DATE_ADD( submit_time,INTERVAL 1 MONTH ),submit_time ) day
FROM
exam_record

新填充字段，SQL创建新列

SELECT uid,"activity2" FROM
	examination_info ei
	INNER JOIN exam_record er ON ei.exam_id = er.exam_id
	WHERE YEAR(start_time)="2021" 

新建列 输入需要加入引号“”
MySQL是可以支持直接创建字段值，但是牛客不可以，解决方式在下方。

SELECT DISTINCT MONTH( submit_time ) mon,
	DAY ( LAST_DAY( submit_time ) ) DAY 
FROM
	exam_record UNION
SELECT
	CONCAT(max(submit_time),"汇总"),
	"61"
	from exam_record

SELECT DISTINCT MONTH( submit_time ) mon,
	DAY ( LAST_DAY( submit_time ) ) DAY 
FROM
	exam_record UNION
SELECT
	"汇总",
	"61"

解决方案：
COALESCE()
COALESCE是一个函数，coalesce (expression_1, expression_2, …,expression_n)依次参考各参数表达式，遇到非null值即停止并返回该值。
下图表示当DATE_FORMAT( submit_time, "%Y%m" )为空时，返回2021汇总。

SELECT  COALESCE( DATE_FORMAT( submit_time, "%Y%m" ), "2021汇总" ) AS "新建2021汇总" 
FROM
	exam_record

多条件空值判断：

select coalesce(success_cnt,period,1) from tableA

当success_cnt不为null，那么无论period是否为null，都将返回success_cnt的真实值（因为success_cnt是第一个参数），当success_cnt为null，而period不为null的时候，返回period的真实值。只有当success_cnt和period均为null的时候，将返回1。

with rollup

with在sql语句中定义在group by之后。当需要对数据库数据进行分类统计的时候，往往会用上groupby进行分组。而在groupby后面还可以加入withcube和withrollup等关键字对数据进行汇总。不过这个cube在mysql中并不适用。

使用 WITH ROLLUP，此函数是对聚合函数进行求和，注意 with rollup是对 group by 后的第一个字段，进行分组计算。可以与coalesce函数一起使用。

COALESCE()和WITH ROLLUP 同时使用可直接汇总。

SELECT COALESCE
	( date_format ( submit_time, '%Y%m' ), "2021汇总" ) submit_month,
	count( question_id ) month_q_cnt,
	ROUND( count( question_id ) / max(day(LAST_DAY(submit_time ))), 3 ) avg_day_q_cnt 
FROM
	practice_record 
WHERE
	YEAR ( submit_time ) = "2021" 
GROUP BY
	date_format ( submit_time, "%Y%m" ) WITH ROLLUP

between … and …

between…and… 是指闭区间
eg:[20,24] 包含20，和24。

偏移函数lead（），lag（）

lag(exp_str,offset,defval) over() ：向前偏移N个

• exp_str 是偏移字段
• offset 是 exp_str 字段的偏移量，即 offset 为N ，指的是在表中从当前行位置向前数N行就是我们所要找的那一行了。
• defval是超出临界值以后的默认值，比如向前数n行后超出了表的取值范围，则会返回这个默认值。如果没有设置这个值，则超出后返回值为空。

lead(exp_str,offset,defval) over()：向后偏移N个
first_value(exp_str) over ()
last_value(exp_str) over ()

示例见三次连续：多用于某一列被多次利用