SQL 查询语句（备份）

最新推荐文章于 2022-10-14 16:04:43 发布

weixin_34129696

最新推荐文章于 2022-10-14 16:04:43 发布

阅读量135

点赞数

原文链接：http://www.cnblogs.com/xiezhi-sanlang/p/3156830.html

版权

分组查询

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

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

View Code

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

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

View Code

排名查询：

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

View Code

2、分数相同的排名相同

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

View Code

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

转载于:https://www.cnblogs.com/xiezhi-sanlang/p/3156830.html

关注