0313 ROW_NUMBER()CTE

下午网上查询 测试DataList实现分页功能未成功

晚上 ROW_NUMBER() 排序加行号 CTE

---------------------------------------------------------------

ROW_NUMBER() OVER([PARTITION BY COLUMN ORDER BY COLUMN)

 

row_number()OVER(ORDER BY COl DESC)表示按col降序排列 再每行从1开始加上行号列

row_number() OVER (PARTITION BY COL1 ORDER BY COL2) 表示根据COL1分组，在分组内部根据 COL2排序

select *, ROW_NUMBER() over(order by GroupName DESC)as Number from ContactGroup

 

 

select *, ROW_NUMBER() over(partition by GroupName order by UserId DESC)as Number from ContactGroup

-------------------------------------------------------------------

CTE(Common Table Expression) ，即公用表表达式，可以认为是在单个 SELECT、INSERT、UPDATE、DELETE 或 CREATE VIEW 语句的执行范围内定义的临时结果集。CTE 与派生表类似，具体表现在不存储为对象，并且只在查询期间有效。与派生表的不同之处在于，CTE 可自引用，还可在同一查询中引用多次。

CTE可用于 ：　　

1.创建递归查询　　2.在同一语句中多次引用生成的表

 

MSDN :

[ WITH <common_table_expression> [ ,...n ] ]

<common_table_expression>::=
    expression_name [ ( column_name [ ,...n ] ) ]
    AS
    ( CTE_query_definition )

 

eg:

USE AdventureWorks2008R2;
GO
-- Define the CTE expression name and column list.
WITH Sales_CTE (SalesPersonID, SalesOrderID, SalesYear)
AS
-- Define the CTE query.
(
    SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear
    FROM Sales.SalesOrderHeader
    WHERE SalesPersonID IS NOT NULL
)
-- Define the outer query referencing the CTE name.
SELECT SalesPersonID, COUNT(SalesOrderID) AS TotalSales, SalesYear
FROM Sales_CTE
GROUP BY SalesYear, SalesPersonID
ORDER BY SalesPersonID, SalesYear;
GO