前两天收到一家知名互联网公司的offer（具体哪家公司就不说了^_^），是去做公司内部的MIS系统，使用Asp或Java语言，虽然本人对ASP比较熟，但最近两年一直在做.NET，本来是不想去的，后来想想还是去看看吧。

      打电话通知面试的时候就已说明，要求上机做两道Sql Server面试题。

第一题比较简单，查询出销售表中，销售额大于本地区平均水平的记录，用一条sql语句就搞定了。

Sales表

Sql语句：select * from sales as s inner join (select avg(total) as avge,region from sales group by region) avgtable on s.region = avgtable.region where total > avgtable.avge 

第二题就比较麻烦了，他们公司网站上的广告位是轮播的，每天某一广告位最多可轮播的广告数量是有限制的，比如A广告位，每天只能轮播三个广告，但销售人员在销售广告位时并不考虑此限制，要求查询出合同表中，超过广告位轮播数量的合同。

合同表 Orders

广告位表 Product

说明：对于广告位A来讲，轮播情况如下表

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

可能当时也有点紧张吧，这道题面试的时候弄了两个多小时，还是没有解决，最终只好放弃了。不过还是不死心，回家后又仔细研究了一下，终于给解决了，使用了存储过程，但不知道还有没有更好的方式，过程过下。
    create proc overcontract
    as
    declare @mindate smalldatetime
    declare @days int
    declare @temptable table ( orderid int)

set @mindate = (select min(startdate) from orders)
    set @days = (select datediff(d,min(startdate),max(enddate)) from orders)

while (@days>-1)
       begin
              declare @curdate smalldatetime
              set @curdate = dateadd(d,@days,@mindate) 

              insert into @temptable select o.orderid from product as p inner join
 (select count(positioncode) as total,positioncode from orders where @curdate between startdate and enddate group by positioncode ) dt on dt.positioncode = p.positioncode left join orders o on o.positioncode = p.positioncode 
where total>p.showcount and @curdate between startdate and enddate

       set @days = @days-1    
        end
       select distinct(orderid) from @temptable 
     go

评论

  

2008-06-28 15:31 | 代码乱了      

--创建函数
CREATE  FUNCTION getAllDate 
( 
    @Startdate datetime , @Enddate datetime 
) 
RETURNS @t TABLE ( date datetime ) 
AS 
BEGIN 
    DECLARE @time datetime 
    SET @time = @Startdate 
    WHILE ( @time <= @Enddate ) 
    BEGIN 
        INSERT INTO @t 
        SELECT @time 

        SET @time = dateadd ( day , 1 , @time ) 
    END 
    RETURN 
END 
GO

DECLARE @minDate datetime , @maxDate datetime 
SELECT 
    @minDate = min ( Startdate ) , 
    @maxDate = max ( Enddate ) 
FROM Orders 

SELECT orderid FROM Orders 
WHERE Positioncode IN 
( 
    SELECT a.Positioncode FROM 
    ( 
        SELECT 
            Positioncode , 
            date , 
            count ( date ) AS Showcount 
        FROM Orders a 
            INNER JOIN 
            ( 
                SELECT * FROM dbo.getAllDate ( @minDate , @maxDate ) 
            ) 
            t ON t.date BETWEEN Startdate AND Enddate 
        GROUP BY Positioncode , date 
    ) 
    AS a , Product 
    WHERE Product.Positioncode = a.Positioncode 
        AND a.Showcount > Product.Showcount 
) 

 来自：一家公司的数据库面试题

分类： 数据库开发 数据仓库 Web技术