小谈数据库的Left Outer Join和CTE

今天和经理一起合作写了一个非常长的Query, 对Join和CTE的理解又加深了许多。由于对真实需求的描述涉及到非常复杂的业务知识，所以在此文章对需求进行抽象的描述。

---

需求：
给定两个表A, B。A当中有三个Columns, 假定为C_A1，C_A2，C_A3， C_A4。B当中有也有两个值C_B1和C_B2。两个表没有外键联系，但是潜在的对应关系为C_A3 对应 C_B1, C_A4对应C_B2。
那么需要找出在每个拥有最大C_A2的items当中，C_A3 = C_B1, 但在这个item当中的C_A4，从B中找不到对应的C_B2。

---

例子：

A表

C_A1 C_A2 C_A3 C_A4 123 10/01/2015 999 08/01/2015 123 09/01/2015 999 08/01/2015 456 10/01/2015 888 10/01/2015 456 09/01/2015 888 09/01/2015

---

B表

C_B1 C_B2 999 09/01/2015 999 10/01/2015 888 09/01/2015 888 10/01/2015

---

那么该返回的应该是A表当中这个record

C_A1 C_A2 C_A3 C_A4 123 10/01/2015 999 08/01/2015

分析：

首先我们可以利用CTE找到A表当中的对应C_A2的最大值。

WITH max_c AS (SELECT C_A1, C_A3, C_A4, MAX(C_A2) max_c_a2FROM Agroup by C_A1, C_A3, C_A4)

然后，利用left outer join在B当中寻找C_A4不存在的项

SELECT * FROM A a1JOIN max_c mc ON (a1.C_A1 = mc.C_A1 AND a1.C_A3 = mc.C_A3 AND a1.C_A4 = mc.C_A4 AND a1.C_A1 = mc.max_c_a2)LEFT OUTER JOIN B b ON (a1.C_A3 = b.C_B1 AND a1.C_A4 = b.C_B2)WHERE b.C_B2 = NULL;

在这里正是利用了以下LEFT OUTER JOIN的特性

LEFT OUTER JOIN preserves the unmatched rows from the first (left) table, joining them with a NULL row in the shape of the second (right) table.

总结：这篇文章介绍了数据库中LEFT OUTER JOIN的实际项目中的应用。