10报警订单.sql 4.2 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384
  1. -- Active: 1732070527111@@192.168.110.85@3306@xiaoding_test
  2. /*
  3. 订单编号是coach_alerts表的order_id,用户名称是coach_alerts表通过order_id关联order表查询出的user_id通过order表的user_id关联member_users表查询出nickname为用户名称,
  4. 项目名称是coach_alerts表通过order_id关联order表查询出的service_id通过order表的service_id关联project表查询出title为项目名称,
  5. 下单时间是coach_alerts表通过order_id关联的order_records表经过判断order_id是否存在以及state状态是否是接单状态最后都满足则获取order_records表的created_at字段为下单时间,
  6. 支付金额是coach_alerts表通过order_id直接与wallet_payment_records表关联判断订单编号是否存在,存在则获取wallet_payment_records表的actual_amount为支付金额,
  7. 服务状态是coach_alerts表通过order_id关联的order_records表经过判断order_id是否存在以及state最新状态,
  8. 技师昵称是coach_alerts表通过order_id关联order表查询出的coach_id通过order表的coach_id关联coach_users表查询出user_id然后再去关联member_users表查询出nickname为技师名称,
  9. 技师接单时间是coach_alerts表通过order_id关联的order_records表经过判断order_id是否存在以及state状态是否是技师接单状态最后都满足则获取order_records表的created_at字段为技师接单时间,
  10. 区域是coach_alerts表的order_id关联的order表中的area_code字段,
  11. 地址是coach_alerts表的order_id关联的order表中的address字段,
  12. 备注是coach_alerts表的order_id关联的order表中的remark字段,
  13. 代理商是coach_alerts表的order_id关联的order表获取agent_id再去关联agent_infos表获取user_id再去关联member_users表中的nickname字段为代理商,
  14. 代理商电话是coach_alerts表的order_id关联的order表获取agent_id再去关联agent_infos表获取user_id再去关联member_users表中的mobile字段为代理商电话
  15. 查询不出的字段:跟单客服是系统表的
  16. 没设计的字段:附近站点电话、负责人电话
  17. */
  18. /*
  19. 查询订单编号、项目名称、用户名称、下单时间、支付金额、服务状态、技师、技师接单时间、区域、地址、备注、代理商、代理商电话
  20. */
  21. WITH order_create_time AS (
  22. /* 获取下单时间 */
  23. SELECT
  24. order_id,
  25. created_at
  26. FROM order_records ord1
  27. WHERE state = '接单状态' /* 请替换为实际的接单状态值 */
  28. AND created_at = (
  29. SELECT MIN(created_at)
  30. FROM order_records ord2
  31. WHERE ord1.order_id = ord2.order_id
  32. AND state = '接单状态'
  33. )
  34. ),
  35. coach_accept_time AS (
  36. /* 获取技师接单时间 */
  37. SELECT
  38. order_id,
  39. created_at
  40. FROM order_records ord1
  41. WHERE state = '技师接单状态' /* 请替换为实际的技师接单状态值 */
  42. AND created_at = (
  43. SELECT MIN(created_at)
  44. FROM order_records ord2
  45. WHERE ord1.order_id = ord2.order_id
  46. AND state = '技师接单状态'
  47. )
  48. )
  49. SELECT
  50. ca.order_id /* 订单编号 */,
  51. p.title /* 项目名称 */,
  52. mu1.nickname /* 用户名称 */,
  53. oct.created_at /* 下单时间 */,
  54. wpr.actual_amount /* 支付金额 */,
  55. orr.state /* 服务状态 */,
  56. mu2.nickname /* 技师昵称 */,
  57. cat.created_at /* 技师接单时间 */,
  58. t1.area_code /* 区域 */,
  59. t1.address /* 地址 */,
  60. t1.remark /* 备注 */,
  61. mu3.nickname /* 代理商 */,
  62. mu3.mobile /* 代理商电话 */
  63. FROM coach_alerts ca
  64. LEFT JOIN `order` t1 ON ca.order_id = t1.id
  65. LEFT JOIN project p ON t1.service_id = p.id
  66. LEFT JOIN member_users mu1 ON t1.user_id = mu1.id
  67. LEFT JOIN order_create_time oct ON ca.order_id = oct.order_id
  68. LEFT JOIN wallet_payment_records wpr ON ca.order_id = wpr.order_id
  69. LEFT JOIN order_records orr ON ca.order_id = orr.order_id
  70. LEFT JOIN coach_users cu ON t1.coach_id = cu.id
  71. LEFT JOIN member_users mu2 ON cu.user_id = mu2.id
  72. LEFT JOIN coach_accept_time cat ON ca.order_id = cat.order_id
  73. LEFT JOIN agent_infos ai ON t1.agent_id = ai.id
  74. LEFT JOIN member_users mu3 ON ai.user_id = mu3.id
  75. WHERE orr.created_at = (
  76. /* 获取最新状态记录 */
  77. SELECT MAX(created_at)
  78. FROM order_records
  79. WHERE order_id = ca.order_id
  80. )
  81. ORDER BY ca.order_id DESC;