题解 #国庆期间每类视频点赞量和转发量#不会复杂操作怎么办

(select
tag,
'2021-10-01' as dt,
sum(c.sum_li) as sum_like_cnt_7d,
max(sum_re) as max_retweet_cnt_7d
from 
(select
tag,
sum(a.if_like) as sum_li,
sum(a.if_retweet) as sum_re
from tb_user_video_log a left join tb_video_info b on a.video_id=b.video_id
where datediff('2021-10-01',start_time)<=6 and datediff('2021-10-01',start_time)>=0
group by tag,left(start_time,10)
) as c
group by tag

union
select
tag,
'2021-10-02' as dt,
sum(c.sum_li) as sum_like_cnt_7d,
max(sum_re) as max_retweet_cnt_7d
from 
(select
tag,
sum(a.if_like) as sum_li,
sum(a.if_retweet) as sum_re
from tb_user_video_log a left join tb_video_info b on a.video_id=b.video_id
where datediff('2021-10-02',start_time)<=6 and datediff('2021-10-02',start_time)>=0
group by tag,left(start_time,10)
) as c
group by tag

union 

select
c.tag,
'2021-10-03' as dt,
sum(c.sum_li) as sum_like_cnt_7d,
max(sum_re) as max_retweet_cnt_7d
from 
(select
tag,
sum(a.if_like) as sum_li,
sum(a.if_retweet) as sum_re
from tb_user_video_log a left join tb_video_info b on a.video_id=b.video_id
where datediff('2021-10-03',start_time)<=6 and datediff('2021-10-03',start_time)>=0
group by tag,left(start_time,10)
) as c
group by tag
)
order by tag desc,dt

三天的不会一起取，突然想起来之前有学过union，就用最笨的方法将10-1~10-3三天的分别取出来，之后用union合并起来。但该方法只适合我这种菜鸡，如果日期多了如7天则非常麻烦，所以还是好好学一下复杂操作吧！