ThinkPHP达人，一个非常复杂的SQL查询，用TP框架如何实现？

Question

 SELECT `app_plan`.*,
       `app_agreement`.*,
       `app_customer`.*,
       `app_product`.*,
       `app_product_category`.*,
       @计划总原发量:=(
select sum(`app_operation`.`send_weight`)
  from `app_operation`
 where `app_operation`.`plan_id`= `app_plan`.`plan_id`
   AND `app_operation`.`is_del`= 0) as `plan_total_send_weight`,
         @计划总实收量:=(
select sum(`app_operation`.`receive_weight`)
  from `app_operation`
 where `app_operation`.`plan_id`= `app_plan`.`plan_id`
   AND `app_operation`.`is_del`= 0) as `plan_total_receive_weight`,
         @计划总路损量:= IFNULL((
select sum(`app_operation`.`send_weight`-`app_operation`.`receive_weight`)
  from `app_operation`
 where `app_operation`.`plan_id`= `app_plan`.`plan_id`
   AND `app_operation`.`is_del`= 0
   AND `app_operation`.`operation_status`= 2), 0) as `plan_total_lost_quantity`,
         @计划总在途量:= IFNULL((
select sum(`app_operation`.`send_weight`)
  from `app_operation`
 where `app_operation`.`plan_id`= `app_plan`.`plan_id`
   AND `app_operation`.`is_del`= 0
   AND `app_operation`.`operation_status`= 1), 0) as `plan_onway_quantity`,
         @计划路损超出量:= IFNULL((
SELECT sum(`app_operation`.`send_weight`-`app_operation`.`receive_weight`- `app_plan`.`rational_loss`)
  from `app_operation`
 where `app_operation`.`plan_id`= `app_plan`.`plan_id`
   and `app_operation`.`is_del`= 0
   AND `app_operation`.`operation_status`= 2
   AND(`send_weight`-`receive_weight`)> `app_plan`.`rational_loss`), 0) AS plan_over_lost, @路损超出扣款:= truncate(IFNULL((
SELECT sum((`app_operation`.`send_weight`-`app_operation`.`receive_weight`- `app_plan`.`rational_loss`) * `app_plan`.`plan_deduct_fee`)
  from `app_operation`
 where `app_operation`.`plan_id`= `app_plan`.`plan_id`
   and `app_operation`.`is_del`= 0
   AND `app_operation`.`operation_status`= 2
   AND(`send_weight`-`receive_weight`)> `app_plan`.`rational_loss`), 0), 2) AS plan_total_punish_fee, @计划总发车数:= IFNULL((
select count(*)
  from `app_operation`
 where `app_operation`.`plan_id`= `app_plan`.`plan_id`
   AND `app_operation`.`is_del`= 0), 0) as `plan_count_send`, @计划总收车数:= IFNULL((
select count(*)
  from `app_operation`
 where `app_operation`.`plan_id`= `app_plan`.`plan_id`
   AND `app_operation`.`operation_status`= 2
   AND `app_operation`.`is_del`= 0), 0) as `plan_count_receive`, @总运费:= truncate(IFNULL((
SELECT sum(`app_operation`.`receive_weight` * `app_operation`.`operation_fee`)
  FROM `app_operation`
 WHERE `app_operation`.plan_id= `app_plan`.`plan_id`
   AND `app_operation`.`is_del`= 0), 0), 2) as `plan_total_ship_fee`, @应付运费:= truncate(IFNULL((@总运费 - @路损超出扣款), 0), 2) AS total_invoice_ship_fee, @计划未发量:= truncate(IFNULL(`plan_total_quantity` - @计划总实收量 - @计划总在途量, 0), 2) AS `plan_total_not_quantity`
  FROM(`app_plan`)
  LEFT JOIN `app_agreement` ON `app_plan`.`agreement_id`= `app_agreement`.`agreement_id`
  LEFT JOIN `app_customer` ON `app_agreement`.`customer_id`= `app_customer`.`customer_id`
  LEFT JOIN `app_product` ON `app_agreement`.`product_id`= `app_product`.`product_id`
  LEFT JOIN `app_product_category` ON `app_product`.`category_id`= `app_product_category`.`category_id`
 WHERE `plan_status`= 1
   AND `app_plan`.`is_del`= 0

附件是数据库SQL备份。
大家尝试看看，这应该算相当复杂的SQL了吧。

数据库备份

Answer 1

http://segmentfault.com/q/1010000004306223?sort=created