material_info.sql 19 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362
  1. #1.头条-全量素材基本信息表
  2. #创建表
  3. CREATE TABLE `app_bytedance_material_info` (
  4. `material_id` varchar(50) NOT NULL COMMENT '素材id',
  5. `company_id` varchar(50) DEFAULT NULL COMMENT '分公司id',
  6. `company_name` varchar(100) DEFAULT NULL COMMENT '分公司名称',
  7. `help_time` bigint(20) DEFAULT NULL COMMENT '上传助手时间',
  8. `media_time` bigint(20) DEFAULT NULL COMMENT '上传媒体时间',
  9. `confirm_time` timestamp NULL DEFAULT NULL COMMENT '通过审核时间',
  10. `farst_cost_time` bigint(20) DEFAULT NULL COMMENT '首次消耗时间',
  11. `channel_type` bigint(20) DEFAULT NULL COMMENT '素材渠道',
  12. `material_type` bigint(20) DEFAULT NULL COMMENT '视频类型',
  13. `material_name` varchar(1000) DEFAULT NULL COMMENT '视频名称',
  14. `video_url` varchar(1000) DEFAULT NULL COMMENT '视频链接',
  15. `cover_url` varchar(1000) DEFAULT NULL COMMENT '首页链接',
  16. `material_innovate` bigint(20) DEFAULT NULL COMMENT '是否创新素材',
  17. `status` bigint(20) DEFAULT NULL COMMENT '当日审核状态',
  18. `sync_kuaishou` bigint(20) DEFAULT NULL COMMENT '是否同步快手',
  19. `sync_bytedance` bigint(20) DEFAULT NULL COMMENT '是否同步头条',
  20. `effect_flag` bigint(20) DEFAULT NULL COMMENT '是否有效素材',
  21. `effect_date` bigint(20) DEFAULT NULL COMMENT '有效时间',
  22. `faddish_flag` bigint(20) DEFAULT NULL COMMENT '是否爆款素材',
  23. `faddish_date` bigint(20) DEFAULT NULL COMMENT '爆款时间',
  24. `auditor_id` varchar(100) DEFAULT NULL COMMENT '审核人',
  25. `auditor_name` varchar(100) DEFAULT NULL COMMENT '审核人名称',
  26. `dept_id` varchar(100) DEFAULT NULL COMMENT '设计部门id',
  27. `dept_name` varchar(100) DEFAULT NULL COMMENT '设计部门名称',
  28. `clip_id` varchar(100) DEFAULT NULL COMMENT '剪辑id',
  29. `clip_name` varchar(100) DEFAULT NULL COMMENT '剪辑名称',
  30. `shot_id` varchar(100) DEFAULT NULL COMMENT '拍摄',
  31. `shot_name` varchar(100) DEFAULT NULL COMMENT '拍摄名称',
  32. `plan_id` varchar(100) DEFAULT NULL COMMENT '策划id',
  33. `plan_name` varchar(100) DEFAULT NULL COMMENT '策划名称',
  34. `leader_id` varchar(100) DEFAULT NULL COMMENT '设计负责人id',
  35. `leader_name` varchar(100) DEFAULT NULL COMMENT '设计负责人名称',
  36. `data_create_time` timestamp DEFAULT CURRENT_TIMESTAMP,
  37. `data_update_time` timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  38. PRIMARY KEY (`material_id`) /*T![clustered_index] NONCLUSTERED */
  39. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='头条-素材基本信息(日新)';
  40. #插入头条素材明细数据(前置sql)
  41. REPLACE INTO application.`app_bytedance_material_info` (material_id, company_id, company_name, help_time, media_time, confirm_time, farst_cost_time, channel_type, material_type, material_name, video_url, cover_url, material_innovate, status, sync_kuaishou, sync_bytedance, effect_flag, effect_date, faddish_flag, faddish_date, auditor_id, auditor_name, dept_id, dept_name, clip_id, clip_name, shot_id, shot_name, plan_id, plan_name, leader_id, leader_name)
  42. select a.signature,com.company_id,
  43. (CASE
  44. WHEN com.company_id = 'd57fecdcf7a94d009736d9c850731582' THEN
  45. '北京汇创思拓数字科技有限公司'
  46. WHEN com.company_id = '6608ed13c0dd42de93c790dbdf124234' THEN
  47. '广州汇创思拓数字科技有限公司'
  48. WHEN com.company_id = '4b10089775c040119e139087517aed88' THEN
  49. '上海汇创思拓数字科技有限公司'
  50. WHEN com.company_id = '5e46b5dea3d740eeb8ff1a2895015a4b' THEN
  51. '北京骄阳数字科技有限公司'
  52. WHEN com.company_id = '1648c7ceb4d449a9a13fbed7a8d9976c' THEN
  53. '杭州妙构思数字科技有限公司'
  54. WHEN com.company_id = '3321d5893bcd430cab1238a99bc7db11' THEN
  55. '北京次元像素'
  56. else com.company_name
  57. END) company_name,
  58. date_format(b.create_time,'%Y%m%d') create_time,date_format(c.stat_date,'%Y%m%d') stat_date,d.confirm_time,
  59. a.frast_cost_time,0 channel_type,c.material_type,
  60. CASE WHEN d.material_name IS NOT NULL THEN d.material_name
  61. ELSE c.photo_name END AS material_name,
  62. c.video_url,d.cover_url cover_url,d.material_innovate,d.status,
  63. d.sync_kuaishou,d.sync_bytedance,
  64. case when eff.effect_date is not null then 1 else 0 end effect_flag,
  65. case when eff.effect_date is not null then eff.effect_date else 0 end effect_date,
  66. case when hot.hot_date is not null then 1 else 0 end faddish_flag,
  67. case when hot.hot_date is not null then hot.hot_date else 0 end faddish_date,
  68. d.auditor_id,audi.realname auditor_name,ur.org_code dept_id,ur.depart_name dept_name,b.clip_id,userd.realname clip_name,
  69. b.shot_id,shot.realname shot_name,b.plan_id,plan.realname plan_name,b.leader_id,leader.realname leader_name
  70. from application.bytedance_material_farst_cost_time a
  71. left join (select material_id,create_time,clip_id,shot_id,plan_id,leader_id from (
  72. select material_id,create_time,clip_id,shot_id,plan_id,leader_id,row_number() over (partition by material_id order by create_time ) rn
  73. from `jeecg-boot`.ctop_material_ascription) er where rn=1) b
  74. on a.signature=b.material_id
  75. left join
  76. (select * from
  77. (select signature,
  78. case when size>height then 2
  79. when size<height then 1
  80. else 0 end material_type,video_url,date_format(create_time,'%Y%m%d') stat_date ,filename photo_name,row_number() over(partition by signature order by create_time) rm from `media_api`.bytedance_file_video_get
  81. where date_format(create_time,'%Y%m%d')>= date_format(date_sub(${stat_date}, INTERVAL 13 DAY),'%Y%m%d')) m where rm=1) c
  82. on a.signature=c.signature
  83. left join
  84. (select * from
  85. (select code,confirm_time,auditor_id,user_id,material_innovate,status,sync_kuaishou,sync_bytedance,material_name,cover_url,row_number() over (partition by code order by confirm_time) rm
  86. from `jeecg-boot`.ctop_material_info ) v
  87. where rm=1) d
  88. on a.signature=d.code
  89. left join `jeecg-boot`.user_company com on b.clip_id=com.user_id
  90. left join `jeecg-boot`.sys_user audi on d.auditor_id=audi.id
  91. left join `jeecg-boot`.sys_user userd on b.clip_id=userd.id
  92. left join `jeecg-boot`.sys_user shot on b.shot_id=shot.id
  93. left join `jeecg-boot`.sys_user plan on b.plan_id=plan.id
  94. left join `jeecg-boot`.sys_user leader on b.leader_id=leader.id
  95. LEFT JOIN `jeecg-boot`.sys_user_depart de ON b.leader_id = de.user_id
  96. LEFT JOIN `jeecg-boot`.sys_depart ur ON de.dep_id = ur.id
  97. left join application.effect_material eff
  98. on a.signature=eff.signature
  99. left join
  100. application.hot_material hot
  101. on a.signature=hot.signature
  102. where a.frast_cost_time=${stat_date};
  103. #更新头条素材有效和爆款(后置sql)
  104. replace into application.`app_bytedance_material_info` (material_id, company_id, company_name, help_time, media_time, confirm_time, farst_cost_time, channel_type, material_type, material_name, video_url, cover_url, material_innovate, status, sync_kuaishou, sync_bytedance, effect_flag, effect_date, faddish_flag, faddish_date, auditor_id, auditor_name, dept_id, dept_name, clip_id, clip_name, shot_id, shot_name, plan_id, plan_name, leader_id, leader_name)
  105. select
  106. new.material_id,
  107. new.company_id,
  108. new.company_name,
  109. new.help_time,
  110. new.media_time,
  111. new.confirm_time,
  112. new.farst_cost_time,
  113. new.channel_type,
  114. new.material_type,
  115. new.material_name,
  116. new.video_url,
  117. new.cover_url,
  118. new.material_innovate,
  119. new.status,
  120. new.sync_kuaishou,
  121. new.sync_bytedance,
  122. case when eff.signature is not null then 1 else 0 end effect_flag,
  123. case when eff.signature is not null then eff.effect_date else 0 end effect_date,
  124. case when hot.signature is not null then 1 else 0 end faddish_flag,
  125. case when hot.signature is not null then hot.hot_date else 0 end faddish_date,
  126. new.auditor_id,
  127. new.auditor_name,
  128. new.dept_id,
  129. new.dept_name,
  130. new.clip_id,
  131. new.clip_name,
  132. new.shot_id,
  133. new.shot_name,
  134. new.plan_id,
  135. new.plan_name,
  136. new.leader_id,
  137. new.leader_name
  138. from application.`app_bytedance_material_info` new
  139. left JOIN
  140. (select signature,effect_date from application.effect_material where media_id=1) eff
  141. on new.material_id=eff.signature
  142. left join
  143. (select signature,hot_date from application.hot_material where media_id=1) hot
  144. on new.material_id=hot.signature
  145. where new.farst_cost_time>=date_sub(${stat_date}, INTERVAL 20 DAY);
  146. #2.快手-全量素材基本信息表
  147. #创建表
  148. CREATE TABLE `app_kuaishou_material_info` (
  149. `material_id` varchar(50) NOT NULL COMMENT '素材id',
  150. `company_id` varchar(50) DEFAULT NULL COMMENT '分公司id',
  151. `company_name` varchar(100) DEFAULT NULL COMMENT '分公司名称',
  152. `help_time` bigint(20) DEFAULT NULL COMMENT '上传助手时间',
  153. `media_time` bigint(20) DEFAULT NULL COMMENT '上传媒体时间',
  154. `confirm_time` timestamp NULL DEFAULT NULL COMMENT '通过审核时间',
  155. `farst_cost_time` bigint(20) DEFAULT NULL COMMENT '首次消耗时间',
  156. `channel_type` bigint(20) DEFAULT NULL COMMENT '素材渠道',
  157. `material_type` bigint(20) DEFAULT NULL COMMENT '视频类型',
  158. `material_name` varchar(1000) DEFAULT NULL COMMENT '视频名称',
  159. `video_url` varchar(1000) DEFAULT NULL COMMENT '视频链接',
  160. `cover_url` varchar(1000) DEFAULT NULL COMMENT '首页链接',
  161. `material_innovate` bigint(20) DEFAULT NULL COMMENT '是否创新素材',
  162. `status` bigint(20) DEFAULT NULL COMMENT '当日审核状态',
  163. `sync_kuaishou` bigint(20) DEFAULT NULL COMMENT '是否同步快手',
  164. `sync_bytedance` bigint(20) DEFAULT NULL COMMENT '是否同步头条',
  165. `effect_flag` bigint(20) DEFAULT NULL COMMENT '是否有效素材',
  166. `effect_date` bigint(20) DEFAULT NULL COMMENT '有效时间',
  167. `faddish_flag` bigint(20) DEFAULT NULL COMMENT '是否爆款素材',
  168. `faddish_date` bigint(20) DEFAULT NULL COMMENT '爆款时间',
  169. `auditor_id` varchar(100) DEFAULT NULL COMMENT '审核人',
  170. `auditor_name` varchar(100) DEFAULT NULL COMMENT '审核人名称',
  171. `dept_id` varchar(100) DEFAULT NULL COMMENT '设计部门id',
  172. `dept_name` varchar(100) DEFAULT NULL COMMENT '设计部门名称',
  173. `clip_id` varchar(100) DEFAULT NULL COMMENT '剪辑id',
  174. `clip_name` varchar(100) DEFAULT NULL COMMENT '剪辑名称',
  175. `shot_id` varchar(100) DEFAULT NULL COMMENT '拍摄',
  176. `shot_name` varchar(100) DEFAULT NULL COMMENT '拍摄名称',
  177. `plan_id` varchar(100) DEFAULT NULL COMMENT '策划id',
  178. `plan_name` varchar(100) DEFAULT NULL COMMENT '策划名称',
  179. `leader_id` varchar(100) DEFAULT NULL COMMENT '设计负责人id',
  180. `leader_name` varchar(100) DEFAULT NULL COMMENT '设计负责人名称',
  181. `data_create_time` timestamp DEFAULT CURRENT_TIMESTAMP,
  182. `data_update_time` timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  183. PRIMARY KEY (`material_id`) /*T![clustered_index] NONCLUSTERED */
  184. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='快手-素材基本信息(日新)';
  185. ##插入快手素材明细数据(前置sql)
  186. replace into application.`app_kuaishou_material_info` (material_id, company_id, company_name, help_time, media_time, confirm_time, farst_cost_time, channel_type, material_type, material_name, video_url, cover_url, material_innovate, status, sync_kuaishou, sync_bytedance, effect_flag, effect_date, faddish_flag, faddish_date, auditor_id, auditor_name, dept_id, dept_name, clip_id, clip_name, shot_id, shot_name, plan_id, plan_name, leader_id, leader_name)
  187. SELECT
  188. a.signature,
  189. com.company_id,
  190. (
  191. CASE
  192. WHEN com.company_id = 'd57fecdcf7a94d009736d9c850731582' THEN
  193. '北京汇创思拓数字科技有限公司'
  194. WHEN com.company_id = '6608ed13c0dd42de93c790dbdf124234' THEN
  195. '广州汇创思拓数字科技有限公司'
  196. WHEN com.company_id = '4b10089775c040119e139087517aed88' THEN
  197. '上海汇创思拓数字科技有限公司'
  198. WHEN com.company_id = '5e46b5dea3d740eeb8ff1a2895015a4b' THEN
  199. '北京骄阳数字科技有限公司'
  200. WHEN com.company_id = '1648c7ceb4d449a9a13fbed7a8d9976c' THEN
  201. '杭州妙构思数字科技有限公司'
  202. WHEN com.company_id = '3321d5893bcd430cab1238a99bc7db11' THEN
  203. '北京次元像素'
  204. ELSE
  205. com.company_name
  206. END
  207. ) company_name,
  208. date_format(b.create_time, '%Y%m%d') help_time,
  209. date_format(c.stat_date, '%Y%m%d') media_time,
  210. d.confirm_time,
  211. a.frast_cost_time,
  212. c.channel_type,
  213. c.material_type,
  214. CASE WHEN d.material_name IS NOT NULL THEN d.material_name
  215. ELSE c.photo_name END AS material_name,
  216. c.url,
  217. c.cover_url,
  218. d.material_innovate,
  219. d.STATUS,
  220. d.sync_kuaishou,
  221. d.sync_bytedance,
  222. 0 effect_flag,
  223. 0 effect_date,
  224. 0 faddish_flag,
  225. 0 faddish_date,
  226. d.auditor_id,
  227. audi.realname auditor_name,
  228. ur.org_code dept_id,
  229. ur.depart_name dept_name,
  230. b.clip_id,
  231. userd.realname clip_name,
  232. b.shot_id,
  233. shot.realname shot_name,
  234. b.plan_id,
  235. plan.realname plan_name,
  236. b.leader_id,
  237. leader.realname leader_name
  238. FROM application.material_farst_cost_time a
  239. LEFT JOIN (select material_id,create_time,clip_id,shot_id,plan_id,leader_id from (
  240. select material_id,create_time,clip_id,shot_id,plan_id,leader_id,row_number() over (partition by material_id order by create_time ) rn
  241. from `jeecg-boot`.ctop_material_ascription) er where rn=1) b
  242. ON a.signature = b.material_id
  243. LEFT JOIN (select * from (
  244. SELECT signature,channel_type,material_type,url,cover_url,stat_date,photo_name,row_number() over(partition by signature order by stat_date) rm FROM `media_api`.kuaishou_video_list
  245. where date_format(stat_date,'%Y%m%d')>= date_format(date_sub(${stat_date}, INTERVAL 13 DAY),'%Y%m%d'))m where rm=1) c ON a.signature = c.signature
  246. LEFT JOIN
  247. (select code,confirm_time,auditor_id,user_id,material_innovate,status,sync_kuaishou,sync_bytedance,material_name from (
  248. select code,confirm_time,auditor_id,user_id,material_innovate,status,sync_kuaishou,sync_bytedance,material_name,row_number() over (partition by code order by confirm_time) rm
  249. from `jeecg-boot`.ctop_material_info ) v
  250. where rm=1) d
  251. ON a.signature = d.CODE
  252. LEFT JOIN `jeecg-boot`.user_company com ON b.clip_id = com.user_id
  253. LEFT JOIN `jeecg-boot`.sys_user audi ON d.auditor_id = audi.id
  254. LEFT JOIN `jeecg-boot`.sys_user userd ON b.clip_id = userd.id
  255. LEFT JOIN `jeecg-boot`.sys_user shot ON b.shot_id = shot.id
  256. LEFT JOIN `jeecg-boot`.sys_user plan ON b.plan_id = plan.id
  257. LEFT JOIN `jeecg-boot`.sys_user leader ON b.plan_id = leader.id
  258. LEFT JOIN `jeecg-boot`.sys_user_depart de ON b.leader_id = de.user_id
  259. LEFT JOIN `jeecg-boot`.sys_depart ur ON de.dep_id = ur.id
  260. where a.frast_cost_time=${stat_date};
  261. #更新快手素材有效和爆款(后置sql)
  262. replace into application.`app_kuaishou_material_info` (material_id, company_id, company_name, help_time, media_time, confirm_time, farst_cost_time, channel_type, material_type, material_name, video_url, cover_url, material_innovate, status, sync_kuaishou, sync_bytedance, effect_flag, effect_date, faddish_flag, faddish_date, auditor_id, auditor_name, dept_id, dept_name, clip_id, clip_name, shot_id, shot_name, plan_id, plan_name, leader_id, leader_name)
  263. select
  264. new.material_id,
  265. new.company_id,
  266. new.company_name,
  267. new.help_time,
  268. new.media_time,
  269. new.confirm_time,
  270. new.farst_cost_time,
  271. new.channel_type,
  272. new.material_type,
  273. new.material_name,
  274. new.video_url,
  275. new.cover_url,
  276. new.material_innovate,
  277. new.status,
  278. new.sync_kuaishou,
  279. new.sync_bytedance,
  280. case when eff.signature is not null then 1 else 0 end effect_flag,
  281. case when eff.signature is not null then eff.effect_date else 0 end effect_date,
  282. case when hot.signature is not null then 1 else 0 end faddish_flag,
  283. case when hot.signature is not null then hot.hot_date else 0 end faddish_date,
  284. new.auditor_id,
  285. new.auditor_name,
  286. new.dept_id,
  287. new.dept_name,
  288. new.clip_id,
  289. new.clip_name,
  290. new.shot_id,
  291. new.shot_name,
  292. new.plan_id,
  293. new.plan_name,
  294. new.leader_id,
  295. new.leader_name
  296. from application.`app_kuaishou_material_info` new
  297. LEFT JOIN
  298. (select signature,effect_date from application.effect_material where media_id=2) eff
  299. on new.material_id=eff.signature
  300. left join
  301. (select signature,hot_date from application.hot_material where media_id=2) hot
  302. on new.material_id=hot.signature
  303. where new.farst_cost_time>=date_sub(${stat_date}, INTERVAL 20 DAY);
  304. #3.素材标签基本信息表(日更新)
  305. #创建表
  306. CREATE TABLE `app_material_tag_busi_info` (
  307. `material_id` varchar(100) NOT NULL COMMENT '素材id',
  308. `company_id` varchar(50) DEFAULT NULL COMMENT '分公司id',
  309. `company_name` varchar(100) DEFAULT NULL COMMENT '分公司名称',
  310. `create_date` bigint(20) DEFAULT NULL COMMENT '创建时间',
  311. `updt_date` bigint(20) DEFAULT NULL COMMENT '更新时间',
  312. `leader_id` varchar(100) DEFAULT NULL COMMENT '设计id',
  313. `leader_name` varchar(100) DEFAULT NULL COMMENT '设计负责人名称',
  314. `tag_category_id` varchar(100) DEFAULT NULL COMMENT '一级标签',
  315. `tag_category_name` varchar(100) DEFAULT NULL COMMENT '一级标签名称',
  316. `parent_id` varchar(100) DEFAULT NULL COMMENT '二级标签',
  317. `parent_name` varchar(100) DEFAULT NULL COMMENT '二级标签名称',
  318. `tag_code` varchar(100) NOT NULL COMMENT '三级标签',
  319. `tag_name` varchar(100) DEFAULT NULL COMMENT '三级标签名称',
  320. PRIMARY KEY (`material_id`,`tag_code`) /*T![clustered_index] NONCLUSTERED */
  321. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='素材标签明细(新)';
  322. #插入数据
  323. REPLACE INTO application.`app_material_tag_busi_info`
  324. select m.material_id,m.company_id,m.company_name,m.create_date,m.updt_date,m.leader_id,m.leader_name,m.tag_category_id,m.tag_category_name,m.parent_id,m.parent_name,
  325. ifnull(m.tag_code,0) tag_code,m.tag_name from
  326. (select a.code material_id,com.company_id,
  327. (CASE
  328. WHEN com.company_id = 'd57fecdcf7a94d009736d9c850731582' THEN
  329. '北京汇创思拓数字科技有限公司'
  330. WHEN com.company_id = '6608ed13c0dd42de93c790dbdf124234' THEN
  331. '广州汇创思拓数字科技有限公司'
  332. WHEN com.company_id = '4b10089775c040119e139087517aed88' THEN
  333. '上海汇创思拓数字科技有限公司'
  334. WHEN com.company_id = '5e46b5dea3d740eeb8ff1a2895015a4b' THEN
  335. '北京骄阳数字科技有限公司'
  336. WHEN com.company_id = '1648c7ceb4d449a9a13fbed7a8d9976c' THEN
  337. '杭州妙构思数字科技有限公司'
  338. WHEN com.company_id = '3321d5893bcd430cab1238a99bc7db11' THEN
  339. '北京次元像素'
  340. else com.company_name
  341. END) company_name,
  342. date_format(a.create_time,'%Y%m%d') create_date,date_format(a.update_time,'%Y%m%d') updt_date,c.leader_id,us.realname leader_name,b.tag_category_id,b.tag_category_name,b.parent_id,b.parent_name,b.tag_code,b.tag_name
  343. from `jeecg-boot`.ctop_material_tag_info a
  344. left join
  345. (select tag_category_id,tag_category_name,
  346. case when parent_id is not null then parent_id
  347. when tag_category_id=2 then '' else tag_code end parent_id,
  348. case when parent_id=60155 then '真人'
  349. when parent_id=60156 then '制作'
  350. when parent_id=60160 then '外拍场景'
  351. when parent_id=60160 then '公司内景'
  352. when tag_category_id=2 then '' else '其他' end parent_name,
  353. tag_code,tag_name
  354. from `jeecg-boot`.ctop_tag_info )b
  355. on a.tag_code=b.tag_code
  356. inner join `jeecg-boot`.ctop_material_ascription c
  357. on a.code=c.material_id
  358. left join `jeecg-boot`.user_company com on c.clip_id=com.user_id
  359. left join `jeecg-boot`.sys_user us on c.leader_id=us.id
  360. WHERE date_format(a.create_time,'%Y%m%d') between date_sub(${stat_date},INTERVAL 4 DAY) AND ${stat_date}) m;