7.12 SQL Server自连接

 

SQL Server Self Join

 

目录

• SQL Server Self Join
  • 简介
  • 自连接查询示例
    • 1）使用自连接查询层级数据
    • 2）使用自联接比较表中的行

 

简介

自连接将表连接到自身。它有助于查询层级数据或比较同一表中的行。

自联接使用内联接（INNER JOIN）或左联接（LEFT JOIN）子句。因为使用自联接的查询引用的是同一个表，，所以为了区别表它们，得使用别名为查询中的同一表分配不同的名称。

注意：在查询中引用多个相同的表而不使用表别名将报错。

语法：

	SELECT
	select_list
	FROM
	T t1
	[INNER | LEFT] JOIN T t2 ON
	join_predicate;

查询两次引用表T。表别名t1和t2用于在查询中为T表分配不同的名称。

自连接查询示例

1）使用自连接查询层级数据

有如下员工表（sales.staffs）：

员工表存储了员工得基本信息：员工Id，名字，姓氏，邮箱，还有一列manager_id制定了直接管理者Id。比如说因为Mireya的manager_id中的值是Fabiola,所以Fabiola是Mireya得上级领导。Mireya得像Fabiola报告工作。

Fabiola没有更上层领导（应该是最高层），因此manager_id列为NULL。

如果要知道谁是谁的直属领导，可以使用如下自连接查询：

	SELECT
	e.first_name + ' ' + e.last_name employee,
	m.first_name + ' ' + m.last_name manager
	FROM
	sales.staffs e
	INNER JOIN sales.staffs m ON m.staff_id = e.manager_id
	ORDER BY
	manager;

在本例中，我们引用了两次staffs表：一次作为员工的e，另一次作为领导的m。连接条件使用e.manager_id和m.staff_id匹配员工和领导关系。

因为是使用的内连接的自连接(INNER JOIN)，由于Fabiola Jackson没有上层领导，所以他就没有匹配上，没有出现在结果集中，如果想要Fabiola Jackson也展示出来，则可以使用左连接(LEFT JOIN):

	SELECT
	e.first_name + ' ' + e.last_name employee,
	m.first_name + ' ' + m.last_name manager
	FROM
	sales.staffs e
	LEFT JOIN sales.staffs m ON m.staff_id = e.manager_id
	ORDER BY
	manager;

2）使用自联接比较表中的行

有如下客户表：

以下语句使用自联接查找位于同一城市的客户。

	SELECT
	c1.city,
	c1.first_name + ' ' + c1.last_name customer_1,
	c2.first_name + ' ' + c2.last_name customer_2
	FROM
	sales.customers c1
	INNER JOIN sales.customers c2 ON c1.customer_id > c2.customer_id
	AND c1.city = c2.city
	ORDER BY
	city,
	customer_1,
	customer_2;

以下条件确保该语句不会与同一客户进行比较：

c1.customer_id > c2.customer_id

以下条件匹配两个客户的城市：

AND c1.city = c2.city

注意，如果把大于号(>)改成不等于(<>)符号，结果中将会包含更多行：

	SELECT
	c1.city,
	c1.first_name + ' ' + c1.last_name customer_1,
	c2.first_name + ' ' + c2.last_name customer_2
	FROM
	sales.customers c1
	INNER JOIN sales.customers c2 ON c1.customer_id <> c2.customer_id
	AND c1.city = c2.city
	ORDER BY
	city,
	customer_1,
	customer_2;
	```
	我们看看`ON`子句中的`>`和`<>`之间的区别，将其限制为一个城市，以便于比较。
	```sql
	SELECT
	customer_id, first_name + ' ' + last_name c,
	city
	FROM
	sales.customers
	WHERE
	city = 'Albany'
	ORDER BY
	c;

此查询在ON子句中使用（>）运算符:

	SELECT
	c1.city,
	c1.first_name + ' ' + c1.last_name customer_1,
	c2.first_name + ' ' + c2.last_name customer_2
	FROM
	sales.customers c1
	INNER JOIN sales.customers c2 ON c1.customer_id > c2.customer_id
	AND c1.city = c2.city
	WHERE c1.city = 'Albany'
	ORDER BY
	c1.city,
	customer_1,
	customer_2;

此查询在ON子句中使用（<>）运算符：

	SELECT
	c1.city,
	c1.first_name + ' ' + c1.last_name customer_1,
	c2.first_name + ' ' + c2.last_name customer_2
	FROM
	sales.customers c1
	INNER JOIN sales.customers c2 ON c1.customer_id <> c2.customer_id
	AND c1.city = c2.city
	WHERE c1.city = 'Albany'
	ORDER BY
	c1.city,
	customer_1,
	customer_2;

 

分类: SQL Server

标签: SQL Server , 数据库