加载中…mysql单列拼接，groupby多个字段使用场景
(2019-01-24 14:47:03)| 分类： mysql |
假设表字段为
时间
用户id 阅读文章id
2019-01-20 00:00:01
1
A
2019-01-20 00:00:02
1
A
2019-01-20 00:00:03
1
A
2019-01-20 00:00:01 2
A
2019-01-20 00:00:01 2
C
2019-01-20 00:00:01 1
C
2019-01-21 00:00:01
1
A
2019-01-21 00:00:02
1
A
2019-01-21 00:00:01 2
A
2019-01-21 00:00:01 2
C
2019-01-21 00:00:01 1
C
2019-01-22 00:00:01 1
A
2019-01-22 00:00:01 2
A
2019-01-22 00:00:01 1
C
以下语句表示用户3天都读了n篇或以上相同的文章，contentids为相同的文章id用逗号连接（单列连接），group
by两个字段是为了排重，表示某用户读了某篇文章：
select
count(a.contentid) repeat_news,GROUP_CONCAT(a.contentid)
contentids,a.userid
from
(
select userid,contentid from p_user_income_record where time
between "2019-01-20 00:00:00" and "2019-01-20 23:59:59" group by
userid,contentid
) a
inner join (select userid,contentid from p_user_income_record where time between "2019-01-21 00:00:00" and "2019-01-21 23:59:59" group by userid,contentid
) b
on a.userid=b.userid and a.contentid=b.contentid
inner join (select userid,contentid from p_user_income_record where time between "2019-01-22 00:00:00" and "2019-01-22 23:59:59" group by userid,contentid
) c
on a.userid=c.userid and a.contentid=c.contentid
group by a.userid having repeat_news>=n
当n=1时结果为
repeat_news contentids userid
2
A,C
1
1
A
2
当n=2时结果为
repeat_news contentids userid
2
A,C
1
喜欢
0
赠金笔
前一篇：mysql删除事务锁
后一篇：截取ip前3段