我是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的appoinment被推迟两次,并且这些延迟已经为同一PersonID创建了ID = 7和ID = 12的约会,我想进行一个查询返回以下结果：
AppointmentID PrevAppointmentID ----------------- ---------------------- 1 NULL 7 1 12 7
如果使用Oracle,我记得可以使用CONNECT BY PRIOR子句获得类似的东西.
有没有办法进行查询来实现这些结果？
我正在使用sql Server 2005/2008.
提前致谢
解决方法
看看使用什么叫CTE(普通表表达式)：
;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
