SQL（笔试题）网站上的广告位是轮播的，每天某一广告位最多可轮播的广告数量是有限制的，比如A广告位，每天只能轮播三个广告，但销售人员在销售广告位时并不考虑此限制，要求查询出合同表中，超过广告位轮播数量的合同。

合同表 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来讲，轮播情况如下表

OrderID	2006-11-01	2006-11-02	2006-11-03	2006-11-04	2006-11-05
1
4
7
8

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

方法一：可以创建多个临时表来完成

--先创建一张临时日期表，来记录合约中所有广告位的日期

CREATE TABLE #calendar (d date);

DECLARE @stime date

DECLARE @etime date

set @stime=(select min(Startdate) from Orders);

set @etime=(select max(Enddate) from Orders);

--这种语法是用来提供通用表达式的

--用到批次添加多条记录（从最早的一天到最晚的一天）

WITH a1 AS

(

SELECT @stime as d

UNION ALL

SELECT DATEADD(dd,1,d) FROM a1 where a1.d<@etime

)

INSERT #calendar

SELECT d FROM a1

select * from #calendar

--创建临时表#B存放广告位、日期、合同号

select a.* into #B from(select Orders.Positioncode,c.d,Orders.OrderID

from #calendar c,Orders

where c.d between Orders.Startdate and Orders.Enddate)as  a

 

select * from #B

--查询结果

--选出超出当天对应广告位轮播数量的日期和对应的广告位，将它存到临时表#D中

select s.* into #D from(select t.d,t.Positioncode from

(select d,Positioncode,COUNT(*)as c from #B  group by d ,Positioncode) as t

inner join Product on t.Positioncode=Product.Positioncode

where t.c>Product.Showcount)  as s

 

select * from #D

--得到各销售人员每天销售的广告位，将它放到一张临时表中#C

 

select s.* into #C from (SELECT
d
,Positioncode,stuff((
SELECT
','+convert(varchar(50),OrderID)
FROM dbo.#B AS tb01 where tb01.d=tb02.d and tb01.Positioncode=tb02.Positioncode
FOR xml PATH('')),1,1,'') AS classList
FROM dbo.#B AS tb02
GROUP BY Positioncode,d) as s

select * from #C

(注意用：MySql中group_concat函数很方便） 

得到结果：

--结合临时表#C、#D得到最终结果
select #C.* from #C,#D where #C.d=#D.d and #C.Positioncode=#D.Positioncode;

 

第一种方法看起来很简单，但里面涉及到的知识点多，以后有时间再整理。

想办法用存储过程等高级语法来实现