转户.sql 1.5 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475
  1. SELECT
  2. (
  3. SELECT
  4. CASE
  5. WHEN media_id = 1 THEN
  6. '头条'
  7. WHEN media_id = 2 THEN
  8. '快手'
  9. WHEN media_id = 3 THEN
  10. '头条内广'
  11. ELSE
  12. ''
  13. END
  14. FROM
  15. ctop_project
  16. WHERE
  17. id = t1.project_id
  18. ) AS '媒体',
  19. (
  20. SELECT
  21. project_name
  22. FROM
  23. ctop_project
  24. WHERE
  25. id = t1.project_id
  26. ) AS '项目',
  27. CONCAT('`',t1.account_id,'`' ) AS '账户id',
  28. (
  29. SELECT
  30. realname
  31. FROM
  32. sys_user
  33. WHERE
  34. id = t1.transferor
  35. ) AS '转户人',
  36. (
  37. SELECT
  38. realname
  39. FROM
  40. sys_user
  41. WHERE
  42. id = t1.assignee
  43. ) AS '受让人',
  44. t1.transferor_date AS '转户人绩效统计截止日期',
  45. t1.assignee_date AS '受让人绩效统计开始日期'
  46. FROM
  47. ctop_transfer_account t1
  48. WHERE
  49. t1.transferor_date >= '2022-01-01'
  50. AND t1.transferor_date <= '2022-01-31'
  51. and t1.transferor in(
  52. SELECT user_id from user_company WHERE company_id = '4b10089775c040119e139087517aed88'
  53. )
  54. ORDER BY
  55. t1.account_id
  56. select company_name,
  57. project_name,
  58. user.realname,
  59. leader.realname,
  60. concat('`',account_id,'`') as account_id,
  61. account_name,
  62. charge
  63. from (select company_name, project_name, user_id, leader_id, account_id, account_name, charge
  64. from application.app_kuaishou_account_report_busi_m
  65. where stat_mon = 202206
  66. and company_id = '4b10089775c040119e139087517aed88') a
  67. left join `jeecg-boot`.sys_user user
  68. on a.user_id = user.id
  69. left join `jeecg-boot`.sys_user leader
  70. on a.leader_id = leader.id