Mysql:CONCAT() GROUP_CONCAT()

1 CONCAT()

将多个字符串连接成一个字符串

select concat(face_id, action) as info
    from action_log
    where DATE(time) = '2021-03-24';

输出：

CONCAT_WS:指定拼接符拼接字符串

select concat_ws('-', face_id, action) as info
    from action_log
    where DATE(time) = '2021-03-24';

输出

2 GROUP_CONCAT()

查询指定日期下，同一face_id的所有动作

select face_id, group_concat(action) as actions
    from action_log
    where DATE(time) = '2021-03-24'
    group by face_id;

输出

select face_id, group_concat(action,time order by time desc separator ';') as actions
    from action_log
    where DATE(time) = '2021-03-24'
    group by face_id;

拼接action和time并按时间降序排列。指定';'为分隔符。

输出：

2.1 SUBSTRING_INDEX

指定分隔符，将字符串转换为数组，并指定输出的长度

select face_id, substring_index(group_concat(action,time order by time desc separator ';'), ';', 1) as actions
from action_log
where DATE(time) = '2021-03-24'
group by face_id;

substring_index(..., ';', n)要执行的是输出数组的前n个

输出

2.2 SUBSTRING

substring(group_concat(action,time order by time desc separator ';'), 2, 4)

这里是将字符串当成char[]数组来处理，输出char数组的从第2个开始，输出4个。数组下标从1开始。

select face_id, substring(group_concat(action,time order by time desc separator ';'), 2, 4) as actions
    from action_log
    where DATE(time) = '2021-03-24'
    group by face_id;

输出

3 降序去重

select * from (
    select * from action_log 
    where DATE(time) = '2021-03-24' 
    order by time asc
) a group by a.face_id;

输出

这里仅输出了同一face_id下，时间最晚的记录。