7.12 SQL Server自连接
SQL Server Self Join
简介
自连接将表连接到自身。它有助于查询层级数据或比较同一表中的行。
自联接使用内联接(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;
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· DeepSeek “源神”启动!「GitHub 热点速览」
· C# 集成 DeepSeek 模型实现 AI 私有化(本地部署与 API 调用教程)
· DeepSeek R1 简明指南:架构、训练、本地部署及硬件要求
· NetPad:一个.NET开源、跨平台的C#编辑器