SQL自连接

最新推荐文章于 2023-12-11 15:43:15 发布

小博

最新推荐文章于 2023-12-11 15:43:15 发布

阅读量622

点赞数

分类专栏：数据库技术文章标签： sql windows system border table class

数据库技术专栏收录该内容

14 篇文章 0 订阅

订阅专栏

一个表与自身进行连接，称为自连接
有一个学生表,里面有学号功课编号学生成绩三个字段.

用一个SQL查询语句得出每门功课成绩最好的前两名

学号功课编号学生成绩
1 1 99
2 1 98
3 1 100
4 2 88
5 2 87
6 2 88
7 3 99
8 3 88
9 3 100

解决方法
SELECT DISTINCT 学生表1.*
FROM 学生表学生表1 where 学生表1.学号 IN
(SELECT TOP 2 学生表.学号
FROM 学生表
WHERE 学生表.功课编号 = 学生表1.功课编号
ORDER BY 学生成绩 DESC)

查询结果
学号功课编号学生成绩
1 1 99
2 1 98
4 2 88
6 2 88
7 3 99
9 3 100
同样的问题还有

查找不同课程成绩相同的学生的学号、课程号、学生成绩

另一个问题：

表名topic

字段：id（递增）、 title、 fid（父id） typeid
1 1标题 0 1
2 2标题 1 2
3 3标题 0 1
4 4标题 1 1
5 5标题 3 1
我想查询 topic 表中所有typeid=1 的 id字段以及 fid等于这个记录的id记录的数目(即行数)

执行应该是
1 2
3 1
4 0
5 0

SELECT ID,(SELECT COUNT(*) FROM topic t2 WHERE t2.fid=t1.ID) AS mycount

FROM topic t1 WHERE typeid=1

7.2.4 自连接表进行查询

在信息查询时，有时需要将表与其自身进行连接，即自连接，这就需要为表定义别名。下面通过一个具体的实例讲解自连接的使用及实现机理。

实例5 表的自连接的使用

如果需要从ReaderInfo表查询可借图书数目比编号为9704读者多的所有读者信息，这时就可采用表的自连接方式实现，实现代码如下：

USE Library
SELECT R1.readerid,R1.readername,R1.unit,R1.bookcount
FROM ReaderInfo AS R1,ReaderInfo AS R2
WHERE R2.readerid=9704
AND R1.bookcount>R2.bookcount       --连接关系
ORDER BY R1.bookcount

实例运行结果如图7.4所示。

图7.4 实例运行结果

在代码中，FROM子句中的两个表实际上都是表ReaderInfo。为了独立地使用它们，采用上节介绍的表别名方法，分别为其定义别名R1和R2。这样就可以在WHERE子句中，使用R2查询编号为9704读者信息；而在SELECT子句中，使用R1查询满足条件的结果。下面简单介绍一下实例代码的执行过程。

DBMS首先执行FROM子句，将ReaderInfo表R1与它自身R2的笛卡尔积，作为中间表。实际上，该中间表的每一条记录包含两部分信息：一部分是R1的记录，另一部分是R2的记录。

而后执行WHERE子句，在中间表中，搜索R2部分中编号为9704的读者记录（假定为X），同时要求R1表记录中的bookcount字段的值要大于R2表中X记录的bookcount字段值。

★ 注意 ★

这里的R1和R2虽然名称不同，但表示的是同一个表ReaderInfo。


最后执行SELECT语句，从中间表获取S1中相应的信息作为结果表。

当然，上面的实例也可以分两步来实现。

实例6 分步实现实例5

如果不采用表的自连接方法，而采用常规方式，可分两步实现实例5。

从ReaderInfo表中，查询编号为9704读者的可借图书数目。代码如下：

USE Library
SELECT bookcount
FROM ReaderInfo 
WHERE readerid=9704

运行代码，结果如图7.5所示。

图7.5 代码运行结果

根据第步得到的结果值，从ReaderInfo表中查询可借图书数目大于该值的读者信息。代码如下：

<table width="400" border="1" 
cellspacing="0" cellpadding="2" 
bordercolorlight="black" bordercolordark="#FFFFFF"

align="center"><tr><td bgcolor="e6e6e6" class="code"><pre>
Win98系统：c:\Windows　 c:\Windows\system
Winnt和Win2000系统：c:\Winntc:\Winnt\system32
Winxp系统：c:\Windows　 c:\Windows\system32      
</pre></td></tr></table>
USE Library
SELECT readerid,readername,unit,bookcount
FROM ReaderInfo
WHERE bookcount>1
ORDER BY bookcount

运行代码，结果如图7.6所示。

图7.6 代码运行结果

可见，与实例5得到了相同的结果。但与采用自连接的方式相比，这种方法需要对中间结果进行人工干预，显然不利于程序中的自动处理操作。另外，还可以采用子查询的方法实现该实例。

实例7 采用子查询的方法实现实例5

本实例将采用子查询的方法实现实例5，实例代码如下：

USE Library
SELECT readerid,readername,unit,bookcount
FROM ReaderInfo
WHERE bookcount>(SELECT bookcount     --使用子查询
FROM ReaderInfo 
WHERE readerid=9704)
ORDER BY bookcount

}运行代码，得到如图7.7所示的结果。