2 mlunwen mlunwen 于 2016.01.14 13:28 提问

ThinkPHP达人,一个非常复杂的SQL查询,用TP框架如何实现?
 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了吧。

图片说明
数据库备份

1个回答

devmiao
devmiao   Ds   Rxr 2016.01.20 03:50
Csdn user default icon
上传中...
上传图片
插入图片
准确详细的回答,更有利于被提问者采纳,从而获得C币。复制、灌水、广告等回答会被删除,是时候展现真正的技术了!