ROW_NUMBER (Transact-SQL)
SQL number row
2023-09-14 09:03:14 时间
ROW_NUMBER ( ) OVER ( [ PARTITION BY value_expression , ... [ n ] ] order_by_clause )
除非以下条件成立,否则不保证在每次执行时,使用 ROW_NUMBER() 的查询所返回行的顺序完全相同。
以下示例根据销售人员年初至今的销售额,计算 Adventure Works Cycles 中销售人员的行号。
SELECT ROW_NUMBER() OVER(ORDER BY SalesYTD DESC) AS Row, FirstName, LastName, ROUND(SalesYTD,2,1) AS "Sales YTD" FROM Sales.vSalesPerson WHERE TerritoryName IS NOT NULL AND SalesYTD 0;
Row FirstName LastName SalesYTD --- ----------- ---------------------- ----------------- 1 Linda Mitchell 4251368.54 2 Jae Pak 4116871.22 3 Michael Blythe 3763178.17 4 Jillian Carson 3189418.36 5 Ranjit Varkey Chudukatil 3121616.32 6 José Saraiva 2604540.71 7 Shu Ito 2458535.61 8 Tsvi Reiter 2315185.61 9 Rachel Valdez 1827066.71 10 Tete Mensa-Annan 1576562.19 11 David Campbell 1573012.93 12 Garrett Vargas 1453719.46 13 Lynn Tsoflias 1421810.92 14 Pamela Ansman-Wolfe 1352577.13
下面的示例按 OrderDate 的顺序计算 SalesOrderHeader 表中所有行的行号,并只返回行 50 到 60(含)。
以下示例使用 PARTITION BY 参数按列 TerritoryName 对结果集进行分区。 在 OVER 子句中指定的 ORDER BY 子句按列 SalesYTD 对每个分区中的行进行排序。 SELECT 语句中的ORDER BY 按 TerritoryName 子句对整个查询结果集进行排序。
SELECT FirstName, LastName, TerritoryName, ROUND(SalesYTD,2,1), ROW_NUMBER() OVER(PARTITION BY TerritoryName ORDER BY SalesYTD DESC) AS Row FROM Sales.vSalesPerson WHERE TerritoryName IS NOT NULL AND SalesYTD 0 ORDER BY TerritoryName;
FirstName LastName TerritoryName SalesYTD Row --------- -------------------- ------------------ ------------ --- Lynn Tsoflias Australia 1421810.92 1 José Saraiva Canada 2604540.71 1 Garrett Vargas Canada 1453719.46 2 Jillian Carson Central 3189418.36 1 Ranjit Varkey Chudukatil France 3121616.32 1 Rachel Valdez Germany 1827066.71 1 Michael Blythe Northeast 3763178.17 1 Tete Mensa-Annan Northwest 1576562.19 1 David Campbell Northwest 1573012.93 2 Pamela Ansman-Wolfe Northwest 1352577.13 3 Tsvi Reiter Southeast 2315185.61 1 Linda Mitchell Southwest 4251368.54 1 Shu Ito Southwest 2458535.61 2 Jae Pak United Kingdom 4116871.22 1
相关文章
- sql语法:inner join on, left join on, right join on详细使用方法
- MyBatis基础-04-动态sql
- PL/SQL题型代码示例
- herbnate session.createSQLQuery(sql) 和 session.createQuery(sql)使用
- Sql 四大排名函数(ROW_NUMBER、RANK、DENSE_RANK、NTILE)简介
- SQL Server调优系列基础篇(并行运算总结)
- 读书笔记--SQL必知必会06--用通配符进行过滤
- Oracle v$sql,v$sqlarea,v$sqltext区别
- ROW_NUMBER (Transact-SQL)
- 统计分析SQL Server Profiler 跟踪的SQL
- 已解决UnboundLocalError: local variable ‘sql‘ referenced before assignment
- DTSE Tech Talk 第18期丨统计信息大揭秘,数仓SQL执行优化之密钥
- 解决java.sql.SQLException: ORA-01789: query block has incorrect number of result columns
- 大数据不就是写sql吗?—— Hive:把sql解析后用MapReduce跑 SparkSQL:把sql解析后用Spark跑,比hive快点 Drill/Impala/Presto:交互式查询OLAP Druid/Kylin:强调预计算,同样是OLAP
- SQL Server存储过程
- Yii查看执行的SQL