请教如何取出 GROUP BY 之后每组前N条数据

Question

Date               City    Count2011-07-07         深圳    1
2011-07-07         深圳    2
2011-07-07         北京    3
2011-07-07         北京    4
2011-07-07         广州    5
2011-07-07         广州    6
2011-07-07         上海    7
2011-07-07         上海    82011-07-06         深圳    8
2011-07-06         深圳    7
2011-07-06         北京    6
2011-07-06         北京    5
2011-07-06         广州    4
2011-07-06         广州    3
2011-07-06         上海    2
2011-07-06         上海    1
SELECT 
Date, City, SUM(Count) AS Count
FROM tb
GROUP BY Date , City
ORDER BY Date DESC, Count DESC得到
Date               City    Count
2011-07-07         上海    15
2011-07-07         广州    11
2011-07-07         北京    7
2011-07-07         深圳    32011-07-06         深圳    15
2011-07-06         北京    11
2011-07-06         广州    7
2011-07-06         上海    3然后怎么能取到每天前2名的City呢？ 就是这样的结果2011-07-07         上海    15
2011-07-07         广州    112011-07-06         深圳    15
2011-07-06         北京    11

Answer 1

假设TT为VIEW，代码为你上述的SQL
(SELECT  
Date, City, SUM(Count) AS Count
FROM tb
GROUP BY Date , City
ORDER BY Date DESC, Count DESC)select * from tt a where 2>=(select count(*) from tt where 
a.Date=Date and a.Count<=Count )参考 ：
http://topic.csdn.net/u/20091231/16/2f268740-391e-40f2-a15e-f243b2c925ab.html?67923
中我的回答