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.showcount
Order by PositionCode
| OrderId | PositionCode | StartDate | EndDate |
| 1 | A | 2015-11-01 | 2015-11-03 |
| 4 | A | 2015-11-03 | 2015-11-04 |
| 7 | A | 2015-11-02 | 2015-11-03 |
| 3 | B | 2015-11-01 | 2015-11-04 |
| 6 | B | 2015-11-02 | 2015-11-05 |
2.
请注意：该表中有很多科目，都有1－12月份的发生额。
AccID：科目代码，Occmonth：发生额月份， DebitOccur：发生额。
| AccId | Occmonth | DebitOccur |
| 101 | 1 | 100 |
| 102 | 1 | 200 |
| 103 | 1 | 300 |
| 101 | 2 | 400 |
| 102 | 2 | 300 |
| 103 | 2 | 500 |
| 101 | 3 | 300 |
| 104 | 3 | 400 |
| NULL | NULL | NULL |
select a.* from T_GetLargerDebitOccur a
,(select Occmonth,max(DebitOccur) Debit101ccur from T_GetLargerDebitOccur where AccID='' group by Occmonth) b
where a.Occmonth=b.Occmonth and a.DebitOccur>b.Debit101ccur
结果：
| AccId | Occmonth | DebitOccur |
| 102 | 1 | 200 |
| 103 | 1 | 300 |
| 104 | 2 | 500 |
| 105 | 3 | 400 |
To be continue...