首页 > 代码库 > SQL高级查询技巧(两次JOIN同一个表,自包含JOIN,不等JOIN)

SQL高级查询技巧(两次JOIN同一个表,自包含JOIN,不等JOIN)

掌握了这些,就比较高级啦

Using the Same Table Twice

如下面查询中的branch字段

SELECT a.account_id, e.emp_id, b_a.name open_branch, b_e.name emp_branch FROM account AS a INNER JOIN branch AS b_a ON a.open_branch_id = b_a.branch_id INNER JOIN employee AS e ON a.open_emp_id = e.emp_id INNER JOIN branch b_e ON e.assigned_branch_id = b_e.branch_id WHERE a.product_cd = CHK;

 

Self-Joins

因为employee中自引用了自己的上级,所以存在这个可能性,django example里也有这样的CASE

SELECT e.fname, e.lname, e_mgr.fname mgr_fname, e_mgr.lname mgr_lname FROM employee AS e INNER JOIN employee AS e_mgr ON e.superior_emp_id = e_mgr.emp_id;

 

Non-Equi-Joins

在JOIN语句的ON条件中,不一定非要用等号,也可以不等号

 SELECT e1.fname, e1.lname, VS vs, e2.fname, e2.lname FROM employee AS e1 INNER JOIN employee AS e2 ON e1.emp_id < e2.emp_id WHERE e1.title = Teller AND e2.title = Teller;

技术分享

SQL高级查询技巧(两次JOIN同一个表,自包含JOIN,不等JOIN)