SQL 查询语句（备份）

分组查询

1、分组查询后获取分组后的前5条数据

SELECT * FROM (SELECT rid = ROW_NUMBER() OVER (PARTITION BY FoodParentID ORDER BY FoodParentID DESC), * FROM FoodLibInfo) AS T WHERE rid <= 5 

2、分组查询后随机获取每一组的5条数据

SELECT * FROM (SELECT rid = ROW_NUMBER() OVER (PARTITION BY FoodParentID ORDER BY newid()), * FROM FoodLibInfo) AS T WHERE rid <= 5 

 排名查询：

1、使用ROW_NUMBER()函数，分数相同按照Id升序排名

SELECT ID,result,ROW_NUMBER() over (order by result desc,ID Asc) as RowId FROM answer group by Q_user_ID,Q_result

2、分数相同的排名相同

SELECT a.ID,a.result,
Place=(SELECT COUNT(result) FROM answer WHERE result>a.result )+1
FROM answer a where ORDER BY Place 

 

可能有用的：http://www.cnblogs.com/zengxiangzhan/archive/2009/09/23/1572276.html