SQL Server 递归查询 [英] SQL Server recursive query
问题描述
我是 SQL Server 开发的新手.我的大部分经验都是在 Oracle 上完成的.
I am new to SQL Server development. Most of my experience has been done with Oracle.
假设我有包含约会对象的下表
suppose I have the following table that contains Appointments objects
CREATE TABLE [dbo].[Appointments](
[AppointmentID] [int] IDENTITY(1,1) NOT NULL,
.......
[AppointmentDate] [datetime] NOT NULL,
[PersonID] [int] NOT NULL,
[PrevAppointmentID] [int] NULL,
CONSTRAINT [PK_Appointments] PRIMARY KEY CLUSTERED ([AppointmentID] ASC)
约会可以推迟,因此,当发生这种情况时,会在表上创建一个新行,其中 PrevAppointmentID 字段包含原始约会的 ID.
An appointment can be postponed so, when this happens, a new row is created on the table with the PrevAppointmentID field containing the ID of the original Appointment.
我想查询以获取个人约会的历史记录.例如,如果 ID = 1 的约会被推迟了两次,并且这些推迟已经为同一个 PersonID 创建了 ID = 7 和 ID = 12 的约会,我想进行一个返回以下结果的查询:
I would like to make a query to obtain the history of a Person appointments. For example, if the appoinment with ID = 1 is postponed two times, and these postponements have created appointments with ID = 7 and ID = 12 for the same PersonID, I would like to make a query that returns the following results:
AppointmentID PrevAppointmentID
----------------- ----------------------
1 NULL
7 1
12 7
如果使用 Oracle,我记得可以使用 CONNECT BY PRIOR 子句获得类似的内容.
If using Oracle I remember that something like this can be obtained using the CONNECT BY PRIOR clause.
有什么办法可以通过查询来实现这些结果?
Is there any way to make a query to achieve these results?
我使用的是 SQL Server 2005/2008.
I am using SQL Server 2005/2008.
提前致谢
推荐答案
研究使用所谓的 CTE(公用表表达式)(请参阅 MSDN 文档):
Look into using what is called a CTE (common table expression) (Refer to MSDN document):
;with cteAppointments as (
select AppointmentID, PersonID, PrevAppointmentID
from Appointments
where PrevAppointmentID is null
union all
select a.AppointmentID, a.PersonID, a.PrevAppointmentID
from Appointments a
inner join cteAppointments c
on a.PrevAppointmentID = c.AppointmentID
)
select AppointmentID, PrevAppointmentID
from cteAppointments
where PersonID = xxx
这篇关于SQL Server 递归查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!