sql面试题4

合同表 Orders

OrderID	Positioncode	Startdate	Enddate
1	A	2006-11-01	2006-11-03
2	C	2006-11-02	2006-11-03
3	B	2006-11-01	2006-11-04
4	A	2006-11-03	2006-11-04
5	C	2006-11-01	2006-11-02
6	B	2006-11-02	2006-11-05
7	A	2006-11-02	2006-11-03
8	A	2006-11-04	2006-11-05
9	C	2006-11-03	2006-11-04
10	C	2006-11-02	2006-11-04

广告位表 Product

Positioncode	Showcount
A	2
B	1
C	3

说明：对于广告位A来讲，轮播情况如下表
广告位A每天最多可轮播2个广告，但合同表中在2006-11-03这天有三个广告（1、4、7），对于广告位A，1、4、7则是最终需要得到的结果。如需要可使用临时表、存储过程等

-- 我搞了2张临时表
Create table #Orders  
(
  OrderID        int ,
  Positioncode   char(1),
  StartDate      datetime,
  EndDate        datetime
)
Go
Insert  into  #Orders(OrderId,Positioncode,StartDate,EndDate)  values(1,'A','2006/11/01','2006/11/03')
Insert  into  #Orders(OrderId,Positioncode,StartDate,EndDate)  values(2,'C','2006/11/02','2006/11/03')
Insert  into  #Orders(OrderId,Positioncode,StartDate,EndDate)  values(3,'B','2006/11/01','2006/11/04')
Insert  into  #Orders(OrderId,Positioncode,StartDate,EndDate)  values(4,'A','2006/11/03','2006/11/04')
Insert  into  #Orders(OrderId,Positioncode,StartDate,EndDate)  values(5,'C','2006/11/01','2006/11/02')
Insert  into  #Orders(OrderId,Positioncode,StartDate,EndDate)  values(6,'B','2006/11/02','2006/11/05')
Insert  into  #Orders(OrderId,Positioncode,StartDate,EndDate)  values(7,'A','2006/11/02','2006/11/03')
Insert  into  #Orders(OrderId,Positioncode,StartDate,EndDate)  values(8,'A','2006/11/04','2006/11/05')
Insert  into  #Orders(OrderId,Positioncode,StartDate,EndDate)  values(9,'C','2006/11/03','2006/11/04')
Insert  into  #Orders(OrderId,Positioncode,StartDate,EndDate)  values(10,'C','2006/11/02','2006/11/04')
Go  
Create   table  #Product
(
   Positioncode  char(1),
   Showcount     int
)
Go
insert into  #Product(Positioncode,showcount) values('A',2)
insert into  #Product(Positioncode,showcount) values('B',1)
insert into  #Product(Positioncode,showcount) values('C',3)
Go

--开始做
create table #date  -- 增加一张 #date 临时表
(
  orderDate  datetime
)
 
declare @enddate datetime
declare @d1      datetime
 
select  @d1=min(StartDate),@enddate=max(EndDate)  from #Orders
 
while  @d1<=@enddate
 begin
   insert into #date(orderdate) values(@d1)
   set @d1=@d1+1
 end
 
 

Select  distinct   A.OrderId, A.Positioncode,startDate,EndDate
From  #Orders A
inner join 
(
   select  A.PositionCode,B.OrderDate,ShowCount=Count(*)   from  #Orders A
                inner join #date  B  on  B.OrderDate>=A.StartDate  and B.OrderDate<=A.EndDate
                Group  by  A.PositionCode,B.OrderDate
) B on  A.PositionCode=B.PositionCode   and  B.OrderDate>=A.StartDate  and  B.OrderDate<=A.EndDate
inner join #Product  C  on  b.positionCode=c.positionCode
where  b.ShowCount>c.ShowCount