ThinkPHP达人，一个非常复杂的SQL查询，用TP框架的链式方法如何实现？
程序员文章站
2024-01-17 23:07:16
...
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了吧。
客户端的运行结果
数据库备份下载地址：http://7xiqxd.com2.z0.glb.qiniucdn.com/deliver.sql.zip
回复内容：
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了吧。
客户端的运行结果
数据库备份下载地址：http://7xiqxd.com2.z0.glb.qiniucdn.com/deliver.sql.zip
这么复杂干嘛还非要用链式来解决呢，你的SQL都出来了，直接SQL不就行了么。TP的Model只是为了方便使用，封装了常用的几种简单查询更新的操作，复杂的操作建议直接SQL实现。就算你绞尽脑汁想出了一个复杂的通过TP链式方法实现这个复杂操作的写法，最后TP还是要把你的操作转成SQL，你说你这不是瞎费劲么？
这么复杂的sql语句不建议转换成TP的sql语句，没有什么太大的意义。TP的链式方式最终还是会转义成sql语句来实现的，可以先写好sql，对然后用M对数据库实例化执行sql就可以了。还有如此复杂的sql中存在内外链接，这样会很大的消耗sql资源，如果数据量大的情况下，获取数据不易，建议拆分sql语句，获得基础数据后用php语言写算法，这样速度会快一些。
你要效能還是想要語句結構化？
要效能就直接SQL算出來
要語句理解就用Model完成邏輯