php语言如何实现sql查询,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了吧。

客户端的运行结果