Thinkphp talent, a very complex SQL query, with the TP framework of the chain method how to implement?

SELECT ' App_plan '. *, ' app_agreement '. *, ' app_customer '. *, ' app_product '. *, ' app_product_category '. *, @ Plan Total Original Amount: = (select sum (' app_operation '. ' Send_weight ') from ' app_operation ' where ' app_operation '. ' plan_id ' = ' app_p Lan '. ' plan_id ' and ' app_operation '. ' Is_del ' = 0 ' as ' plan_total_send_weight ', @ planned total yield: = (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 ', @ planned total road loss: = Ifnull ((select sum (' app_operation ') ' send_weight '-' App_operat Ion ' 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 ', @ planned total in transit: = Ifnull (sel ECT sum (' app_operation '. ' Send_weight ') from ' app_operation ' where ' app_operation '. ' plan_id ' = ' app_plan '. ' plan_id ' and   ' App_operation '. ' Is_del ' = 0and ' app_operation '. ' Operation_status ' = 1), 0) as ' plan_onway_quantity ', @ Planned road loss exceeded: = Ifnull ((SELECT sum (' App_operat Ion ' 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, @ Road loss exceeding deduction: = 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, @ planned total number of departures: = Ifnull ((select COUNT (*) from ' app_operation ' where ' app_oper ation '. ' plan_id ' = ' app_plan '. ' plan_id ' and ' App_operation '. ' Is_del ' = 0), 0) as ' plan_count_send ', @ Plan total repossession Number: = Ifnull ((select COUNT (*) from ' app_operation ' where ' app_operation '. ' P lan_id ' = ' app_plan '. ' plan_id ' and ' app_operation '. ' Operation_status ' = 2 and ' app_operation '. ' Is_del ' = 0 ', 0) as ' Plan_ Count_receive ', @ Total Freight: = 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 ', @ payable Shipping: = Truncate (@ ifnull (@ Total Shipping-@ Road loss over debit), 0), 2) as Total_invoice_ship_fee, @ plan not issued: = Truncate (I Fnull (' plan_total_quantity '-@ Total planned yield-@ planned total in transit, 0), 2) as ' plan_total_not_quantity ' from (' App_plan ') left JOIN ' App_agr Eement ' 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 

The attachment is a database SQL backup.
Let's try to see, this should be quite a complicated SQL.

Results of Client Run

Database backup Download Address: Http://7xiqxd.com2.z0.glb.qiniucdn.com/deliver.sql.zip

Reply content:

SELECT ' App_plan '. *, ' app_agreement '. *, ' app_customer '. *, ' app_product '. *, ' app_product_category '. *, @ Plan Total Original Amount: = (select sum (' app_operation '. ' Send_weight ') from ' app_operation ' where ' app_operation '. ' plan_id ' = ' app_p Lan '. ' plan_id ' and ' app_operation '. ' Is_del ' = 0 ' as ' plan_total_send_weight ', @ planned total yield: = (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 ', @ planned total road loss: = Ifnull ((select sum (' app_operation ') ' send_weight '-' App_operat Ion ' 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 ', @ planned total in transit: = Ifnull (sel ECT sum (' app_operation '. ' Send_weight ') from ' app_operation ' where ' app_operation '. ' plan_id ' = ' app_plan '. ' plan_id ' and   ' App_operation '. ' Is_del ' = 0and ' app_operation '. ' Operation_status ' = 1), 0) as ' plan_onway_quantity ', @ Planned road loss exceeded: = Ifnull ((SELECT sum (' App_operat Ion ' 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, @ Road loss exceeding deduction: = 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, @ planned total number of departures: = Ifnull ((select COUNT (*) from ' app_operation ' where ' app_oper ation '. ' plan_id ' = ' app_plan '. ' plan_id ' and ' App_operation '. ' Is_del ' = 0), 0) as ' plan_count_send ', @ Plan total repossession Number: = Ifnull ((select COUNT (*) from ' app_operation ' where ' app_operation '. ' P lan_id ' = ' app_plan '. ' plan_id ' and ' app_operation '. ' Operation_status ' = 2 and ' app_operation '. ' Is_del ' = 0 ', 0) as ' Plan_ Count_receive ', @ Total Freight: = 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 ', @ payable Shipping: = Truncate (@ ifnull (@ Total Shipping-@ Road loss over debit), 0), 2) as Total_invoice_ship_fee, @ plan not issued: = Truncate (I Fnull (' plan_total_quantity '-@ Total planned yield-@ planned total in transit, 0), 2) as ' plan_total_not_quantity ' from (' App_plan ') left JOIN ' App_agr Eement ' 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 

The attachment is a database SQL backup.
Let's try to see, this should be quite a complicated SQL.

Results of Client Run

Database backup Download Address: Http://7xiqxd.com2.z0.glb.qiniucdn.com/deliver.sql.zip

So complex why still have to use the chain to solve, your SQL is out, direct SQL is not OK. TP model is only for the convenience of use, encapsulated a number of commonly used simple query update operation, complex operation recommended direct SQL implementation. Even if you racked your brains and came up with a complicated way to do this complex operation by TP chain method, finally TP still want to turn your operation into SQL, you say you this is not laborious?

Such a complex SQL statement is not recommended for conversion to TP SQL statement, there is no significant significance. The chain of TP will eventually be escaped into SQL statements to achieve, you can write good SQL, and then use M to instantiate the database to execute SQL. There are such complex SQL in the existence of internal and external links, which will greatly consume SQL resources, if the data volume is large, it is not easy to obtain data, it is recommended to split the SQL statement, the basic data after the use of PHP language writing algorithm, so faster.

Do you want to be effective or do you want to construct the language?
To the efficiency of the direct SQL is calculated
To understand the language, use model to complete the logically album.

