ITPub&图灵“数据库高手擂台赛”，参赛结果已出，并附答案，敬请大家关注！

bupti001

是下载下来做题目？

飞天小鸟

好 多翻译有问题 不过题目是太简单了

--1
select  * from MyTable
where case when nullif(f1,0) is null then 1 else 0 end +
       case when nullif(f2,0) is null then 1 else 0 end +
       case when nullif(f3,0) is null then 1 else 0 end +
       case when nullif(f4,0) is null then 1 else 0 end +
       case when nullif(f5,0) is null then 1 else 0 end +
       case when nullif(f6,0) is null then 1 else 0 end +
       case when nullif(f7,0) is null then 1 else 0 end +
       case when nullif(f8,0) is null then 1 else 0 end +
       case when nullif(f9,0) is null then 1 else 0 end +
       case when nullif(f10,0) is null then 1 else 0 end =1

只需要一次全表扫描

--4

select distinct pilot from
(select *,row=row_number()over(partition by pilot order by px)
  from
  (select x1.*,x.px from PilotSkills x1
   inner join (select *,px=row_number()over(order by plane) from Hangar)x
   on x.plane=x1.plane
  )x3)x4
where row=(select max(px) from (select *,px=row_number()over(order by plane) from Hangar)x)
/*
pilot
---------------
Smith         
Wilson         

(2 行受影响)
*/

--6
select cust_id,
sum(case when datediff(day,bill_date,getdate()) between 0 and 30 then pizza_amt else 0 end) as [0-30天],
sum(case when datediff(day,bill_date,getdate()) between 31 and 60 then pizza_amt else 0 end) as [31-60天],
sum(case when datediff(day,bill_date,getdate()) between 61 and 90 then pizza_amt else 0 end) as [61-90天],
sum(case when datediff(day,bill_date,getdate())>90 then pizza_amt else 0 end) as [90+天]
from 赊账表
group by cust_id

--7
select student_name,case when px=chose then 'Y' else 'X' end as gradnograd
from
(select student_name,count(1) as px,sum(case when x1.credits >=x2.rqd_credits then 1 else 0 end) as chose
from
(select student_name,credit_cat, sum(credits) as credits
CreditsEarned as x
group by student_name,credit_cat
)x1
left join Categories as x2
on x1.credit_cat=x2.credit_cat
group by student_name )x3

--8
select promo_name,clerk_name,account
from
(select promo_name,clerk_name,account,px=row_number()over(partition by promo_name order by account desc)
  from
  (select x1.promo_name,x.clerk_name,sum(x.sale_amt) as account
    from Sales  as x
    left join Promotions as x1
    on x.sale_date>=x1.start_date and  x.sale_date<=x1.end_date
    group by x1.promo_name,x.clerk_name
  )x2
)x3
where px=1


2个小时 5道题目

另外 2 和10是不是 题意没讲清楚？

sylvanzzy

我觉得奖品还是太少了!

飞天小鸟

最后2题 快了

9 和 10 有点搞

sagc

Alan Flancman ran into a problem with some legacy system data that
had been moved over to an SQL database. The table looked like this:
CREATE TABLE MyTable
(keycol INTEGER NOT NULL,
f1 INTEGER NOT NULL,
f2 INTEGER NOT NULL,
f3 INTEGER NOT NULL,
f4 INTEGER NOT NULL,
f5 INTEGER NOT NULL,
f6 INTEGER NOT NULL,
f7 INTEGER NOT NULL,
f8 INTEGER NOT NULL,
f9 INTEGER NOT NULL,
f10 INTEGER NOT NULL);

renjieyi

把题下下来研究下

油菜

比写sql啊，应该是sql高手擂台赛。

horizonsun

肯定不是中国人出题的，或直接用机器翻译的？

飞天小鸟

全部搞定 邮件已发 除了 第九 第10 有点难度。其他有点简单。有点对不住高手这个称号.呵呵
期待奖品和 书。：）

newkid

还没收场么？标准答案都被人贴出来了！（见29楼）