sql分组排序，分页查询

1.查询每个人，拥有数量最多的两条数据

;WITH tab AS (SELECT row_number() OVER(PARTITION BY name ORDER BY amount desc) AS row,* FROM table)
SELECT * FROM tab WHERE row<=
           

2.查询每门课程不低于80分的学生信息

select  * from table GROUP BY name having  socre >=

//也可以用排除法 把成绩小于的学生排除掉
select name from score where name not in(
    select name from score where score < )
           

3.排序查询第 20-40

;WITH  A  AS (
SELECT 
row_number() OVER(ORDER BY datetime DESC) AS rowIndex ,*
FROM table where delete=) 
 SELECT *  FROM A WHERE A.rowIndex <=  and A.rowIndex >
//这种方法会把表里的数据全部排序后，进行查询，所以要加上where条件，进行部分排序，不然表数据太多的话，性能下降的很快
           

或者

SELECT TOP  * FROM table 
WHERE id NOT IN (
SELECT TOP  id FROM table)
           

或者

SELECT * FROM TABLE  WHERE id IN (
SELECT TOP  id FROM TABLE ORDER BY id) ORDER BY id desc
//这种方法很少用，而且也难处理，性能没前两个好，当初某面试官给我说的一种方法