小虾米, a couple of solutions for your query in sql server 2005+

很好的题目，相信应该有4种解法，不过这两种最常用（起码对我来说，其它的麻烦点）。很久前面试中碰到过，人家要求5分钟，我用了半分钟，给了他们下面两个solution，当场拍板要俺了，真的应当记住的。

没有你说的demo table,就用northwind的employees table。把你的query变为找出每个country中最年轻的2个employee.


1. top n [with tie]

select employeeid,country,birthdate
from employees e1
where employeeid in
(
select top 2 employeeid from employees e2 where e1.country=e2.country
order by birthdate desc )
order by country,employeeid

2. cte && rank (or row_number())

with dobcte as
(
select employeeid, country, birthdate,rank() over (partition by country order by birthdate desc) as rk
from employees
)

select * from dobcte
where rk order by country, employeeid

result:

6 UK 1963-07-02 00:00:00.000
9 UK 1966-01-27 00:00:00.000
3 USA 1963-08-30 00:00:00.000
8 USA 1958-01-09 00:00:00.000

所有跟帖： 

• 强人！这也是我的面试题，我FAIL了。这两天正疯补呢。 -小虾米- ♀ (0 bytes) () 07/21/2009 postreply 06:57:20

• 这都是平时数据处理中很基本的东西. 你是不是新手呀? -小谋- ♀ (0 bytes) () 07/21/2009 postreply 07:20:11

• 我平时用的不多，都是很简单的QUERY和TABLE。 -小虾米- ♀ (0 bytes) () 07/21/2009 postreply 07:37:20

• 半分钟，我都抄不完？ -竹杉林- ♂ (24 bytes) () 07/21/2009 postreply 09:33:08

• 小撅 -眼冒金星- ♂ (0 bytes) () 07/21/2009 postreply 15:38:11

• i just talked it through, rather than actually coding it, haha~~ -撅着挺好- ♂ (0 bytes) () 07/21/2009 postreply 18:48:52

• 回复：小虾米, a couple of solutions for your query in sql server 2005+ -旺旺旺旺- ♀ (0 bytes) () 07/21/2009 postreply 19:48:13