Mysql-常用函数

1.group_concat

select t.name as '姓名',group_concat(t.girlfriend order by t.age separator ';') 
as '女友' from girlfriendTable t  group by t.name

行合并，根据age排序，用分号隔开
相当于oracle的 listagg()

select t.name as '姓名',listagg(t.girlfriend,';') within group (order by t.age) 
over(partition by t.name) from girlfriendTable t 

2.unix_timestamp

(unix_timestamp(t.END_TIME_)-unix_timestamp(t.START_TIME_))
计算相差秒数

3.convert

convert(a.FULL_MSG_ using utf8)
可以将blob类型转换成字符串输出
convert(t.createtime,datetime)
输出2020-02-28 20:10:30

4.分组后排序

row_number() over(partition by t.USER_ID order by t.CREATETIME desc)

SELECT t.USER_ID,
row_number() over(partition by t.USER_ID order by t.CREATETIME desc) rn,
t.createtime,t.LATITUDE,t.LONGITUDE 
FROM locus_manage t

5.时间差，time2-time1相差得星期，天，小时，分，秒

timestampdiff(week,time1,time2)
timestampdiff(day,time1,time2)
timestampdiff(hour,time1,time2)
timestampdiff(minute,time1,time2)
timestampdiff(second,time1,time2)

6.case when

case when now()>t.DEADLINE then 1
when timestampdiff(hour,now(),t.DEADLINE)<24 then 2
else 0
end color