DesignerInUserCompanyCost.sql 1.2 KB

123456789101112131415161718192021222324252627282930313233343536
  1. select
  2. a.account_id as `账户 `,
  3. signature as `视频md5`,
  4. photo_url as `视频链接`,
  5. video_name as `视频名称`,
  6. project_name `项目名称`,
  7. clip_name `剪辑`,
  8. plan_name `编导`,
  9. shot_name `拍摄`,
  10. leader_name `负责人`,
  11. sum(charge) as `消耗`,
  12. case when channel_type =0 then '内部' when channel_type=1 then '塑造' else '-' end as `渠道`
  13. from (select account_id,
  14. signature,
  15. channel_type,
  16. project_name,
  17. leader_name,
  18. plan_name,
  19. clip_name,
  20. shot_name,
  21. charge,
  22. clip_company_id,
  23. photo_url,
  24. video_name
  25. from application.kuaishou_material_video_report_daily_dw
  26. where stat_date between 20220401 and 20220430) a
  27. inner join (
  28. select account_id
  29. from `jeecg-boot`.ctop_user_allocation
  30. where user_id in (
  31. select user_id from `jeecg-boot`.user_company where company_id = 'd57fecdcf7a94d009736d9c850731582'
  32. )
  33. ) b
  34. on a.account_id = b.account_id
  35. where clip_company_id='1648c7ceb4d449a9a13fbed7a8d9976c'
  36. group by signature, a.account_id