连表查询SQL语句

连表查询SQL语句

---

前言

首先说一下：本文主要是解决Django中ORM多表联查耗时较长的问题，简单说下涉及到的表

Employee (员工表)、Company (省公司表)、SubCompany (分公司表)、GasStation (加油站表)、Application (员工返利申请表)、ApplicationGoods (员工返利申请表中包含的商品信息)、Goods (商品表)

以上是涉及到查询的所有表，其中关联关系不细说了，下边的SQL语句中可以细品

正文

select
aca.id, ace.name as employee_name, acg.name as gasstation_name, acs.name as subcompany_name,
acp.name as province_company_name, acgoods.name as goods_name, acag.count as count,
acag.price as price, acag.count * acag.price as goods_total, acag.count * acag.rebate as total_rebate,
aca.status as status, aca.create_time as create_time
from 
admin_cms_application as aca, 
admin_cms_applicationgoods acag, 
admin_cms_employee as ace,
admin_cms_gasstation as acg,
admin_cms_subcompany as acs,
admin_cms_provincecompany as acp,
admin_cms_goods as acgoods
where 
aca.id=acag.application_id 
and aca.employee_id = ace.id
and aca.gas_station_id=acg.id
and aca.sub_company_id=acs.id
and aca.province_company_id=acp.id
and acag.origin_goods_id=acgoods.id
and create_time between '2020-04-17 00:00:00' and '2020-05-30 00:00:00'
and aca.status = 2;

select后边定义的是查询出来dict的key，这里说一下admin_cms_application（aca）是主表，其他表都是查询结果中所需要展示其中某个字段

from后边跟着的就是涉及到查询的所有表，as 后的为别名

where后边的为过滤条件，aca.id=acag.application_id意思是admin_cms_applicationgoods（acag）这个表中有admin_cms_application（aca）表的外键，

同理acag.origin_goods_id=acgoods.id表示admin_cms_applicationgoods（acag）表中有admin_cms_goods（acgoods）表的外键，

aca.employee_id=ace.id表示admin_cms_application（aca）表关联了admin_cms_employee（ace）表，

aca.gas_station_id=acg.id表示admin_cms_application（aca）表关联了admin_cms_gasstation（acg）表，

aca.sub_company_id=acs.id表示admin_cms_application（aca）表关联了admin_cms_sub_company（acs）表，

aca.province_company_id=acp.id表示admin_cms_application（aca）表关联了admin_cms_province_company（acp）表，

acag.origin_goods_id=acgoods.id表示admin_cms_applicationgoods（acag）表关联了admin_cms_goods（acgoods）表，

下边两个是筛选项，create_time between '2020-04-17 00:00:00' and '2020-05-30 00:00:00'表示筛选该时间段内的数据，aca.status = 2表示筛选admin_cms_application（aca）表中状态为2的数据

至此整个SQL链表查询就结束了，相比较Django中ORM查询的方式使用SQL语句查询快得多（ORM查询完耗时大概在7-10秒之间，因为中间还有写有循环，SQL语句查询时间大概在0.02-0.06秒之间）

由于表结构不方便公开，就请细品SQL语句吧