SQL Server 递归查询

发布于 2024-09-27 07:14:05 字数 878 浏览 1 评论 0原文

我是 SQL Server 开发新手。我的大部分经验都是通过 Oracle 完成的。

假设我有包含约会对象的下表，

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。

我想查询以获取某人的约会历史记录。例如，如果 ID = 1 的约会被推迟两次，并且这些推迟为同一 PersonID 创建了 ID = 7 和 ID = 12 的约会，我想进行一个返回以下结果的查询：

AppointmentID         PrevAppointmentID
-----------------    ----------------------
1                     NULL
7                     1
12                    7

如果使用 Oracle我记得可以使用 CONNECT BY PRIOR 子句获得类似的东西。

有什么办法可以通过查询来达到这些结果吗？

我正在使用 SQL Server 2005/2008。

提前致谢

原文

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)

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.

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

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?

I am using SQL Server 2005/2008.

thanks in advance

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

长发绾君心 2024-10-04 07:14:05

研究使用所谓的 CTE（公用表表达式）（请参阅 MSDN 文档):

;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

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

回复收藏 0 原文

~没有更多了~