sql server里如何给子查询设置别名

[fanzhouqi]

declare @sId uniqueidentifier, @free int ,@booked int, @bookingTimeUp datetime
set @sId = '8F0EC5C0-7839-4C24-8974-9738F2C0F1DA'
set @free = 1
set @booked = 2
set @bookingTimeUp = getdate()
select
t.TableName
from
[Table] t
inner join
[TableType] tt
on t.[TableType]=tt.ID
inner join
[Shop] s
on tt.[Shop]= s.ID
inner join
[BookingTable] bt
on t.ID=bt.[Table]
inner join
[Booking] b
on bt.[Booking]=b.ID
where s.ID=@sId and (
(t.State=@free) or
(t.State=@booked and @bookingTimeUp <
all(select BookingTime from [Booking] b1
inner join [BookingTable] bt2 on b1.ID = bt2.[Booking]
where bt2.[Table] = t.ID) A)
)

想将 select BookingTime from [Booking] b1
inner join [BookingTable] bt2 on b1.ID = bt2.[Booking]
where bt2.[Table] = t.ID
这个子查询设个别名，因为下面还要用到很多次，但我用上面的写法是报错的，不知道正确的应该怎么写？？？

[东那个升]

declare @sId uniqueidentifier, @free int ,@booked int, @bookingTimeUp datetime
set @sId = '8F0EC5C0-7839-4C24-8974-9738F2C0F1DA'
set @free = 1
set @booked = 2
set @bookingTimeUp = getdate()
select
t.TableName
from
[Table] t 
inner join
[TableType] tt
on t.[TableType]=tt.ID
inner join
[Shop] s 
on tt.[Shop]= s.ID 
inner join
[BookingTable] bt 
on t.ID=bt.[Table] 
inner join
[Booking] b 
on bt.[Booking]=b.ID 
where s.ID=@sId and (
(t.State=@free) or 
(t.State=@booked and @bookingTimeUp < 
(select min(BookingTime) from [Booking] b1 
inner join [BookingTable] bt2 on b1.ID = bt2.[Booking]
where bt2.[Table] = t.ID) ) 
)

[东那个升]

select min(BookingTime) from [Booking] b1 
inner join [BookingTable] bt2 on b1.ID = bt2.[Booking]
where bt2.[Table] = t.ID

[黄_瓜]

and @bookingTimeUp < all(
select BookingTime  from 
  (
       select BookingTime from [Booking] b1 
       inner join [BookingTable] bt2 on b1.ID = bt2.[Booking]
       where bt2.[Table] = t.ID
   ) A
)

[黄_瓜]

建议不要使用all
and @bookingTimeUp < (
select min(BookingTime)  from 
  (
       select BookingTime from [Booking] b1 
       inner join [BookingTable] bt2 on b1.ID = bt2.[Booking]
       where bt2.[Table] = t.ID
   ) A
)
--这样应该可以在其他地方使用表a
--你试试吧，不行就把数据插入到一个临时表或者便量表中使用

[黄_瓜]

建议不要使用all
and @bookingTimeUp < (
select min(BookingTime)  from 
  (
       select BookingTime from [Booking] b1 
       inner join [BookingTable] bt2 on b1.ID = bt2.[Booking]
       where bt2.[Table] = t.ID
   ) A
)
--这样应该可以在其他地方使用表a
--你试试吧，不行就把数据插入到一个临时表或者便量表中使用

[--小F--]

如果是2005的话 直接把select BookingTime from [Booking] b1
inner join [BookingTable] bt2 on b1.ID = bt2.[Booking]
where bt2.[Table] = t.ID放入CTE


2000的话放入临时表

[fanzhouqi]

to 5楼：你的解决方案在只能让A在一个地方出现，我在后面再引用A就报错了。

存到临时变量中不知该怎么写？因为这里的子查询是要根据父查询的ID进行查询的。

[Mr_Nice]

[Quote=引用 6 楼 fredrickhu 的回复:]
如果是2005的话 直接把select BookingTime from [Booking] b1
inner join [BookingTable] bt2 on b1.ID = bt2.[Booking]
where bt2.[Table] = t.ID放入CTE


2000的话放入临时表
[/Quote]

多次引用2005 用CTE
2000用临时表。