1.现有广告合同表Orders,表示广告在哪个广告位的哪几天需要播出 OrderIDPositioncodeStartdateEnddate1A2015-11-012015-11-032C2015-11-022015-11-033B2015-11-012015-11-044A2 ...
1.
现有广告合同表Orders,表示广告在哪个广告位的哪几天需要播出
OrderID | Positioncode | Startdate | Enddate |
1 | A | 2015-11-01 | 2015-11-03 |
2 | C | 2015-11-02 | 2015-11-03 |
3 | B | 2015-11-01 | 2015-11-04 |
4 | A | 2015-11-03 | 2015-11-04 |
5 | C | 2015-11-01 | 2015-11-02 |
6 | B | 2015-11-02 | 2015-11-05 |
7 | A | 2015-11-02 | 2015-11-03 |
8 | A | 2015-11-04 | 2015-11-05 |
9 | C | 2015-11-03 | 2015-11-04 |
10 | C | 2015-11-02 | 2015-11-04 |
产品表 Product,表示每个广告位每天最多播几个广告
Positioncode | Showcount |
A | 2 |
B | 1 |
C | 3 |
要求查询出合同表中,超过广告位轮播数量的合同
Declare @Dup table (TmpDate datetime) Declare @minDate datetime,@maxDate datetime SELECT @minDate=MIN(StartDate),@maxDate=Max(EndDate) FROM Orders WHILE @minDate<=@MaxDate BEGIN INSERT INTO @Dup VALUES (@minDate) SET @minDate=@minDate+1 END SELECT distinct aa.* FROM Orders aa INNER JOIN ( SELECT Positioncode,tmpdate,count(*) as cnt FROM Orders a,@Dup b where tmpdate between a.startdate and a.enddate group by Positioncode,tmpdate )bb ON aa.PositionCode=bb.PositionCode AND bb.tmpDate Between aa.StartDate AND aa.ENdDate INNER JOIN Product cc ON bb.PositionCode=cc.PositionCode WHERE bb.cnt>cc.showcountOrder by PositionCode
原标题:面试题整理:SQL(二)
关键词:sql
*特别声明:以上内容来自于网络收集,著作权属原作者所有,如有侵权,请联系我们:
admin#shaoqun.com
(#换成@)。