前两天收到一家知名互联网公司的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
文章来源:http://www.cnblogs.com/springwind/archive/2008/06/28/1231527.html