目錄
回复内容:
首頁 後端開發 php教程 ThinkPHP达人,一个非常复杂的SQL查询,用TP框架的链式方法如何实现?

ThinkPHP达人,一个非常复杂的SQL查询,用TP框架的链式方法如何实现?

Jun 06, 2016 pm 08:20 PM
php sql thinkphp

<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了吧。

客户端的运行结果
ThinkPHP达人,一个非常复杂的SQL查询,用TP框架的链式方法如何实现?

数据库备份下载地址: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了吧。

客户端的运行结果
ThinkPHP达人,一个非常复杂的SQL查询,用TP框架的链式方法如何实现?

数据库备份下载地址: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完成邏輯

本網站聲明
本文內容由網友自願投稿,版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容,請聯絡admin@php.cn

熱AI工具

Undresser.AI Undress

Undresser.AI Undress

人工智慧驅動的應用程序,用於創建逼真的裸體照片

AI Clothes Remover

AI Clothes Remover

用於從照片中去除衣服的線上人工智慧工具。

Undress AI Tool

Undress AI Tool

免費脫衣圖片

Clothoff.io

Clothoff.io

AI脫衣器

AI Hentai Generator

AI Hentai Generator

免費產生 AI 無盡。

熱門文章

R.E.P.O.能量晶體解釋及其做什麼(黃色晶體)
2 週前 By 尊渡假赌尊渡假赌尊渡假赌
倉庫:如何復興隊友
4 週前 By 尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island冒險:如何獲得巨型種子
3 週前 By 尊渡假赌尊渡假赌尊渡假赌

熱工具

記事本++7.3.1

記事本++7.3.1

好用且免費的程式碼編輯器

SublimeText3漢化版

SublimeText3漢化版

中文版,非常好用

禪工作室 13.0.1

禪工作室 13.0.1

強大的PHP整合開發環境

Dreamweaver CS6

Dreamweaver CS6

視覺化網頁開發工具

SublimeText3 Mac版

SublimeText3 Mac版

神級程式碼編輯軟體(SublimeText3)

適用於 Ubuntu 和 Debian 的 PHP 8.4 安裝和升級指南 適用於 Ubuntu 和 Debian 的 PHP 8.4 安裝和升級指南 Dec 24, 2024 pm 04:42 PM

適用於 Ubuntu 和 Debian 的 PHP 8.4 安裝和升級指南

CakePHP 專案配置 CakePHP 專案配置 Sep 10, 2024 pm 05:25 PM

CakePHP 專案配置

CakePHP 日期和時間 CakePHP 日期和時間 Sep 10, 2024 pm 05:27 PM

CakePHP 日期和時間

CakePHP 檔案上傳 CakePHP 檔案上傳 Sep 10, 2024 pm 05:27 PM

CakePHP 檔案上傳

CakePHP 路由 CakePHP 路由 Sep 10, 2024 pm 05:25 PM

CakePHP 路由

討論 CakePHP 討論 CakePHP Sep 10, 2024 pm 05:28 PM

討論 CakePHP

如何設定 Visual Studio Code (VS Code) 進行 PHP 開發 如何設定 Visual Studio Code (VS Code) 進行 PHP 開發 Dec 20, 2024 am 11:31 AM

如何設定 Visual Studio Code (VS Code) 進行 PHP 開發

CakePHP 快速指南 CakePHP 快速指南 Sep 10, 2024 pm 05:27 PM

CakePHP 快速指南

See all articles