你的位置:首页 > 数据库

[数据库]面试题整理:SQL(二)


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

 

OrderId  PositionCode  StartDate  EndDate  
1A2015-11-012015-11-03
4A2015-11-032015-11-04
7A2015-11-022015-11-03
3B2015-11-012015-11-04
6B2015-11-022015-11-05

 

 

 

 

 

 

2.

*请用SQL语句实现:从T_GetLargerDebitOccur数据表中查询出所有月份的发生额都比101科目相应月份的发生额高的记录。

请注意:该表中有很多科目,都有1-12月份的发生额。 

  AccID:科目代码,Occmonth:发生额月份,  DebitOccur:发生额。

AccId  Occmonth  DebitOccur  
1011100
1021200
1031300
1012400
1022300
1032500
1013300
1043400
NULLNULLNULL

 

 

 

 

 

 

 

 

 

select a.* from T_GetLargerDebitOccur a ,(select Occmonth,max(DebitOccur) Debit101ccur from T_GetLargerDebitOccur where AccID='101' group by Occmonth) b where a.Occmonth=b.Occmonth and a.DebitOccur>b.Debit101ccur

 

结果:

AccId  Occmonth  DebitOccur  
1021200
1031300
1042500
1053400

 

 

 

 

 

 

 

 

 

 

 

To be continue...