小蝦米, 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