ThinkPHP is a simple method for implementing multiple subquery statements.

ThinkPHP is a simple method for implementing multiple subquery statements.

This example describes how thinkPHP implements multiple subquery statements. We will share this with you for your reference. The details are as follows:

SQL statements

With a good understanding of SQL statements, you can make good use of database operations in thinkphp and other frameworks.

Original SQL:

SELECT a.*,b.* from (SELECT a.id as opener_id,a.name,sum(c.money) as bonus_money,c.year,c.month from sh_opener aLEFT JOIN sh_opener_bonus b on a.id = b.opener_idLEFT JOIN sh_incentive c on b.incentive_id = c.idwhere a.agent_id = 3 and a.status = 1 and c.year = 2015 and c.month = 11GROUP BY a.id,c.year,c.month) aLEFT JOIN (SELECT a.id as payment_id,a.opener_id,a.money as payment_money,a.trode_number from sh_opener_bonus_payment awhere a.year = 2015 and a.`month` = 11 and a.agent_id = 3) bon a.opener_id = b.opener_id;

There are two subquery statements. In fact, the subquery statement is also a table, but it only exists in memory.

Thinkphp implementation:

$ UseYear = date ('y', strtotime ('Last month'); $ this-> assign ('useyear', $ useYear ); $ useMonth = date ('M', strtotime ('Last month'); $ this-> assign ('usemonth', $ useMonth ); // obtain the bonus amount of the person in the previous month. // subquery 1 $ whereSub1 ['a. agent_id '] = $ this-> agent_id; $ whereSub1 ['a. status '] = 1; $ whereSub1 ['C. year '] = $ useYear; $ whereSub1 ['C. month '] = $ useMonth; $ subQuery1 = M ()-> table ('sh _ opener A')-> join ('sh _ opener_bonus B on. id = B. opener_id ')-> join ('sh _ incentive c on B. incentive_id = c. id')-> where ($ whereSub1)-> group ('a. id, c. year, c. month ')-> field ('a. id,. name, sum (c. money) as bonus_money, c. year, c. month ')-> select (false); // subquery 2 $ whereSub2 ['a. agent_id '] = $ this-> agent_id; $ whereSub2 ['a. year '] = $ useYear; $ whereSub2 ['a. month '] = $ useMonth; $ subQuery2 = M ()-> table ('sh _ opener_bonus_payment A')-> where ($ whereSub2)-> field ('a. id as payment_id,. opener_id,. money as payment_money,. trode_number ')-> select (false); $ list = M ()-> table ($ subQuery1. 'A')-> join ($ subQuery2.' B on. id = B. opener_id ')-> select (); $ this-> assign ('LIST', $ list );

In fact, the thinkphp framework encapsulates SQL, and eventually it will be pieced together into SQL statements.