SQL 查询语句(备份)
分组查询
1、分组查询后获取分组后的前5条数据
1 SELECT * FROM (SELECT rid = ROW_NUMBER() OVER (PARTITION BY FoodParentID ORDER BY FoodParentID DESC), * FROM FoodLibInfo) AS T WHERE rid <= 5
2、分组查询后随机获取每一组的5条数据
1 SELECT * FROM (SELECT rid = ROW_NUMBER() OVER (PARTITION BY FoodParentID ORDER BY newid()), * FROM FoodLibInfo) AS T WHERE rid <= 5
排名查询:
1、使用ROW_NUMBER()函数,分数相同按照Id升序排名
1 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