-- Active: xiaoding_test@@192.168.110.85@3306@xiaoding_test /* 订单编号是coach_alerts表的order_id,用户名称是coach_alerts表通过order_id关联order表查询出的user_id通过order表的user_id关联member_users表查询出nickname为用户名称, 项目名称是coach_alerts表通过order_id关联order表查询出的project_id通过order表的project_id关联project表查询出title为项目名称, 下单时间是coach_alerts表通过order_id关联的order_records表经过判断order_id是否存在以及state状态是否是接单状态最后都满足则获取order_records表的created_at字段为下单时间, 支付金额是coach_alerts表通过order_id直接与wallet_payment_records表关联判断订单编号是否存在,存在则获取wallet_payment_records表的actual_amount为支付金额, 服务状态是coach_alerts表通过order_id关联的order_records表经过判断order_id是否存在以及state最新状态, 技师昵称是coach_alerts表通过order_id关联order表查询出的coach_id通过order表的coach_id关联coach_users表查询出user_id然后再去关联member_users表查询出nickname为技师名称, 技师接单时间是coach_alerts表通过order_id关联的order_records表经过判断order_id是否存在以及state状态是否是技师接单状态最后都满足则获取order_records表的created_at字段为技师接单时间, 区域是coach_alerts表的order_id关联的order表中的area_code字段, 地址是coach_alerts表的order_id关联的order表中的address字段, 备注是coach_alerts表的order_id关联的order表中的remark字段, 代理商是coach_alerts表的order_id关联的order表获取agent_id再去关联agent_infos表获取user_id再去关联member_users表中的nickname字段为代理商, 代理商电话是coach_alerts表的order_id关联的order表获取agent_id再去关联agent_infos表获取user_id再去关联member_users表中的mobile字段为代理商电话 查询不出的字段:跟单客服是系统表的 没设计的字段:附近站点电话、负责人电话 */ /* 查询订单编号、项目名称、用户名称、下单时间、支付金额、服务状态、技师、技师接单时间、区域、地址、备注、代理商、代理商电话 */ /* 报警订单视图 */ DROP VIEW IF EXISTS manage_alarm_order; CREATE OR REPLACE VIEW manage_alarm_order AS WITH order_create_time AS ( /* 获取下单时间 */ SELECT order_id, created_at FROM order_records ord1 WHERE state = '接单状态' /* 请替换为实际的接单状态值 */ AND created_at = ( SELECT MIN(created_at) FROM order_records ord2 WHERE ord1.order_id = ord2.order_id AND state = '接单状态' ) ), coach_accept_time AS ( /* 获取技师接单时间 */ SELECT order_id, created_at FROM order_records ord1 WHERE state = '技师接单状态' /* 请替换为实际的技师接单状态值 */ AND created_at = ( SELECT MIN(created_at) FROM order_records ord2 WHERE ord1.order_id = ord2.order_id AND state = '技师接单状态' ) ) SELECT ca.order_id AS order_id /* 序号 */, t1.order_no AS order_no /* 订单号 */, p.title AS project_name /* 项目名称 */, mu1.nickname AS user_name /* 用户名称 */, OCT.created_at AS order_time /* 下单时间 */, wpr.actual_amount AS payment_amount /* 支付金额 */, orr.state AS service_state /* 服务状态 */, mu2.nickname AS coach_name /* 技师昵称 */, cat.created_at AS coach_accept_time /* 技师接单时间 */, t1.area_code AS area_code /* 区域 */, t1.address AS address /* 地址 */, t1.remark AS remark /* 备注 */, mu3.nickname AS agent_name /* 代理商 */, mu3.mobile AS agent_mobile /* 代理商电话 */ FROM coach_alerts ca LEFT JOIN `order` t1 ON ca.order_id = t1.id LEFT JOIN project p ON t1.project_id = p.id LEFT JOIN member_users mu1 ON t1.user_id = mu1.id LEFT JOIN order_create_time OCT ON ca.order_id = OCT.order_id LEFT JOIN wallet_payment_records wpr ON ca.order_id = wpr.order_id LEFT JOIN order_records orr ON ca.order_id = orr.order_id LEFT JOIN coach_users cu ON t1.coach_id = cu.id LEFT JOIN member_users mu2 ON cu.user_id = mu2.id LEFT JOIN coach_accept_time cat ON ca.order_id = cat.order_id LEFT JOIN agent_infos ai ON t1.agent_id = ai.id LEFT JOIN member_users mu3 ON ai.user_id = mu3.id WHERE orr.created_at = ( /* 获取最新状态记录 */ SELECT MAX(created_at) FROM order_records WHERE order_id = ca.order_id ) ORDER BY ca.order_id DESC; SELECT * FROM manage_alarm_order;