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

用戶端的運行結果

Database Backup下載地址：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了吧。

用戶端的運行結果

Database Backup下載地址：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完成邏輯

