Tony Lu的博客
  博客园 :: 首页 :: 联系 :: 订阅 订阅 :: 管理
  1 Posts :: 0 Stories :: 41 Comments :: 0 Trackbacks

公告

计数器:
昵称：Tony Lu
园龄：6年5个月
粉丝：0
关注：0

搜索

 
 

常用链接

我的标签

随笔分类

随笔档案

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

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

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

Sales表

OrderID

Region

Total

1

A

100.00

2

C

80.00

3

A

130.00

4

B

90.00

5

B

100.00

6

C

120.00

7

A

90.00

8

C

90.00

9

B

80.00

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

OrderID

Positioncode

Startdate

Enddate

1

A

2006-11-01

2006-11-03

2

C

2006-11-02

2006-11-03

3

B

2006-11-01

2006-11-04

4

A

2006-11-03

2006-11-04

5

C

2006-11-01

2006-11-02

6

B

2006-11-02

2006-11-05

7

A

2006-11-02

2006-11-03

8

A

2006-11-04

2006-11-05

9

C

2006-11-03

2006-11-04

10

C

2006-11-02

2006-11-04

广告位表 Product

Positioncode

Showcount

A

2

B

1

C

3

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

OrderID

2006-11-01

2006-11-02

2006-11-03

2006-11-04

2006-11-05

1

4

7

8

广告位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

posted on 2008-06-28 10:13 Tony Lu 阅读(22438) 评论(41) 编辑 收藏

Feedback

#1楼2008-06-28 10:36allies[未注册用户]
丫的 我DB太差了~~ 就算技术上上去了 我看好的offer 依然还得DB好。。。杂DBA都没了吗？
　回复　引用　  
#2楼[楼主]2008-06-28 10:49Tony Lu      
@allies
同感，面试的时候我还问面试的人这个职位是侧重于数据库还是编程，面试的说你编什么程序也得用数据库啊，唉，什么都不说了。
　回复　引用　查看　  
#3楼2008-06-28 11:45Kingthy      
试一下这条SQL语句:

SELECT O.OrderId,O.Positioncode FROM (
SELECT D.OrderId,D.Positioncode,
(SELECT COUNT(*) FROM [Orders] T
WHERE T.Positioncode=D.Positioncode AND ((D.Startdate BETWEEN T.Startdate AND T.Enddate) OR (D.Enddate BETWEEN T.Startdate AND T.Enddate))
) AS Amount FROM [Orders] D) O
INNER JOIN [Product] P ON P.Positioncode=O.Positioncode
WHERE O.Amount > P.Showcount;

　回复　引用　查看　  
#4楼2008-06-28 11:49Kingthy      
根据你提供的数据.用我上面的那条SQL语句,返回的是如下数据表:
OrderId Positioncode
----------- ------------
1 A
4 A
7 A
8 A
3 B
6 B
2 C
10 C

也就是:
A位置中的是 1,4,7,8 (你文中说的只有1,4,7是错误的,因为四条订单都在2006-11-03号重叠)
B位置的是: 3,6
C位置的是: 2,10
　回复　引用　查看　  
#5楼2008-06-28 12:52kes.king[未注册用户]
我有疑问，楼主第二题的数据中，OrderID=8不是从11-03播到11-05吗，那么轮播情况表中，亦即最终得到的结果集不应只是1、4、7，还应加个8吧？

因为我看完题目后，没有看你的存储过程，自己做出来的结果，广告位A是1、4、7、8.
　回复　引用　  
#6楼2008-06-28 12:53kes.king[未注册用户]
唔。。。我回了才刚看到楼上的回复。

同上~ ^_^
　回复　引用　  
#7楼2008-06-28 12:54kes.king[未注册用户]
刚才建表做题，一直没有刷新页面，做完后直接回复刷新了才看到有人更快了。
　回复　引用　  
#8楼2008-06-28 13:01kes.king[未注册用户]
是了，Kingthy兄，你的SQL语句是可以，但我之前困惑的就是不知题目是否要求结果集中包括日期，因你的结果集中无具体日期。

为了解出日期，不得以我用了临时表与存储过程，SQL想不出解决日期的方法，不知可有高见？请指教。

另外，关于C，我的结果与你不同，根据楼主的数据分析，
C，2006-11-01，1次
C，2006-11-02，3次
C，2006-11-03，3次
C，2006-11-04，2次
并未超过次数。我的结果集中不含C。
　回复　引用　  
#9楼2008-06-28 13:05阿滨       
你是要面试程序还是dba啊,真是的.!!!
知名的网络公司dba的事情还要你来做啊!
　回复　引用　查看　  
#10楼2008-06-28 13:06kes.king[未注册用户]
POSITIONCODE ORDERID SHOWDATE SHOWCOUNT DEFAULTSHOWCOUNT
A 1 2006-11-3 4 2
A 4 2006-11-3 4 2
A 7 2006-11-3 4 2
A 8 2006-11-3 4 2
B 3 2006-11-2 2 1
B 6 2006-11-2 2 1
B 3 2006-11-3 2 1
B 6 2006-11-3 2 1
B 3 2006-11-4 2 1
B 6 2006-11-4 2 1

B在2、3、4号均超次数。
　回复　引用　  
#11楼[楼主]2008-06-28 13:14Tony Lu      
@Kingthy
@kes.king
抱歉，是我弄错了，已修改orderid=8的开始日期。
　回复　引用　查看　  
#12楼[楼主]2008-06-28 13:15Tony Lu      
@阿滨
不是dba，呵呵。
　回复　引用　查看　  
#13楼2008-06-28 13:35Kingthy      
@kes.king
嗯.刚才是我输入错了样例数据导致的.我刚才再检查一篇.发现C应该是所有都符合.也即结果应该是如下:
OrderId Positioncode
----------- ------------
1 A
4 A
7 A
8 A
3 B
6 B
5 C
9 C
2 C
10 C

在2006-11-02时,C的2,5,10重叠.所以符合答案,
在2006-11-03时,C的2,9,10也重叠,所以也符合答案.
　回复　引用　查看　  
#14楼2008-06-28 13:43tempdb[未注册用户]
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
　回复　引用　  
#15楼2008-06-28 13:55hehehe[未注册用户]
select a.OrderID, a.Positioncode,
datediff(day,a.Startdate, a.Enddate) + 1 as diff, b.Showcount
from Orders a
inner join Product b on a.Positioncode = b.Positioncode
where (datediff(day,a.Startdate, a.Enddate) + 1) > b.showcount
order by OrderID
　回复　引用　  
#16楼2008-06-28 15:05kes.king[未注册用户]
@Kingthy

C的showcount是3次哦，呵呵，大于3才算超过吧。
　回复　引用　  
#17楼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 
) 

　回复　引用　查看　  
#18楼2008-06-28 15:35代码乱了      
我的思路和博主的差不多呵呵，花了半个小时啊
　回复　引用　查看　  
#19楼2008-06-28 15:47Kingthy      
@kes.king

-_-#嗯,第二次我才输入错误了数据,我把C输入成了2,汗..

PS:我的那个SQL语句是错的.看来一句是不行滴..
　回复　引用　查看　  
#20楼2008-06-28 16:07walkinhill[未注册用户]
Select * from orders
Where
'2006-11-03' Between StartDate and dateadd(day,1,EndDate) and
Positioncode in
(
Select
P.PositionCode
From
Product P
Inner Join
(
Select PositionCode,Count(*) as CNT
From Orders
Where '2006-11-03' Between StartDate and dateadd(day,1,EndDate)
Group by PositionCode
) O
On
P.PositionCode = o.PositionCode
Where
o.CNT > P.ShowCount
) And PositionCode = 'A'

结果集：
OrderID PositionCode StartDate EndDate
----------- -------------------- ----------------------- -----------------------
1 A 2006-11-01 00:00:00.000 2006-11-03 00:00:00.000
4 A 2006-11-03 00:00:00.000 2006-11-04 00:00:00.000
7 A 2006-11-02 00:00:00.000 2006-11-03 00:00:00.000

结果集：
　回复　引用　  
#21楼2008-06-28 16:27边城浪[未注册用户]
第一个.这样写很简单:
SELECT * FROM Sales AS A WITH(NOLOCK)
WHERE Total>(SELECT avg(Total) From Sales AS T WHERE A.Region=T.Region)
　回复　引用　  
#22楼2008-06-28 17:01xs[未注册用户]

declare @FMinDate datetime
declare @FMaxDate datetime

set @FMinDate = (select min(fstartdate) from xs_Orders)
set @FMaxDate = (select max(fenddate) from xs_Orders)

declare @Days int
Set @Days = datediff(day,@FMinDate,@FMaxDate)

create table #Temp
(
ShowCount int,
FPositionCode varchar(100)
)

declare @step int
set @step=1
while @step <= @days
begin
declare @currentDay datetime
Set @CurrentDay = dateadd(day,@step,@FMinDate)
insert into #temp
Select count(*) as ShowCount,FPositionCode from xs_Orders where fstartdate <= @CurrentDay and fenddate >=@CurrentDay group by FPositionCode
set @step = @step + 1
end

select distinct c.* from #temp a
inner join xs_Product b on b.FPositionCode=a.FPositionCode
inner join xs_Orders c on c.FPositionCode=a.FPositionCode
where b.FCount < a.ShowCount

drop table #temp
　回复　引用　  
#23楼2008-06-28 18:12HelloCode      
第二题感觉有问题，像广告这一类的，在添加广告的时候就应该可以显示那些日期还有空余的广告位。这种题目不符合实际。
　回复　引用　查看　  
#24楼2008-06-28 18:25kes.king[未注册用户]
@HelloCode
题目中说明了，销售人员是不care这个限制的。
可能在一个销售人员正在填写还未提交的时候，另一个销售人员已经成功跟客户接下了订单，因此他也需要填写提交。

我认为很符合实际。
　回复　引用　  
#25楼2008-06-29 07:42jillzhang      
题目不错，很考察实战能力
　回复　引用　查看　  
#26楼2008-06-29 12:22HelloCode      
@kes.king
像这种情况在提交合同后在检测出来，肯定还是要再修改的，这就比较浪费时间了。

如果是一个销售人员提交的时候，另外一销售人员先提交了，在后提交的销售人员提交合同时就应该提示这段时间日期已经满了，需要做修改。然后再提交修改后的合同，提交需要肯定修改的合同感觉没有意义，无法安排广告的合同总不能就这样吧。这题目有问题。
　回复　引用　查看　  
#27楼2008-06-29 23:39SecurityElement[未注册用户]
--try:

declare @mindate datetime, @maxdate datetime
select @mindate=min(startdate),@maxdate=max(enddate) from orders

create table #dt(ErrorDay datetime)

while @mindate<=@maxdate
begin
insert into #dt values(@mindate)
select @mindate=dateadd(day,1,@mindate)
end

select d.*,c.showcount from product c,
(select b.positioncode ,a.ErrorDay,ErrorCount=count(1) from #dt a,orders b where a.ErrorDay between b.startdate and b.enddate group by a.ErrorDay,b.positioncode) d
where c.positioncode=d.positioncode and d.ErrorCount>c.showcount

drop table #dt
　回复　引用　  
#28楼2008-06-30 10:19雅阁布      
看得都晕了！！！
　回复　引用　查看　  
#29楼2008-07-01 17:22边城浪[未注册用户]
测试通过
DROP TABLE #Temp
CREATE TABLE #Temp
(
OrderID INT,
Positioncode NCHAR(10),
InDate DATETIME,
);
DECLARE @minId int,@maxID int;
SELECT @minId = min(orderid),@maxid = max(orderid) FROM Orders;

WHILE(@minId<=@maxid)
BEGIN
DECLARE @StartDate DATETIME,@EndDate DATETIME
DECLARE @Positioncode NCHAR(10)

SELECT @Positioncode = Positioncode,
@StartDate = StartDate,
@EndDate = EndDate
FROM dbo.Orders
WHERE OrderID = @minId;

WHILE(@StartDate<=@EndDate)
BEGIN
INSERT INTO #Temp(OrderID, Positioncode, InDate)
VALUES (@minId, @Positioncode, @StartDate);
SET @StartDate = DATEADD(DAY,1,@StartDate);
END
SET @minId = @minId + 1
END

WITH X AS
(
SELECT T.*
FROM (SELECT Positioncode,InDate,Count(1) AS Numbers FROM #Temp
GROUP BY Positioncode,InDate) AS T
INNER JOIN dbo.Product AS P
ON T.Positioncode = P.Positioncode
AND T.Numbers > P.Showcount
--ORDER BY T.Positioncode,T.InDate
)
SELECT A.OrderID,A.Positioncode,X.InDate,X.Numbers
FROM dbo.Orders AS A
INNER JOIN X
ON A.Positioncode = X.Positioncode
AND X.InDate BETWEEN A.StartDate AND A.EndDate
ORDER BY X.Positioncode,X.InDate
　回复　引用　  
#30楼2008-07-01 17:24边城浪[未注册用户]
执行结果为:

1 A 2006-11-03 00:00:00.000 3
4 A 2006-11-03 00:00:00.000 3
7 A 2006-11-03 00:00:00.000 3
3 B 2006-11-02 00:00:00.000 2
6 B 2006-11-02 00:00:00.000 2
3 B 2006-11-03 00:00:00.000 2
6 B 2006-11-03 00:00:00.000 2
3 B 2006-11-04 00:00:00.000 2
6 B 2006-11-04 00:00:00.000 2
　回复　引用　  
#31楼2008-07-03 10:13边城浪[未注册用户]
用SQL2005重新写了个...
WITH T AS
(
SELECT A.*,
(SELECT COUNT(1) FROM dbo.Orders AS B
WHERE A.Positioncode = B.Positioncode
AND A.Startdate >= B.Startdate AND A.Startdate <= B.Enddate
) AS Counts1
,(SELECT COUNT(1) FROM dbo.Orders AS B
WHERE A.Positioncode = B.Positioncode
AND A.Enddate >= B.Startdate AND A.Enddate <= B.Enddate
) AS Counts2
FROM dbo.Orders AS A
)
SELECT T.OrderID, T.Positioncode, (CASE WHEN T.Counts1>T.Counts2
THEN T.Counts1
ELSE T.Counts2 END) AS Counts
FROM T
INNER JOIN dbo.Product AS P
ON T.Positioncode = P.Positioncode
AND P.Showcount < (CASE WHEN T.Counts1 > T.Counts2
THEN T.Counts1
ELSE T.Counts2 END)
ORDER BY Positioncode

运行结果:

1 A 3
4 A 3
7 A 3
6 B 2
3 B 2
　回复　引用　  
#32楼2008-07-23 22:19菜鸟请教一个Sql[未注册用户]
select a.*, (
select count(*) from product b where a.Positioncode = b.Positioncode
)as c from orders a
小弟不才,上面语句看了半天没明白,请教前辈解释一下,多谢了!
为什么能a.Positioncode = b.Positioncode?
我只见过
(select count(*) from product b,product a where a.Positioncode = b.Positioncode)
这样的
明显有区别,但实在琢磨不透啊...闹心啊!!
　回复　引用　  
#33楼2008-08-26 09:56无铭[未注册用户]
@菜鸟请教一个Sql

(
select count(*)
from product b
where a.Positioncode = b.Positioncode
)as c
相当一个属性列,被命名为C

结果表
　回复　引用　  
#34楼2009-05-03 23:45msjl[未注册用户]
good
中国面试交流网
http://www.msjl.net
　回复　引用　  
#35楼2009-05-26 11:29play_8[未注册用户]
Drop table #TempDateList
Drop table #DailyCountTemp
--
Declare @minStartdate datetime
Declare @MaxEnddate datetime
SELECT @MinStartdate = Min(startdate)
FROM orders
SELECT @MaxEnddate = Max(Enddate)
FROM orders
Create table #TempDateList (Date Datetime)
Declare @Tempdate datetime
Set @Tempdate = @MinStartdate
While (@TempDate <= @MaxEnddate)
BEGIN
INSERT INTO #TempDateList (Date) values (@TempDate)
SET @TempDate = DateAdd(dd,1,@TempDate)
END

SELECT COUNT(*) Count , positioncode, T2.Date
INTO #DailyCountTemp
FROM orders T1 Right join #TempDateList T2 ON T2.Date >= T1.Startdate AND T2.Date <= T1.EndDate
Group by positioncode, T2.Date

SELECT Distinct T2.*
FROM #DailyCountTemp T1 , Orders T2 , Product T3
WHERE T1.positioncode = T2.positioncode
AND (T1.Date >= T2.Startdate AND T1.Date <= T2.EndDate)
AND T1.Count > T3.ShowCount
AND T1.positioncode = T3.positioncode
Order by positioncode
　回复　引用　  
#36楼2009-06-04 22:01anchen211[未注册用户]
我的做法:
select o.positioncode, d.day, count(*)
from orders o,
(select to_date((select min(o1.startdate) from orders o1),
'yyyy-mm-dd') + rownum - 1 day
from all_objects
where rownum < 30) d
where d.day >= to_date(o.startdate, 'yyyy-mm-dd')
and d.day <= to_date(o.enddate, 'yyyy-mm-dd')
group by o.positioncode, d.day
having count(*) > (select p.showcnt
from product p
where p.positioncode = o.positioncode);
　回复　引用　  
#37楼2009-06-04 22:02anchen211[未注册用户]
不好意思,我做ORACLE的,用的是ORACLE的写法.
　回复　引用　  
#38楼2009-08-04 18:01xwx[未注册用户]
drop table #tt
create table #tt(dat datetime)
declare @sql datetime
select @sql=(select min(startdate)from orders)
print @sql
while @sql<=(select max(enddate)from orders)
begin
insert into #tt values(''+@sql+'')
select @sql=@sql+1
end
select a.orderid,a.positioncode,b.dat,1 sl into #a111 from orders a left join #tt b on a.startdate<=b.dat and b.dat<=a.enddate

select e.orderid,e.positioncode,cc.dat,cc.sl from orders e,
(select a.* from (select positioncode,dat,sum(sl)sl from #a111
group by positioncode,dat )a join product b on a.sl>b.showcount
and a.positioncode=b.positioncode )cc where e.positioncode=cc.positioncode and cc.dat between startdate and enddate order by positioncode


结果为
1 A 2006-11-03 00:00:00.000 3
4 A 2006-11-03 00:00:00.000 3
7 A 2006-11-03 00:00:00.000 3
3 B 2006-11-02 00:00:00.000 2
6 B 2006-11-02 00:00:00.000 2
3 B 2006-11-03 00:00:00.000 2
6 B 2006-11-03 00:00:00.000 2
3 B 2006-11-04 00:00:00.000 2
6 B 2006-11-04 00:00:00.000 2
　回复　引用　  
#39楼2009-08-19 21:40按时打发似的发射点法[未注册用户]
我也不知道我要说什么
我是菜鸟
　回复　引用　  
#40楼2009-09-23 16:46TM Zhang      
这里还有很多asp.net的面试题，分享一下
http://jack-fx.com/csharp/post/aspnet-interview-questions-and-answers-csharp.htm
http://jack-fx.com/csharp/post/38-interview-questions-and-answers-of-aspnet.htm
http://jack-fx.com/csharp/post/c-interview-questions-and-answers.htm
　回复　引用　查看　  
#41楼2011-04-27 17:47williambirkin      
无意中看到这个题目,也YY一下,哈哈哈.
这个题目的关键是能把Orders表OrderID,Positioncode,Startdate,Enddate
1,A,2011-01-01,2011-01-03
转化为
OrderID,Positioncode,Date
1,A,2011-01-01
1,A,2011-01-02
1,A,2011-01-03
的结构,然后汇总得到"每天","每个广告位"的合同数之和.
(得到这个新的结构很简单.你可以生成一个日历的临时表.然后把order表和日历表连接查询就行了.)
然后再和Product表连接,查询合同数>Showcount的广告位和日期.到这里核心的数据已经得到了.
　回复　引用　查看　  
最新IT新闻:
· 动视暴雪确认今年第二季度推出《暗黑破坏神3》
· 官方承认 Google Bar 逆行倒施
· 创意融资平台Kickstarter迎来重大突破，两个项目各获融资超过100万美元
· 商务社交网站LinkedIn发布2011第四季度财报，收入1.67亿美元，同比增长105%
· PaperKarma：“拍”走垃圾邮件
» 更多新闻...