ROW_NUMBER (Transact-SQL)

SELECT   ROW_NUMBER() OVER ( ORDER BY MemberID DESC ) ,
         *
FROM     dbo.tbm_mem_Member_Beneficiary
ORDER BY MemberID;

 

 

DECLARE @MyTable TABLE
    (
        TableName NVARCHAR(100) NOT NULL ,
        RowNumber INT NOT NULL
    );
INSERT INTO @MyTable
            SELECT TABLE_NAME ,
                   ROW_NUMBER() OVER ( ORDER BY TABLE_NAME ASC )
            FROM   INFORMATION_SCHEMA.TABLES
            WHERE  TABLE_TYPE = 'BASE TABLE'
                   AND TABLE_NAME LIKE 'tbm_cih_CustomTableItemHistory_%';

DECLARE @CurrentNumber INT = 1;
DECLARE @MaxRowNumber INT = 0;
DECLARE @TableName NVARCHAR(100);
DECLARE @Sql NVARCHAR(200);

SELECT @MaxRowNumber = MAX(RowNumber)
FROM   @MyTable;
WHILE ( @CurrentNumber <= @MaxRowNumber )
    BEGIN
        SELECT @TableName = TableName
        FROM   @MyTable
        WHERE  RowNumber = @CurrentNumber;
        SET @Sql = 'SELECT * FROM ' + @TableName
                   + ' WHERE CustomTableItemHistoryID IS NULL ';
        --PRINT @Sql;
        EXEC ( @Sql );
        SET @CurrentNumber = @CurrentNumber + 1;
    END;

 

先从表里面筛选数据，并且添加RowNumber列，把这个筛选结果作为result

然后从result中进行二次筛选，通过pagesize和pageindex来进行分页

 SELECT   TOP ( @PageSize ) CreatedOn ,
                                   PointsType ,
                                   Points ,
                                   Concept
        FROM     (   SELECT ROW_NUMBER() OVER ( ORDER BY CustomTableItemID DESC ) AS RowNumber ,
                            CreatedOn ,
                            PointsType ,
                            Points ,
                            Concept
                     FROM   dbo.vie_cti_19 
                     WHERE  BeneficiaryID = @MemberID
                            AND CompanyID = @CompanyID ) result
        WHERE    result.RowNumber > ( @PageIndex - 1 ) * @PageSize
        ORDER BY result.RowNumber;

 

 

generate row numbers in the same order the data are added.

从表里筛选数据，新增一列，这一列标记自然顺序

https://blog.sqlauthority.com/2015/05/05/sql-server-generating-row-number-without-ordering-any-columns/

But what if you want to generate row numbers in the same order the data are added.

Can you omit the ORDER BY Clause?

SELECT *,ROW_NUMBER() OVER () AS SNO FROM #TEST

The above throws the following error

Msg 4112, Level 15, State 1, Line 1
The function ‘ROW_NUMBER’ must have an OVER clause with ORDER BY.

But there is a way. Just do not ORDER BY any columns, but ORDER BY a literal value as shown below

SELECT *,ROW_NUMBER() OVER (ORDER BY (SELECT 100)) AS SNO FROM #TEST

In place of SELECT 100, you can use anything like SELECT 1, SELECT ‘A’, SELECT NULL, etc

 

 通过id和筛选出来的自然排序进行分组

有一张表TestPartition，有两列数据

WITH temp
AS ( SELECT Id ,
            [number] ,
            ROW_NUMBER() OVER ( ORDER BY ( SELECT 1 )) AS SNO
     FROM   dbo.TestPartition )
SELECT   *
FROM     temp
GROUP BY id ,
         sno ,
         [number]

 

有了自然排序后，可以分组求和。（每一行都有一个累加）

https://www.cnblogs.com/zhaoguan_wang/p/4632071.html

WITH temp2
AS ( SELECT Id ,
            [number] ,
            ROW_NUMBER() OVER ( ORDER BY ( SELECT 1 )) AS SNO
     FROM   dbo.TestPartition )
SELECT temp2.Id ,
       temp2.number ,
       SUM(number) OVER ( PARTITION BY temp2.Id
                          ORDER BY temp2.SNO ) AS 'number 累计值'
FROM   temp2

partition by的字段用来进行分组

order by的字段用来决定累加的顺序

 

作者:Chuck Lu    GitHub