thinkphp实现多个子查询语句
2024-10-19 12:32:04
sql语句博大精深
理解好sql语句,就能用好thinkphp等框架中的数据库操作
原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 = and a.status = and c.year = and c.month =
GROUP BY a.id,c.year,c.month) a
LEFT JOIN (SELECT a.id as payment_id,a.opener_id,a.money as payment_money,a.trode_number from sh_opener_bonus_payment a
where a.year = and a.`month` = and a.agent_id = ) b
on a.opener_id = b.opener_id;
这里面有两个子查询语句,其实子查询语句也是表,只不过是存在内存中罢了。
thinkphp实现
$useYear = date('Y',strtotime('last month'));
$this->assign('useYear',$useYear);
$useMonth = date('m',strtotime('last month'));
$this->assign('useMonth',$useMonth); // 获取上一月人员的奖金金额
// 子查询1
$whereSub1['a.agent_id'] = $this->agent_id;
$whereSub1['a.status'] = ;
$whereSub1['c.year'] = $useYear;
$whereSub1['c.month'] = $useMonth;
$subQuery1 = 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($whereSub1)->group('a.id,c.year,c.month')->field('a.id,a.name,sum(c.money) as bonus_money,c.year,c.month')->select(false); // 子查询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,a.opener_id,a.money as payment_money,a.trode_number')->select(false); $list = M()->table($subQuery1.' a')->join($subQuery2.' b on a.id = b.opener_id')->select();
$this->assign('list',$list);
其实thinkphp框架对sql的封装,最终还是要拼凑成sql语句。
最新文章
- Android监听系统短信数据库变化-提取短信内容
- C# 以管理员身份运行WinForm程序
- jdk安装配置具体分析
- Foundation和UIKit框架组织图
- taglib指令
- 5.Primitive, Reference, and Value Types
- java内部类的继承
- Spring Auto proxy creator example
- 汉诺塔 python版
- 解决网站出现GET .woff 404 (Not Found)的问题
- iOS利用通知(NSNotification)进行传值
- 最简单的html5语言
- Golang使用pprof和qcachegrind进行性能监控
- apply函数用法
- Ubuntu12.04中的虚拟机安装Ubuntu16.04,并实现远程控制16.04
- windows 动态库的封装以及调用
- python nose测试框架全面介绍十一---用例的发现
- JavaScript基础知识(数组的方法)
- jquery或者JavaScript调用WCF服务的方法
- Verilog 加法器和减法器(2)