Topic: 2 道关于SQL的面试考题。老板突然愤愤地把全组7、8个Senior Developer包括Manager召集起来，说是面试了一堆人，都没有人做得出他的题目。要大家都做一遍，看看到底是不是他的题目有问题。有兴趣的人过来看看。 @佛州华人论坛:佛州枫下论坛 The Rolia Forum of Florida

工作学习 / 专业技术讨论 / 2 道关于SQL的面试考题。老板突然愤愤地把全组7、8个Senior Developer包括Manager召集起来，说是面试了一堆人，都没有人做得出他的题目。要大家都做一遍，看看到底是不是他的题目有问题。有兴趣的人过来看看。 -bdbs(不多不少); 2006-11-3 (#3301416@0)

题目一：Table REV has 2 columns, client_id, revenue

Need a query to get the client position the 10th top revenue.

raw data looks like:
AB001, 1000
AB001, 150
AB002, 1500
AB003, 2000
AB001, 1000
-bdbs(不多不少); 2006-11-3 {254} (#3301429@0)

For Oracle:select *
from (select client_id, revenue, rank() over (order by revenue desc) rn from REV)
where rn = 10; -zdq(zdq); 2006-11-3 {107} (#3301487@0)

analytical function是个好东西!好像用的人不多? -newkid(newkid); 2006-11-3 (#3301510@0)
忘了说了，是MS SQL。 -bdbs(不多不少); 2006-11-3 (#3301524@0)

TOP 10, 然后倒排一下就行了 -canadiantire(轮胎-pax et lux); 2006-11-3 (#3301535@0)
Dose it work? I didn't try it on MS SQL.SELECT *
FROM REV a
WHERE 10 = (SELECT COUNT(DISTINCT revenue)
FROM REV b
WHERE b.revenue>= a.revenue)
ORDER BY revenue DESC; -guanshui88(我就是来捣乱的); 2006-11-3 {165} (#3301549@0)

真是来捣乱的？起码要GROUP一下，SUM一下吧。 -bdbs(不多不少); 2006-11-3 (#3301557@0)

I don't have MS SQL. You can try it on your DB. I am sure it works. #3301553 -guanshui88(我就是来捣乱的); 2006-11-3 (#3301563@0)

it won't for sure. -bdbs(不多不少); 2006-11-3 (#3301584@0)

HAVE YOU TIRED IT? I TRIED IT ON ORACLE AND SYBASE. BOTH WORK. THERE IS NO SPECIAL SQL. -guanshui88(我就是来捣乱的); 2006-11-3 (#3301597@0)

I don't need to try. Man, the client_id in the rev table is not unique. same id can have multiple records, though the ranking should based on the tatol revenue. you need at least a SUM in your query. -bdbs(不多不少); 2006-11-3 (#3301617@0)

你题目没有说清楚，是“总”收入 -newkid(newkid); 2006-11-3 (#3301639@0)
就两个column，还一个client id 对好几个revenue? 有意思。 -guanshui88(我就是来捣乱的); 2006-11-3 (#3301643@0)

这是我的不是。可是我后来修正了呀。(#3301557） -bdbs(不多不少); 2006-11-3 (#3301659@0)

我服了你。条条大路通罗马，只有你老板一条路对（GROUP SUM）? 谁有MS SQL 帮我试试？ -guanshui88(我就是来捣乱的); 2006-11-3 (#3301615@0)

这题目还可以更有意思:假设表是：employee_id, salary, dep_id
列出每个部门收入前三名，这是我做过的类似题目，用ORACLE的分析函数RANK可以方便地写出来。 -newkid(newkid); 2006-11-3 {125} (#3301571@0)
select clientID,SUM(revenue) from rev group by clientID order by SUM(revenue) desc -sz648(多伦多市政府); 2006-11-3 (#3301576@0)

CLOSE 了，不过还不对。 -bdbs(不多不少); 2006-11-3 (#3301619@0)

top 10 -coolmao(酷猫); 2006-11-3 (#3301715@0)

还差一点。要第10个。不是前10个。 -bdbs(不多不少); 2006-11-3 (#3301721@0)

在top 10 DESC 的上面再加一个top 1. ：） -luoboyang(萝卜秧); 2006-11-3 (#3301736@0)

SELECT * FROM ( SELECT client_id, revenue, RANK() OVER (ORDER BY revenue DESC) r_rank FROM table) WHERE r_rabk = 10; -waiting(OK); 2006-11-3 (#3301690@0)
SELECT TOP 1 ClientID from REV where ClientID not in (SELECT TOP 9 ClientID from REV order by revenue) order by revenue -09876(闲人); 2006-11-3 (#3301741@0)
刚看完题目一...我的答案. Select top 1 client_id, revenue from ( select top 10 client_id, revenue from REV order by revenue desc ) T1 order by revenue -hard20(hard20); 2006-11-3 (#3302003@0)

思路正确。加上SUM和GROUP BY就对了。 -bdbs(不多不少); 2006-11-3 (#3302418@0)

oracle的话, oder by... rownum=10就行了. SQL server 2005的话, 也有了row_number函数, 低点的版本的话只好用top... -poohbear(毛毛熊); 2006-11-3 (#3302336@0)

或者, 象这样自己做个rank -poohbear(毛毛熊); 2006-11-3 (#3302362@0)

My answers:1.Oracle:

select * from ( select row_.*, rownum rownum_ from ( select * from REV order by revenue) row_ where rownum =< 10) where rownum_ >= 10

2.SQL Server:

select top 10 * from rev where client_id not in
(select top 9 client_id from rev order by revenue ) order by revenue

3.MySQL

select * from REV oder by revenve limit 10, 1

4.DB2

select * from (select *,rownumber() as ROW_NEXT from revenue orderby revenue) where ROW_NEXT between 10 and 10 -informix(informix); 2006-11-4 {500} (#3302914@0)

题目二：2 Tables A and B all have the field called Client_id. example
A:
AB001
AB003
AB004

B:
AB002
AB003
AB005

we need a query to get the client id appear only in Table A or Table B. the result like:

A AB001
B AB002
A AB004
B AB005 -bdbs(不多不少); 2006-11-3 {254} (#3301444@0)

来应聘的人不是学电脑的，而是学别的，比如兽医什么的。 -maer(马儿); 2006-11-3 (#3301467@0)

有.........那么夸张么？马儿看来都不屑一做。马驹后来把糖吃光了么？ -bdbs(不多不少); 2006-11-3 {22} (#3301516@0)

我也在吃啊。。。(#3301402@0)第二题是比较常见的。 -maer(马儿); 2006-11-3 {20} (#3301527@0)

If OracleSELECT *
FROM A
UNION
SELECT *
FROM B
MINUS
(SELECT *
FROM A
INTERSECT
SELECT *
FROM B
) -zdq(zdq); 2006-11-3 {103} (#3301496@0)

我猜用 FULL OUTER JOIN 加上两个 IS NULL 就可以了。 -newkid(newkid); 2006-11-3 (#3301513@0)

CLOSE 了，不过还不对。 -bdbs(不多不少); 2006-11-3 (#3301580@0)

this?SELECT id
FROM (
SELECT
CASE
WHEN A.id IS NULL THEN 'B ' + CAST(B.id AS VARCHAR(4))
WHEN B.id IS NULL THEN 'A ' + CAST(A.id AS VARCHAR(4))
ELSE NULL
END AS id
FROM t1 A
FULL OUTER JOIN t2 B
ON A.id=B.id
) C

WHERE id IS NOT NULL -canadiantire(轮胎-pax et lux); 2006-11-3 {256} (#3301601@0)

this one works. should be one of the right answer. -bdbs(不多不少); 2006-11-3 (#3301640@0)

是不是想知道ID来自哪边？用CASE判断就行了吧。 -newkid(newkid); 2006-11-3 (#3301604@0)

这个肯定不对。结果需要知道不同的ID来自哪个表。 -bdbs(不多不少); 2006-11-3 (#3301531@0)

那就两个LEFT JOIN 两个IS NULL加一个UNION -canadiantire(轮胎-pax et lux); 2006-11-3 (#3301544@0)

此解刚够及格。 -bdbs(不多不少); 2006-11-3 (#3301560@0)

select clientId from tableA where clientID not in (select clientID from tableB) Union select clientId from tableB where clientID not in (select clientID from tableA) -sz648(多伦多市政府); 2006-11-3 (#3301561@0)

FAIL！我老板说了，谁如果用了IN，NOT IN，就可以从这个屋子里出去了。 -bdbs(不多不少); 2006-11-3 (#3301572@0)

MD,什么老板,做完了才说, -sz648(多伦多市政府); 2006-11-3 (#3301589@0)

还有半句是说：用union的如果能做出来，算PASS。刚够及格。我这老板可是个搞技术出身的大拿。 -bdbs(不多不少); 2006-11-3 (#3301627@0)

十分钟做完你两道题行了, -sz648(多伦多市政府); 2006-11-3 (#3301644@0)

老板给我们的时间也只有前后10分钟。还包括解释为什么要我们做这个和出题目的时间。会的人5分钟就能搞定，不会的人再多时间也没戏吧。 -bdbs(不多不少); 2006-11-3 (#3301665@0)

说出来。 10分钟内你们公司几个人搞定了？ -guanshui88(我就是来捣乱的); 2006-11-3 (#3301675@0)

不知道。就让留在桌上，也不知道别人搞定没有。反正我是搞定了。 -bdbs(不多不少); 2006-11-3 (#3301720@0)

那就用EXISTS吧, -sz648(多伦多市政府); 2006-11-3 (#3301729@0)

Exists 比 in 好不了多少,不过肯定比 NOT IN 好得多就是了 -hard20(hard20); 2006-11-4 (#3302613@0)

I used NOT IN, it works. But there may be better way without 'NOT IN' See, this time, I am more careful, I even sorted it. :-)SELECT *
FROM (
SELECT 'A', z_1.client_id
FROM z_1
WHERE z_1.client_id NOT IN (SELECT client_id FROM z_2)
UNION
SELECT 'B', z_2.client_id
FROM z_2
WHERE z_2.client_id NOT IN (SELECT client_id FROM z_1)
) t
order by t.client_id -zdq(zdq); 2006-11-3 {240} (#3301797@0)

他老板不让用的意思可能是要考虑PERFORMANCE，如果表不大倒是可以用的。 -newkid(newkid); 2006-11-3 (#3301828@0)

Ok, this time without 'NOT IN'SELECT * FROM (
SELECT
(CASE WHEN id_2 IS NULL THEN 'A' ELSE 'B' END) table_name,
(CASE WHEN id_2 IS NULL THEN id_1 ELSE id_2 END) client_id
FROM(
SELECT z_1.client_id id_1, z_2.client_id id_2
FROM z_1 full outer join z_2
on z_1.client_id = z_2.client_id
) t
WHERE t.id_1 IS NULL or t.id_2 IS NULL ) t_2
ORDER by t_2.client_id -zdq(zdq); 2006-11-3 {349} (#3301886@0)

这个比较好一点。(#3301711) 我的答案是SELECT DISTINCT
CASE WHEN A.ID IS NULL THEN 'B' ELSE 'A' END AS TABLE,
ISNULL(A.ID, B.ID) AS ID
FROM A
FULL JOIN B
ON A.ID = B.ID
WHERE A.ID IS NULL
OR B.ID IS NULL -bdbs(不多不少); 2006-11-3 {193} (#3302395@0)

distinct 是多余的，会有排序开销 -newkid(newkid); 2006-11-3 (#3302410@0)

如果真正考虑系统开销，好的答案我觉得应该是SELECT
CASE WHEN #A.ID IS NULL THEN 'B' ELSE 'A' END AS TABLE,
ISNULL(#A.ID, #B.ID) AS ID
FROM (SELECT DISTINCT ID FROM A) #A
FULL JOIN (SELECT DISTINCT ID FROM B) #B
ON #A.ID = #B.ID
WHERE #A.ID IS NULL
OR #B.ID IS NULL -bdbs(不多不少); 2006-11-4 {231} (#3302606@0)

这还不如上一个，原来只是对结果集排序（通常结果集是比较小的），现在你是对两个原始表进行DISTINCT。索引在这能用上吗？我不懂SQLSERVER,但估计是不行。 -newkid(newkid); 2006-11-4 (#3302821@0)

我是这么理解。两个表里的ID都不是唯一的，如果DISTINCT了就是生成了两个较小的集再FULL JOIN，否则就是两个巨集FULL JOIN了再排序DISTINCT。所以我觉得如果ID重复越多，前者开销就越小。如果ID重复少，后者就快。 -bdbs(不多不少); 2006-11-4 (#3303110@0)

两位老大，不要为小技巧麻烦了。。。不当饭吃。。。。我来问个问题，如果两个table各有5000万纪录，而且重样的概率大概在10-20%，你们的解决方案还用的上么？我问个问题：请给出最快地解决方案。。。随便什么方案，只要不新写一个操作系统或者数据库即可。。。 -interview(intervieweree); 2006-11-4 {191} (#3303296@0)

这种SQL在实用中就是为了发现“少数”我们感兴趣的数据，如果有上千万行，那肯定不是这么设计的。别叫我老大，我不当老大很多年了…… -newkid(newkid); 2006-11-5 (#3304983@0)

你这个query的performance一样不好, 创建annonymous temp table 的开销是很大的，而且full join 两个temp tables without index.你们老板脑子不懂活学或用，象这个题，用not exists 的performance不会差，不信你自己去试showplan. 总体来说，not in, not exists 容易搞成full table scan, 但是影响query performance的因素很多，关键是要搞清楚optimizer是如何优化你的query. -ubs(ubs); 2006-11-6 {228} (#3305635@0)

how about this one?select
isnull(a.id, b.id) as id ,
case when a.id is null then 'A' else 'B' end as tbl
from #t1 a
full outer join #t2 b on a.id = b.id
where a.id is null or b.id is null -digitworm(digitworm); 2006-11-3 {183} (#3301711@0)

虽然我不懂SQL SERVER,但我的思路也是该这样写 -newkid(newkid); 2006-11-3 (#3301834@0)
that's my answer. -bdbs(不多不少); 2006-11-3 (#3301847@0)

select clientid from a where clientid not in (select clientid from b) -09876(闲人); 2006-11-3 (#3301748@0)
这个比较常见...还是不做了.. -hard20(hard20); 2006-11-3 (#3302007@0)
select Client_id from ( select Client_id from tbA union all select Client_id from tbB ) A group by Client_id having count(Client_id) <= 1 -wuda(乌达); 2006-11-3 (#3302011@0)

You can tell your boss: "The testing sucks" -guanshui88(我就是来捣乱的); 2006-11-3 (#3301553@0)

假如其他7、8个人不这么认为的话，我就该卷铺盖走人了。 -bdbs(不多不少); 2006-11-3 (#3301562@0)

凑个热闹，我这也有个挺有趣的SQL, 不过不是面试题目，是我最近在实现的东西有这么一个表：linked_customers
cust_id1, cust_id2, linked_reason

如果我们发现两个人有关联（比方说，他们用了相同的邮件，或者密码），就会在这张表中增加一个记录。

现在的任务是：发现间接的关联。比方说，A 和 B 有相同的邮编，B 和 C 有相同的姓名，那么必须找出 A 和 C 并加入到这张表。当然实际的关联情况还更复杂些，你找到的记录也有可能已经存在了。

我用了ORACLE的自连接,现在还没有写完，看看有没有什么更好的主意？ -newkid(newkid); 2006-11-3 {412} (#3301681@0)

嗬嗬，跟我做的一个冬冬有点像，不过我的简单些。我也是一个表tt，简单说有两列 A, B，给出任何一个值@v，要求找出所有不重复的关联的Ａ和Ｂ，生成一个列的表。

我的做法使用表变量

DECLARE @w TABLE ( A INT )

INSERT @w (A) VALUES (@v)

然后反复

INSERT INTO @w (A )
SELECT tT.A
FROM tt tT
INNER JOIN @t tW
ON tW.A=tT.A
WHERE tT.B NOT IN (
SELECT A FROM @w
)
UNION
SELECT tT.B
FROM tt tT
INNER JOIN @t tW
ON tW.A=tT.B
WHERE tT.A NOT IN (
SELECT A FROM @w
) -canadiantire(轮胎-pax et lux); 2006-11-3 {432} (#3301716@0)

我也正考虑用临时表。我的需求复杂些，要把整个网找出来，找到的还要写出最短关联路径。整个是递归的我都怕没有出口。 -newkid(newkid); 2006-11-3 (#3301751@0)

Spanning tree? Hmm, 很很有趣，我也试试． -canadiantire(轮胎-pax et lux); 2006-11-3 (#3301769@0)

有个前提就是可以认为原来表里的记录已经是完整的（即覆盖了所有关联），只要考虑新加入的记录所引起的间接关联就行 -newkid(newkid); 2006-11-3 (#3301838@0)

那干吗不用trigger做?怕影响性能? -hard20(hard20); 2006-11-3 (#3302019@0)

GIS的东东 ? -hard20(hard20); 2006-11-3 (#3302018@0)

不是GIS,就是风险控制,试图找出有问题的用户。用TRIGGER确实会影响性能，搞不好就死在那里了。 -newkid(newkid); 2006-11-3 (#3302038@0)

提一个思路：可以按照图论中的最短路径法来考虑。1 表中的 ID1 看作是图中的起始点，初始表中的 ID2 是与 ID1有直接路径的点
2 找出与ID2有直接路径的点，把这些点看作是与 ID1有两次路径的点，并与ID1建立关联。对新建立的关联进行判断，如果已经在表中，则忽略，否则把新关联加入到表中。
3 重复步骤1，2，直到不再有新的关联(不在表中的关联)出现为止。

只是一个想法，还有细节问题需要考虑：
是作为有向图还是无向图。如果表中只出现 ID1=A, ID2=B, 没有出现ID1=B, ID2=A则为无向图，初始点为所有的ID1; 否则为有向图，初始点为ID1和ID2的并集。 -digitworm(digitworm); 2006-11-4 {471} (#3302999@0)

谢谢你小虫，这两天我都没有动手，思考的结果也是你的步骤1-3。我这图是无向的。 -newkid(newkid); 2006-11-5 (#3304968@0)

失败结果：不能用 ORACLE 的 CONNECT BY, 重复数据太多了 -newkid(newkid); 2006-11-3 (#3302486@0)
几层间接的？如果不限层数就麻烦。 -bdbs(不多不少); 2006-11-4 (#3302614@0)

一句话提醒了我，因为关联原因是有限的，直接关联都先找出来了，所以层数也该是有限的。 -newkid(newkid); 2006-11-4 (#3302822@0)

sorry……刚才这推理是错的。 -newkid(newkid); 2006-11-4 (#3302828@0)

读一读 Efficient Transitive Closure Computation in Large Digraphs -886xyz(cqcq); 2006-11-5 (#3304893@0)

多谢，我会去找相关资料（可能来不及读了），先按自己的思路做，以后再想办法提高效率。记得N年前读离散数学有相关内容，书到用时方恨少啊。 -newkid(newkid); 2006-11-5 (#3304979@0)

这个问题用邻接表比较容易，理论上复杂度O(n^2),你大概是搞data mining的吧？ -ubs(ubs); 2006-11-6 (#3305754@0)

第一题会一点OLAP函数就解决了，不会的话用两次TOP也可以.第二题没一个正确答案，没有考虑NULL的问题。不过能这样也算及格了。 -886xyz(cqcq); 2006-11-3 (#3302192@0)

什么乱七八糟的，case 拉, isnull 拉，。。。。看我的select k.*
from
(select a.* from a
union
select b.* from b ) k, (select a.name from a, b where a.name = b.name) m
where k.name <> m.name -interview(intervieweree); 2006-11-3 {145} (#3302299@0)

你出局了，知道用<>连接会产生多少记录？你的意思是想 MINUS 两个集合。有空帮我想想我的题目，我都加班N小时了。 -newkid(newkid); 2006-11-3 (#3302342@0)

就那3行得table会有多少连接？ -interview(intervieweree); 2006-11-3 (#3302389@0)

三行只是SAMPLE DATA……多一个也不行呀。 -newkid(newkid); 2006-11-3 (#3302427@0)

那就4行吧。。。。，算了不台杠了，人家招dba level 的 developer. 至于你那个问题么，我给你个提示，别人能，你也能。。。不怕做不到，就怕想不到。。。。 -interview(intervieweree); 2006-11-3 (#3302442@0)

简直是哲学家啊……我可没抬杠，澄清一下cartesian product 的概念：两个集合的迪卡尔积不是加法，而是乘法。如果K有10行，M有10行，结果就是100行,不是20行。你用 <> 进行连接，只能排除其中的一小部分，会有很多你不想要的结果在结果集中出现。 -newkid(newkid); 2006-11-3 {164} (#3302467@0)

那我不喜欢full out join 怎么办啊。。。 -interview(intervieweree); 2006-11-3 (#3302492@0)

你倒是说说看什么NULL的问题？意思是原表中可能有NULL？既然说是ID, 潜台词就是非空的 -newkid(newkid); 2006-11-3 (#3302350@0)

A里的ID可能B里没有，反之亦然。题目就是要把只有一个表里有另一个表里没有的找出来，当然有NULL了。(#3302395) -bdbs(不多不少); 2006-11-3 (#3302406@0)

我的问题是：两张表的数据中，可能有NULL 吗？不是说连接结果，外连接当然会有NULL了。问这问题，是因为886老兄说没有考虑NULL. 你的答案为什么要加 distinct? 既然是ID, 通常就认为是唯一的。 -newkid(newkid); 2006-11-3 (#3302420@0)

A,B表里并不只有ID一个column，ID不唯一。原题是A是题目一里的Rev，B是一个类似结构的表Billing。 -bdbs(不多不少); 2006-11-3 (#3302444@0)

原来如此。最好题目出详细点，多弄些有意义的字段，例子数据 -newkid(newkid); 2006-11-3 (#3302479@0)

个人觉得, 这两个题作为面试题偏难, 即使应聘的人具备解决的知识和能力, 要想在面试的压力下快速准确地做出来的, 要么得极聪明, 要么得有极丰富的经验, 很难想象这两种人现在会低三下四地做题找工作 -poohbear(毛毛熊); 2006-11-3 (#3302297@0)

或者在天天准备面试的人。。。这种问题，网上interview问题一搜就可以知道答案了如果我碰到这样的问题，首先我会老实回答10分钟想不出来，除非有工具。。。
还有么，我可以在5分钟之内找到答案（至少近似），google, databse interview questiuons. -interview(intervieweree); 2006-11-3 {156} (#3302324@0)

我也是...^_^ -poohbear(毛毛熊); 2006-11-3 (#3302344@0)

干吗找工作非得低三下四地呀？是大公司的Senior position，应聘的人如果是低三下四的话，HR那里都过不了。：） -bdbs(不多不少); 2006-11-3 (#3302387@0)

离题一下，请你老板用c#或者java实现一下loki的design patterns的库。。。。 -interview(intervieweree); 2006-11-3 (#3302399@0)

人各有所长。这个职位是Senior Database Developer。不是.NET Developer。没必要这样去challenge人家。 -bdbs(不多不少); 2006-11-3 (#3302409@0)

Sr 级别的? 现在发简历还来得及嘛? 我感兴趣 -hard20(hard20); 2006-11-3 (#3302529@0)

不瞒你说，我一没有具体的Job Description，二也不知道HR那里谁负责收简历。你要有兴趣就上公司的网站上去找找看，我也不知道有没有post出来。祝你好运。 -bdbs(不多不少); 2006-11-3 (#3302603@0)

前几天这里还有人感慨, 越senior的职位面试越容易哪! 你们老板竟让人家做这么简单(概念上)具体的题... 说实话, 这两题我觉得虽然偏难, 但还适合我的水平, 我工作中也常碰到类似情况(我常来这里问的) -- 我是入门级别... -poohbear(毛毛熊); 2006-11-3 (#3302466@0)

没别的意思, 同情应聘者而已... 当然, 也许senior is supposed to 张口说出结果... 但是既然"面试了一堆人，都没有人做得出他的题目", 也许你们HR把关不严... -poohbear(毛毛熊); 2006-11-3 (#3302476@0)

我也挺同情他们的，也不知道这一堆人里面有没有咱们同胞。所以把题目贴过来给大家看看。HR不把技术关的。 -bdbs(不多不少); 2006-11-4 (#3302611@0)

这两个题都挺有意思的, 谢谢分享! -poohbear(毛毛熊); 2006-11-5 (#3304990@0)

@Florida

Topic

More Topics