Thinkphp simple method to implement multiple subqueries _php instance

In this paper, the thinkphp simple method of implementing multiple subqueries is described. Share to everyone for your reference, specific as follows:

SQL statement Profound

Understand the SQL statement, you can use a good thinkphp, such as database operations in the framework

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 a left
JOIN Sh_opener_bonus b on a.id = b.opener_id left
JOIN sh_incentive c on b.incentive_id = C.id
where a.agent_id = 3 and A.status = 1 and c.year = 2015 and c.month = one
GROUP by A.id,c.year,c.month) a left
JOIN (SELECT a.id as P Ayment_id,a.opener_id,a.money as Payment_money,a.trode_number from Sh_opener_bonus_payment a
where a.year = 2015 and a. ' Month ' = a.agent_id = 3) b on
a.opener_id = b.opener_id;

There are two subqueries in it, but the subquery is also a table, but it is in memory.

thinkphp implementation:

$useYear = Date (' Y ', Strtotime (' last month '));
$this->assign (' useyear ', $useYear);
$useMonth = Date (' m ', Strtotime (' last month '));
$this->assign (' Usemonth ', $useMonth);
Get bonus amount for last month//subquery 1 $whereSub 1[' a.agent_id '] = $this->agent_id;
$whereSub 1[' a.status '] = 1;
$whereSub 1[' c.year '] = $useYear;
$whereSub 1[' c.month '] = $useMonth; $subQuery 1 = M ()->table (' Sh_opener a ')->join (' Sh_opener_bonus b on a.id = b.opener_id ')->join (' Sh_incentive C On b.incentive_id = C.id ')->where ($whereSub 1)->group (' A.id,c.year,c.month ')->field (' A.id,a.name,sum ') (
C.money) as Bonus_money,c.year,c.month ')->select (false);
Subquery 2 $whereSub 2[' a.agent_id '] = $this->agent_id;
$whereSub 2[' a.year '] = $useYear;
$whereSub 2[' a.month '] = $useMonth; $subQuery 2 = M ()->table (' Sh_opener_bonus_payment a ')->where ($whereSub 2)->field (' a.ID as payment_id,
A.opener_id,a.money as Payment_money,a.trode_number ')->select (false); $list = M ()->table ($subQuery 1. ' A ')->join ($subQuery 2. ' B on A.Id = b.opener_id ')->select ();

 $this->assign (' list ', $list);

In fact, the thinkphp Framework for SQL encapsulation, eventually to be pieced together into SQL statements.

More interested readers of thinkphp related content can view the website topic: "thinkphp Introductory Course", "thinkphp template Operation Skill Summary", "thinkphp Common Method Summary", "The CodeIgniter Introductory Course", "CI ( CodeIgniter) Framework Advanced tutorials, introductory tutorials for the Zend framework Framework, Smarty Templates Primer tutorial, and PHP template technology summary.

I hope this article will help you with the PHP program design based on thinkphp framework.