举个例子
假设我们有这样的几个表，第一个表是公司组织关系product_line，表内容如下：
1
2
3
4
5
6
7
8
9+----+-------------------+------------------------+
| id | product_line_name | parent_product_line_id |
+----+-------------------+------------------------+
| 1 | 库存 | 39 |
| 2 | 服务表达 | 39 |
| 3 | 履约 | 39 |
| 4 | 商货品 | 39 |
| 5 | 财务结算 | 39 |
+----+-------------------+------------------------+
这里的39是他们的父部门的代号，还有一个表是每一个表的对账规则rule_case，表内容如下：
1
2
3
4
5
6
7
8
9
10
11
12
13+----+---------+---------------+-----+-----------------+
| id | deleted | rule_name | app | product_line_id |
+----+---------+---------------+-----+-----------------+
| 1 | 0 | 库存规则1 | aaa | 1 |
| 2 | 0 | 库存规则2 | aaa | 1 |
| 3 | 0 | 履约规则1 | bbb | 3 |
| 4 | 0 | 履约规则2 | bbb | 3 |
| 5 | 0 | 财务规则1 | ccc | 5 |
| 6 | 0 | 财务规则2 | ccc | 5 |
| 7 | 0 | 会员规则1 | xxx | 10 |
| 8 | 0 | 财务规则3 | ccc | 5 |
| 9 | 1 | 库存规则1 | aaa | 99 |
+----+---------+---------------+-----+-----------------+
这里面product_line_id就是上面product_line里的id，可见“会员规则1”就不是父部门39的一员（这里最后一行的“库存规则1”也不是39的一员，另有他用，所以deleted=1,做了逻辑删除）。
还有一个表，就是对账规则产生的对应任务及任务的结果rule_task，表内容如下：
1
2
3
4
5
6
7
8
9
10
11
12+----+--------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+
| id | rule_case_id | result | status |
+----+--------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+
| 1 | 1 | {"sqlResult":"[{"mig_task_status":"OVER_ERROR","COUNT(*)":871},{"mig_task_status":"OVER_ERR_OWNER_NO_IS_BLANK","COUNT(*)":2}]","execTime":164,"resultNum":2,"speNum":0} | 2 |
| 2 | 2 | {"sqlResult":"[]","execTime":131,"resultNum":0,"speNum":0} | 2 |
| 3 | 6 | {"sqlResult":"[]","execTime":131,"resultNum":0,"speNum":0} | 2 |
| 4 | 4 | {"sqlResult":"[{"mig_task_status":"HEMA_ERR_CATEGORY_NAME_IS_BLANK","COUNT(*)":44}]","execTime":1374,"resultNum":1,"speNum":0} | 2 |
| 5 | 3 | {"sqlResult":"[{"mig_task_status":"HEMA_ERROR","COUNT(*)":4}]","execTime":1523,"resultNum":1,"speNum":0} | 2 |
| 6 | 3 | {"sqlResult":"[]","execTime":1656,"resultNum":0,"speNum":0} | 2 |
| 7 | 7 | 这是会员规则的任务 | 2 |
| 8 | 7 | 这是会员规则的任务2222 | 2 |
+----+--------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+
这种类型在工作中很常见，那么需要查询所有父部门是39的rule，sql很简单：
1
2
3
4
5select * from rule_case WHERE deleted = 0 and product_line_id IN (
select id
from product_line
where parent_product_line_id = 39
);
效果如图：
但是如果要链接三个表，比如要查询所有父部门是39的规则对应的任务结果？怎么写？
一般来说，链接多个表都是用join，左join右join看以哪个表为准。但是如果你先写where在where的基础上再去join那就回报错，比如这个语句：
1
select rule_name from rule_case where product_line_id in (select id from product_line where parent_product_line_id = 39) inner join rule_task on rule_case.id = rule_task.rule_case_id;
错误如下：
格式有误，应该格式这么写：select * from 表A a left join 表B b on (a.XX=b.XX) left join 表C c on (a.YY=C.YY) where a.GG=MM and b.ui=NN
所以语句就是：
1
select rule_name,rule_task.result from rule_case inner join rule_task on rule_case.id = rule_task.rule_case_id where rule_case.product_line_id in (select id from product_line where parent_product_line_id = 39);
结果如图：
Django2里怎么用？##
如果是在django2.1里也要做这种多表操作，上面的语句就是这样的：
参考资料
https://blog.csdn.net/haibo0668/article/details/52453232
https://database.51cto.com/art/201908/602009.htm
https://zhuanlan.zhihu.com/p/59656673
https://www.cnblogs.com/neozheng/p/9160526.html
