面试题整理：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.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.

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

请注意：该表中有很多科目，都有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='101' 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...