select date_format(current_date, '%Y-%m-%d') `日期`, company_name `公司名称`, project_name `项目名称`, ks_id `快手id`, cr.account_id `账户id`, material_name `素材名称`, code `素材唯一编码`, realname `运营人员`, clip_name `设计人员`, case when hc_status = 0 then '待审核' when hc_status = 1 then '审核通过' else '审核拒绝' end `汇创后台素材审核状态`, case when photo_id is null then '未推送' else '推送' end `素材推送状态`, concat('`', photo_id, '`') `视频id`, creative_create_time `素材首次关联创意时间`, creative_num `素材关联创意数`, error_num `审核拒绝创意数` from (select q.*, case when q.creative_create_time is null then 0 else s.creative_count end as creative_num, case when q.creative_create_time is null then 0 else w.creative_count end as error_num from ( select code, material_name, a.status as hc_status, a.create_time as hc_create_time, clip_id, a.account_id, a.photo_id, min(b.creative_create_time) as creative_create_time from (select a.*, b.clip_id, c.account_id, photo_id, c.stat_date as media_video_time from (select code, material_name, status, create_time from `jeecg-boot`.ctop_material_info where create_time > date_sub('2021-10-12', INTERVAL 6 Day)) a left join( select material_id, clip_id from `jeecg-boot`.ctop_material_ascription ) b on a.code = b.material_id left join( select account_id, signature, photo_id, stat_date from ctop_kuaishou_video_get where stat_date >= date_sub('2021-10-12', INTERVAL 6 Day) ) c on a.code = c.signature) a left join (select account_id, creative_id, photo_id, status, creative_create_time from `jeecg-boot`.ctop_kuaishou_creative where creative_create_time >= date_sub('2021-10-12', INTERVAL 6 Day)) b on a.account_id = b.account_id and a.photo_id = b.photo_id group by a.status, material_name, code, a.create_time, clip_id, a.account_id, a.photo_id) q left join ( select code, account_id, case when account_id is null then 0 else c end as creative_count from ( select code, account_id, count(1) as c from ( select code, material_name, a.status as hc_status, a.create_time as hc_create_time, clip_id, a.account_id, a.photo_id, b.creative_id, b.status as creative_status, b.creative_create_time as creative_create_time from (select a.*, b.clip_id, c.account_id, photo_id, c.stat_date as media_video_time from (select code, material_name, status, create_time from `jeecg-boot`.ctop_material_info where create_time > date_sub('2021-10-12', INTERVAL 6 Day)) a left join( select material_id, clip_id from `jeecg-boot`.ctop_material_ascription ) b on a.code = b.material_id left join( select account_id, signature, photo_id, stat_date from ctop_kuaishou_video_get where stat_date >= date_sub('2021-10-12', INTERVAL 6 Day) ) c on a.code = c.signature) a left join (select account_id, creative_id, photo_id, status, creative_create_time from `jeecg-boot`.ctop_kuaishou_creative where creative_create_time >= date_sub('2021-10-12', INTERVAL 6 Day)) b on a.account_id = b.account_id and a.photo_id = b.photo_id) t group by account_id, material_name, hc_status, hc_create_time, clip_id, code, photo_id) t ) s on q.account_id = s.account_id and q.code = s.code left join( select code, account_id, case when account_id is null then 0 else c end as creative_count from ( select code, account_id, material_name, hc_status, hc_create_time, clip_id, photo_id, case when creative_id is null then 0 else count(1) end as c from ( select code, material_name, a.status as hc_status, a.create_time as hc_create_time, clip_id, a.account_id, a.photo_id, b.creative_id, b.status as creative_status, b.creative_create_time as creative_create_time from (select a.*, b.clip_id, c.account_id, photo_id, c.stat_date as media_video_time from (select code, material_name, status, create_time from `jeecg-boot`.ctop_material_info where create_time > date_sub('2021-10-12', INTERVAL 6 Day)) a left join( select material_id, clip_id from `jeecg-boot`.ctop_material_ascription ) b on a.code = b.material_id left join( select account_id, signature, photo_id, stat_date from ctop_kuaishou_video_get where stat_date >= date_sub('2021-10-12', INTERVAL 6 Day) ) c on a.code = c.signature) a left join (select account_id, creative_id, photo_id, status, creative_create_time from `jeecg-boot`.ctop_kuaishou_creative where status = 42 and creative_create_time >= date_sub('2021-10-12', INTERVAL 6 Day)) b on a.account_id = b.account_id and a.photo_id = b.photo_id) t group by account_id, code, material_name, hc_status, hc_create_time, clip_id, photo_id) t ) w on q.account_id = w.account_id and q.code = w.code) cr left join ( select a.account_id, a.project_name, su.realname, c.user_id as ks_id from (select account_id, user_id, user_name, project_name from ctop_user_allocation) a left join (select account_id, user_id from ctop_kuaishou_advertiser_base_info) c on a.account_id = c.account_id left join sys_user su on su.id = a.user_id ) user on cr.account_id = user.account_id left join ( select id, realname as clip_name from sys_user ) clip on cr.clip_id = clip.id left join (select user_id, company_name from user_company) com on cr.clip_id = com.user_id