SELECT TOP 30 * FROM ARTICLE WHERE ID NOT IN(SELECT TOP 45000 ID FROM ARTICLE ORDER BY YEAR DESC, ID DESC) ORDER BY YEAR DESC,ID DESC

SELECT * FROM (  SELECT TOP 30 * FROM (SELECT TOP 45030 * FROM ARTICLE ORDER BY YEAR DESC, ID DESC) f ORDER BY f.YEAR ASC, f.ID DESC) s ORDER BY s.YEAR DESC,s.ID DESC

SELECT * FROM ARTICLE w1, 
(
    SELECT TOP 30 ID FROM 
    (
        SELECT TOP 50030 ID, YEAR FROM ARTICLE ORDER BY YEAR DESC, ID DESC
    ) w ORDER BY w.YEAR ASC, w.ID ASC
) w2 WHERE w1.ID = w2.ID ORDER BY w1.YEAR DESC, w1.ID DESC

SELECT * FROM ARTICLE w1 
    WHERE ID in 
        (
            SELECT top 30 ID FROM 
            (
                SELECT top 45030 ID, YEAR FROM ARTICLE ORDER BY YEAR DESC, ID DESC
            ) w ORDER BY w.YEAR ASC, w.ID ASC
        ) 
    ORDER BY w1.YEAR DESC, w1.ID DESC

SELECT w2.n, w1.* FROM ARTICLE w1, (  SELECT TOP 50030 row_number() OVER (ORDER BY YEAR DESC, ID DESC) n, ID FROM ARTICLE ) w2 WHERE w1.ID = w2.ID AND w2.n > 50000 ORDER BY w2.n ASC

/**author：熊泽        date：2021-04-16    project：SqlServer存储过程应用二：分页查询数据并动态拼接where条件*/
--创建一个查询学生的存储过程
CREATE PROCEDURE ProcedureStudent
    @pageIndex INT ,        --当前页（如1：第1页）
    @pageCount INT,            --每页条数（如50：每页50条）
    @rowTotal INT OUTPUT ,    --返回的总行数 
    @strWhere VARCHAR(5000) --程序动态拼接的sql查询条件
AS
BEGIN
    /**begin创建拼接动态条件 */
    DECLARE @sq_temp AS VARCHAR(2000)  --定义拼接后的sql语句
    CREATE TABLE #temp (  --创建一个拼接查询条件查询出来的结果用于做子查询
        Number VARCHAR(50)
    )
    SET @sq_temp ='SELECT Number FROM a_Students where 1 = 1 '
                + CASE WHEN ISNULL(@strWhere,'') = '' THEN '' ELSE @strWhere END --动态拼接的条件
    --将拼接的条件写入临时表
    INSERT INTO #temp (Number) EXEC (@sq_temp);
    /**end创建拼接动态条件 */
     --分页查询语句sql
     SELECT * FROM (SELECT  
                        row_number()over (order by a.Number desc)Id,
                        a.Number 学号 ,
                        a.Name 姓名 ,
                        b.ClassName 班级 ,
                        c.Java ,
                        c.Python ,
                        c.C# ,
                        c.SqlDB
                FROM    a_Students a
                        LEFT JOIN a_StudentClass b ON a.ClassId = b.ClassId
                        LEFT JOIN a_StudentsScore c ON a.Number = c.Number
                        WHERE  a.Number IN (SELECT  Number FROM #temp)
                       )temp
       WHERE temp.Id between (@pageIndex-1)*@pageCount+1 and @pageIndex*@pageCount;
       --返回总条数
       SELECT  @rowTotal=COUNT(*)  FROM dbo.a_Students WHERE Number IN (SELECT  Number FROM #temp)
       DROP TABLE #temp  --删除临时表
END
GO