<code>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</code>
附件是数据库SQL备份。
大家尝试看看,这应该算相当复杂的SQL了吧。
客户端的运行结果
数据库备份下载地址:http://7xiqxd.com2.z0.glb.qiniucdn.com/deliver.sql.zip
回复内容:
<code>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</code>
附件是数据库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完成邏輯

本文讨论了PHP中的crypt()和password_hash()之间的差异,以进行密码哈希,重点介绍其实施,安全性和对现代Web应用程序的适用性。

文章讨论了通过输入验证,输出编码以及使用OWASP ESAPI和HTML净化器之类的工具来防止PHP中的跨站点脚本(XSS)。

自动加载PHP会在需要时自动加载类文件,从而通过减少内存使用和增强代码组织来提高性能。最佳实践包括使用PSR-4和有效组织代码。

本文讨论了在PHP中管理文件上传大小的管理,重点是2MB的默认限制以及如何通过修改PHP.INI设置来增加它。

本文讨论了PHP 7.1中引入的PHP中的无效类型,允许变量或参数为指定类型或NULL。它突出显示了诸如提高可读性,类型安全性和明确意图的好处,并解释了如何声明

本文讨论了unset()和unlink()功能在编程中的差异,重点关注其目的和用例。 unset()从内存中删除变量,而unlink()从文件系统中删除文件。两者都对效率至关重要


热AI工具

Undresser.AI Undress
人工智能驱动的应用程序,用于创建逼真的裸体照片

AI Clothes Remover
用于从照片中去除衣服的在线人工智能工具。

Undress AI Tool
免费脱衣服图片

Clothoff.io
AI脱衣机

Video Face Swap
使用我们完全免费的人工智能换脸工具轻松在任何视频中换脸!

热门文章

热工具

SublimeText3汉化版
中文版,非常好用

PhpStorm Mac 版本
最新(2018.2.1 )专业的PHP集成开发工具

ZendStudio 13.5.1 Mac
功能强大的PHP集成开发环境

mPDF
mPDF是一个PHP库,可以从UTF-8编码的HTML生成PDF文件。原作者Ian Back编写mPDF以从他的网站上“即时”输出PDF文件,并处理不同的语言。与原始脚本如HTML2FPDF相比,它的速度较慢,并且在使用Unicode字体时生成的文件较大,但支持CSS样式等,并进行了大量增强。支持几乎所有语言,包括RTL(阿拉伯语和希伯来语)和CJK(中日韩)。支持嵌套的块级元素(如P、DIV),

EditPlus 中文破解版
体积小,语法高亮,不支持代码提示功能