时间段查询（稍微有点复杂度） 欢迎（libin_ftsafe 前来看看）

Question

row_number(partition by 字段,order by 字段)

Answer 1

我先建好测试数据drop table test
create table test(ID int,HotelCode varchar(10),RoomType varchar(10),StartDate datetime,EndDate datetime,Price money,DayOfWeek int)                      
insert into test select 1 ,'00001',  'Twin'  ,'2009-01-01', '2009-03-01',200.00,1                                                                       
insert into test select 2 ,'00001',  'Twin'  ,'2009-01-01', '2009-03-01',200.00,2                                                                       
insert into test select 3 ,'00001',  'Twin'  ,'2009-01-01', '2009-03-01',200.00,3                                                                       
insert into test select 4 ,'00001',  'Twin'  ,'2009-01-01', '2009-03-01',200.00,4                                                                       
insert into test select 5 ,'00001',  'Twin'  ,'2009-01-01', '2009-03-01',200.00,5                                                                       
insert into test select 6 ,'00001',  'Twin'  ,'2009-01-01', '2009-03-01',250.00,6                                                                       
insert into test select 7 ,'00001',  'Twin'  ,'2009-01-01', '2009-03-01',250.00,7                                                                       
insert into test select 8 ,'00001',  'Twin'  ,'2009-03-04', '2009-03-06',300.00,3                                                                       
insert into test select 9 ,'00001',  'Twin'  ,'2009-03-04', '2009-03-06',300.00,4                                                                       
insert into test select 10,'00001',  'Twin'  ,'2009-03-04', '2009-03-06',300.00,5                                                                       
insert into test select 11,'00001',  'Twin'  ,'2009-03-07', '2009-05-06',300.00,1                                                                       
insert into test select 12,'00001',  'Twin'  ,'2009-03-07', '2009-05-06',300.00,2                                                                       
insert into test select 13,'00001',  'Twin'  ,'2009-03-07', '2009-05-06',300.00,3                                                                       
insert into test select 14,'00001',  'Twin'  ,'2009-03-07', '2009-05-06',300.00,4                                                                       
insert into test select 15,'00001',  'Twin'  ,'2009-03-07', '2009-05-06',300.00,5                                                                       
insert into test select 16,'00001',  'Twin'  ,'2009-03-07', '2009-05-06',350.00,6                                                                       
insert into test select 17,'00001',  'Twin'  ,'2009-03-07', '2009-05-06',350.00,7                                                                       
insert into test select 18,'00001',  'Single','2009-01-04', '2009-03-01',100.00,1                                                                       
insert into test select 19,'00001',  'Single','2009-01-04', '2009-03-01',100.00,2                                                                       
insert into test select 20,'00001',  'Single','2009-01-04', '2009-03-01',100.00,3                                                                       
insert into test select 21,'00001',  'Single','2009-01-04', '2009-03-01',100.00,4                                                                       
insert into test select 22,'00001',  'Single','2009-01-04', '2009-03-01',100.00,5                                                                       
insert into test select 23,'00001',  'Single','2009-01-04', '2009-03-01',150.00,6                                                                       
insert into test select 24,'00001',  'Single','2009-01-04', '2009-03-01',150.00,7 

Answer 2

insert into test select 25,'00001',  'Single','2009-03-02', '2009-03-05',80.00 ,1                                                                       
insert into test select 26,'00001',  'Single','2009-03-02', '2009-03-05',80.00 ,2                                                                       
insert into test select 27,'00001',  'Single','2009-03-02', '2009-03-05',80.00 ,3                                                                       
insert into test select 28,'00001',  'Single','2009-03-02', '2009-03-05',90.00 ,4                                                                       
insert into test select 29,'00001',  'Single','2009-03-06', '2009-05-03',90.00 ,1                                                                       
insert into test select 30,'00001',  'Single','2009-03-06', '2009-05-03',90.00 ,2                                                                       
insert into test select 31,'00001',  'Single','2009-03-06', '2009-05-03',90.00 ,3                                                                       
insert into test select 32,'00001',  'Single','2009-03-06', '2009-05-03',90.00 ,4                                                                       
insert into test select 33,'00001',  'Single','2009-03-06', '2009-05-03',90.00 ,5                                                                       
insert into test select 34,'00001',  'Single','2009-03-06', '2009-05-03',100.00,6                                                                       
insert into test select 35,'00001',  'Single','2009-03-06', '2009-05-03',100.00,7                                                                       
insert into test select 36,'00002',  'Twin'  ,'2009-01-01', '2009-03-01',200.00,1                                                                       
insert into test select 37,'00002',  'Twin'  ,'2009-01-01', '2009-03-01',200.00,2                                                                       
insert into test select 38,'00002',  'Twin'  ,'2009-01-01', '2009-03-01',200.00,3                                                                       
insert into test select 39,'00002',  'Twin'  ,'2009-01-01', '2009-03-01',200.00,4                                                                       
insert into test select 40,'00002',  'Twin'  ,'2009-01-01', '2009-03-01',200.00,5                                                                       
insert into test select 41,'00002',  'Twin'  ,'2009-01-01', '2009-03-01',200.00,6                                                                       
insert into test select 42,'00002',  'Twin'  ,'2009-01-01', '2009-03-01',200.00,7                                                                       
insert into test select 43,'00002',  'Twin'  ,'2009-03-02', '2009-04-03',300.00,1                                                                       
insert into test select 43,'00002',  'Twin'  ,'2009-03-02', '2009-04-03',300.00,2                                                                       
insert into test select 43,'00002',  'Twin'  ,'2009-03-02', '2009-04-03',300.00,3                                                                       
insert into test select 43,'00002',  'Twin'  ,'2009-03-02', '2009-04-03',300.00,4                                                                       
insert into test select 43,'00002',  'Twin'  ,'2009-03-02', '2009-04-03',300.00,5                                                                       
insert into test select 43,'00002',  'Twin'  ,'2009-03-02', '2009-04-03',350.00,6                                                                       
insert into test select 43,'00002',  'Twin'  ,'2009-03-02', '2009-04-03',350.00,7                                                                       
insert into test select 44,'00002',  'Single','2009-01-04', '2009-03-01',100.00,1                                                                       
insert into test select 45,'00002',  'Single','2009-01-04', '2009-03-01',100.00,2                                                                       
insert into test select 46,'00002',  'Single','2009-01-04', '2009-03-01',100.00,3                                                                       
insert into test select 47,'00002',  'Single','2009-01-04', '2009-03-01',100.00,4                                                                       
insert into test select 48,'00002',  'Single','2009-01-04', '2009-03-01',100.00,5                                                                       
insert into test select 49,'00002',  'Single','2009-01-04', '2009-03-01',160.00,6                                                                       
insert into test select 50,'00002',  'Single','2009-01-04', '2009-03-01',160.00,7                                                                       
insert into test select 51,'00002',  'Single','2009-03-04', '2009-04-03',150.00,1                                                                       
insert into test select 52,'00002',  'Single','2009-03-04', '2009-04-03',150.00,2                                                                       
insert into test select 53,'00002',  'Single','2009-03-04', '2009-04-03',150.00,3                                                                       
insert into test select 54,'00002',  'Single','2009-03-04', '2009-04-03',150.00,4                                                                       
insert into test select 55,'00002',  'Single','2009-03-04', '2009-04-03',150.00,5                                                                       
insert into test select 56,'00002',  'Single','2009-03-04', '2009-04-03',150.00,6                                                                       
insert into test select 57,' 0000',  'Single','2009-03-04', '2009-04-03',150.00,7
select * from test                                                                       

Answer 3

select ID,   HotelCode,   RoomType,    StratDate,     EndDate,       Price,    row_number(partition by StratDate,order by ID)

Answer 4

select ID, HotelCode, RoomType, StratDate, EndDate, Price, row_number(partition by StratDate,order by ID) DayOfWeek
from Tab 

Answer 5

select *
from 
(select *,ROW_NUMBER()  over (partition by taoistong order by hotelcode ) 'taoistong2' 
from (select *,'1' 'taoistong' from test) a
where isnull(EndDate,'2020-01-01')>='2009-03-01' and StartDate<='2009-03-06'
) a
where 
taoistong2 between 1 and 10
--taoistong2 between 11 and 20
--taoistong2 between 21 and 30
第一个答案

Answer 6

SQL不太精通，就提点儿个人的看法：
1)要求查询时间段 (2009-03-01--2009-03-06)内存在价格的酒店及其房型，只要时间段的两个端点有一个在StartTime和EndTime之间，就满足这个条件；至于后面那个分页的条件，我想找出了数据后就简单了
2) 第二个要求没有思路，不过已经找出了满足条件的记录，再做这个应该也不会太难!!

Answer 7

select hotelcode,roomtype,
where isnull(EndDate,'2020-01-01')>='2009-03-01' and StartDate<='2009-03-06'drop table #taoistong
create table #taoistong (date_ smalldatetime)
insert into #taoistong 
select '2009-03-06' union all 
select '2009-03-05' union all 
select '2009-03-04' union all 
select '2009-03-03' union all 
select '2009-03-02' union all 
select '2009-03-01'
select * from 
(select case DATENAME(DW,date_) when 'Friday' then 5 when 'Thursday' then 4 when 'Wednesday' then 3 when 'Tuesday' then 2
when 'Monday' then 1 when 'Saturday' then 6 when 'Sunday' then 7 end 'week',* 
from #taoistong where date_ between '2009-03-01' and '2009-03-06') a,test b
where a.week=b.DayOfWeek
and a.date_ between b.StartDate and ISNULL(b.EndDate,'2020-01-01')
order by HotelCode ,RoomType,date_第二题