根据时间段查询价格,将价格按周一到周日排列出来 最好写成存储过程那种
表数据结构如下: 表 3.5 :HotelRoomPrice (房型价格表)
ID 字段名 类型 默认值 说明 备注
1 ID Int(4) 自动编号
2 RoomID Varchar(50) 房型编号
3 RoomType Varchar(50) 房型
4 WeekendMode Int(4) 0 周末方式 0.周日周六为周末、
1.周五周六为周末、2.无周末方式
5 StartTime datetime 开始时间 价格政策时段
6 EndTime datetime 结束时间
7 CounterPrice money 门市价格
8 SelldayPrice money 平日销售价
9 SellWeeksPrice money 周末销售价
10 ReturnPrice money 返佣金
11 Priority int 优先级
12 EditDate datetime 修改日期 建表语句 IF OBJECT_ID('HotelRoomPrice') IS NOT NULL
DROP TABLE HotelRoomPrice
GO
CREATE TABLE [dbo].[HotelRoomPrice] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[RoomID] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[RoomType] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[WeekendMode] [int] null,
[StartTime] [datetime] NULL ,
[EndTime] [datetime] NULL ,
[CounterPrice] [money] NULL ,
[SelldayPrice] [money] NULL ,
[SellweekendPrice] [money] null,
[ReturnPrice] [money] NULL ,
[Editdate] [datetime] NULL ,
) ON [PRIMARY]
GO insert HotelRoomPrice SELECT
'H002T01' ,'豪华总统套房',0, '2009-07-20' ,'2009-07-26' , 2088 ,900,600,25,0,'2009-07-26'UNION ALL SELECT
'H002T01' ,'豪华总统套房',0,'2009-02-27' , '2009-08-02' , 2088 ,900,600,25,1,'2009-07-27'UNION ALL SELECT
'H002T01' ,'豪华总统套房',1, '2009-08-02' , '2009-08-09' ,2088 ,900,678,25,2,'2009-07-28'UNION ALL SELECT
'H002T01' ,'豪华总统套房',1,'2009-08-10' , '2009-08-16' , 2088 ,900,678,25,3,'2009-07-28'UNION ALL SELECT
'H002T01' ,'豪华总统套房',2,'2009-08-18' , '2009-08-23' , 2088 ,900,589,25,4,'2009-07-30'UNION ALL SELECT
'H002T01' ,'豪华总统套房',2,'2009-08-25' , '2009-08-31' , 2088 ,900,589,25,5,'2009-07-30'UNION ALL SELECT
'H002T01' ,'豪华总统套房',0,'2009-08-17' , '2010-11-30' , 2088 ,900,589,25,6,'2009-07-30'UNION ALL SELECT
'H002T02' ,'高级商务房', 0,'2009-06-1' , '2009-06-30' , 1600 ,499,368,20,7,'2009-07-25'
go 根据时间段 和 不同的周末方式 显示周一到周日 价格的不同 如:2009-08-02 2009-08-09
查询出来的结果为
周日 周一 周二 周三 周四 周五 周六
900 900 900 900 900 678 678
如果日期相同 价格 根据优先级最大的筛选
表数据结构如下: 表 3.5 :HotelRoomPrice (房型价格表)
ID 字段名 类型 默认值 说明 备注
1 ID Int(4) 自动编号
2 RoomID Varchar(50) 房型编号
3 RoomType Varchar(50) 房型
4 WeekendMode Int(4) 0 周末方式 0.周日周六为周末、
1.周五周六为周末、2.无周末方式
5 StartTime datetime 开始时间 价格政策时段
6 EndTime datetime 结束时间
7 CounterPrice money 门市价格
8 SelldayPrice money 平日销售价
9 SellWeeksPrice money 周末销售价
10 ReturnPrice money 返佣金
11 Priority int 优先级
12 EditDate datetime 修改日期 建表语句 IF OBJECT_ID('HotelRoomPrice') IS NOT NULL
DROP TABLE HotelRoomPrice
GO
CREATE TABLE [dbo].[HotelRoomPrice] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[RoomID] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[RoomType] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[WeekendMode] [int] null,
[StartTime] [datetime] NULL ,
[EndTime] [datetime] NULL ,
[CounterPrice] [money] NULL ,
[SelldayPrice] [money] NULL ,
[SellweekendPrice] [money] null,
[ReturnPrice] [money] NULL ,
[Editdate] [datetime] NULL ,
) ON [PRIMARY]
GO insert HotelRoomPrice SELECT
'H002T01' ,'豪华总统套房',0, '2009-07-20' ,'2009-07-26' , 2088 ,900,600,25,0,'2009-07-26'UNION ALL SELECT
'H002T01' ,'豪华总统套房',0,'2009-02-27' , '2009-08-02' , 2088 ,900,600,25,1,'2009-07-27'UNION ALL SELECT
'H002T01' ,'豪华总统套房',1, '2009-08-02' , '2009-08-09' ,2088 ,900,678,25,2,'2009-07-28'UNION ALL SELECT
'H002T01' ,'豪华总统套房',1,'2009-08-10' , '2009-08-16' , 2088 ,900,678,25,3,'2009-07-28'UNION ALL SELECT
'H002T01' ,'豪华总统套房',2,'2009-08-18' , '2009-08-23' , 2088 ,900,589,25,4,'2009-07-30'UNION ALL SELECT
'H002T01' ,'豪华总统套房',2,'2009-08-25' , '2009-08-31' , 2088 ,900,589,25,5,'2009-07-30'UNION ALL SELECT
'H002T01' ,'豪华总统套房',0,'2009-08-17' , '2010-11-30' , 2088 ,900,589,25,6,'2009-07-30'UNION ALL SELECT
'H002T02' ,'高级商务房', 0,'2009-06-1' , '2009-06-30' , 1600 ,499,368,20,7,'2009-07-25'
go 根据时间段 和 不同的周末方式 显示周一到周日 价格的不同 如:2009-08-02 2009-08-09
查询出来的结果为
周日 周一 周二 周三 周四 周五 周六
900 900 900 900 900 678 678
如果日期相同 价格 根据优先级最大的筛选
11、 Priority int 优先级
IF OBJECT_ID('HotelRoomPrice') IS NOT NULL
DROP TABLE HotelRoomPrice
GO
CREATE TABLE [dbo].[HotelRoomPrice] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[RoomID] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[RoomType] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[WeekendMode] [int] null,
[StartTime] [datetime] NULL ,
[EndTime] [datetime] NULL ,
[CounterPrice] [money] NULL ,
[SelldayPrice] [money] NULL ,
[SellweekendPrice] [money] null,
[ReturnPrice] [money] NULL ,
[priority][int] null, /*优先级*/
[Editdate] [datetime] NULL ,
) ON [PRIMARY]
GOinsert HotelRoomPrice SELECT
'H002T01' ,'豪华总统套房',0, '2009-07-20' ,'2009-07-26' , 2088 ,900,600,25,0,'2009-07-26'UNION ALL SELECT
'H002T01' ,'豪华总统套房',0,'2009-02-27' , '2009-08-02' , 2088 ,900,600,25,1,'2009-07-27'UNION ALL SELECT
'H002T01' ,'豪华总统套房',1, '2009-08-02' , '2009-08-09' ,2088 ,900,678,25,2,'2009-07-28'UNION ALL SELECT
'H002T01' ,'豪华总统套房',1,'2009-08-10' , '2009-08-16' , 2088 ,900,678,25,3,'2009-07-28'UNION ALL SELECT
'H002T01' ,'豪华总统套房',2,'2009-08-18' , '2009-08-23' , 2088 ,900,589,25,4,'2009-07-30'UNION ALL SELECT
'H002T01' ,'豪华总统套房',2,'2009-08-25' , '2009-08-31' , 2088 ,900,589,25,5,'2009-07-30'UNION ALL SELECT
'H002T01' ,'豪华总统套房',0,'2009-08-17' , '2010-11-30' , 2088 ,900,589,25,6,'2009-07-30'UNION ALL SELECT
'H002T02' ,'高级商务房', 0,'2009-06-1' , '2009-06-30' , 1600 ,499,368,20,7,'2009-07-25'
go
select @startTime='2009-08-02', @endTime='2009-08-26'declare @t table(d datetime,weekday int)while @startTime<=@endTime
begin
insert into @t values(@startTime,datepart(weekday,@startTime))
set @startTime=@startTime+1
endselect A.d Date,Datename(weekday,A.d) weekDay,
case when [WeekendMode]=1 then
(case when weekday=7 or weekday=1 then [SellweekendPrice]
else [SelldayPrice] end)
when [WeekendMode]=2 then
(case when weekday=6 or weekday=7 then [SellweekendPrice]
else [SelldayPrice] end)
else [SellweekendPrice] end as price
from @t A left join HotelRoomPrice B
on A.d>=B.[StartTime] and A.d<=B.[EndTime] where B.RoomID='H002T01'go这个可以查询出 周一到周日的 结果 不过时间重复的话 数据会出现多条你请大虾们没优化一下吧
declare @startTime as datetime,@endTime as datetime
select @startTime='2009-08-02', @endTime='2009-08-26' declare @t table(d datetime,weekday int) while @startTime <=@endTime
begin
insert into @t values(@startTime,datepart(weekday,@startTime))
set @startTime=@startTime+1
end
;
with temp as
(
select [priority],A.d Date,Datename(weekday,A.d) weekDay,
case when [WeekendMode]=1 then
(case when weekday=7 or weekday=1 then [SellweekendPrice]
else [SelldayPrice] end)
when [WeekendMode]=2 then
(case when weekday=6 or weekday=7 then [SellweekendPrice]
else [SelldayPrice] end)
else [SellweekendPrice] end as price
from @t A left join HotelRoomPrice B
on A.d>=B.[StartTime] and A.d <=B.[EndTime]
where B.RoomID='H002T01'
)
select Date,weekDay,price from temp C
where not exists(select 1 from temp where C.Date=Date and [priority]>C.[priority])Date weekDay price
----------------------- ------------------------------ ---------------------
2009-08-02 00:00:00.000 星期日 678.00
2009-08-03 00:00:00.000 星期一 900.00
2009-08-04 00:00:00.000 星期二 900.00
2009-08-05 00:00:00.000 星期三 900.00
2009-08-06 00:00:00.000 星期四 900.00
2009-08-07 00:00:00.000 星期五 900.00
2009-08-08 00:00:00.000 星期六 678.00
2009-08-09 00:00:00.000 星期日 678.00
2009-08-10 00:00:00.000 星期一 900.00
2009-08-11 00:00:00.000 星期二 900.00
2009-08-12 00:00:00.000 星期三 900.00
2009-08-13 00:00:00.000 星期四 900.00
2009-08-14 00:00:00.000 星期五 900.00
2009-08-15 00:00:00.000 星期六 678.00
2009-08-16 00:00:00.000 星期日 678.00
2009-08-17 00:00:00.000 星期一 589.00
2009-08-18 00:00:00.000 星期二 589.00
2009-08-19 00:00:00.000 星期三 589.00
2009-08-20 00:00:00.000 星期四 589.00
2009-08-21 00:00:00.000 星期五 589.00
2009-08-22 00:00:00.000 星期六 589.00
2009-08-23 00:00:00.000 星期日 589.00
2009-08-24 00:00:00.000 星期一 589.00
2009-08-25 00:00:00.000 星期二 589.00
2009-08-26 00:00:00.000 星期三 589.00
在Sql server 2000中可以使用吗?
你这里通过A.d>=B.[StartTime] and A.d <=B.[EndTime]是不行的
应该是时间段间相互自比较如果出现重复日期,则该段时间优先级高者取值。
如果用自连接,会有N种情况。
declare @startTime as datetime,@endTime as datetime
select @startTime='2009-08-02', @endTime='2009-08-26' declare @t table(d datetime,weekday int) while @startTime <=@endTime
begin
insert into @t values(@startTime,datepart(weekday,@startTime))
set @startTime=@startTime+1
end select [priority],A.d Date,Datename(weekday,A.d) weekDay,
case when [WeekendMode]=1 then
(case when weekday=7 or weekday=1 then [SellweekendPrice]
else [SelldayPrice] end)
when [WeekendMode]=2 then
(case when weekday=6 or weekday=7 then [SellweekendPrice]
else [SelldayPrice] end)
else [SellweekendPrice] end as price
into #
from @t A left join (select * from HotelRoomPrice where RoomID='H002T01') B
on A.d>=B.[StartTime] and A.d <=B.[EndTime] select Date,weekDay,price from # as C
where not exists(select 1 from # where Date=C.Date and [priority]>C.[priority])
drop table #