MSSQL ROW_NUMBER() OVER (...)实现数据的分组、排序、过滤重复、取Top n记录

最新推荐文章于 2024-08-11 22:47:23 发布
原创 最新推荐文章于 2024-08-11 22:47:23 发布 · 1.2k 阅读 · 1 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#MSSQL #ROW_NUMBER OVER ...

本文介绍了如何利用MSSQL的ROW_NUMBER() OVER()函数进行数据分组、排序，以及过滤重复和选取Top n记录。通过partition by实现类似group by的功能，解决当某月份没有进展状态录入时，查询该月份之前最新进展的问题。通过添加行号rowNum，可以方便地进行数据筛选，避免完全重复的[CriterionID],[MissionIsFinish],[MissionMonth]记录。" 105435252,9462444,2017年百度杯CTF比赛爆破解析1-3,"['网络安全', 'CTF挑战', '漏洞利用', 'Web安全', 'Python编程'] 
select  [CriterionID],[MissionIsFinish] ,[MissionMonth] from 
(
select [CriterionID],[MissionIsFinish],[MissionMonth],
row_number() over(partition by [CriterionID] order by [MissionMonth] desc) as rowNum
from [fts].[v_TTM_DOAM_MissionList]
where [IsDel]=0 and [MissionYear]=2013 and [MissionMonth]<=12 and [MissionMonth]>0
group by [CriterionID],[MissionIsFinish],[MissionMonth]
) as tb
where rowNum=1

 

[fts].[v_TTM_DOAM_MissionList]列出了过往各工作内容(CriterionID)每个已录入月份(MissionMonth)的进展状态(MissionIsFinish),其中可能有的进展已经在过去的时间已经完成，后续不再录入，或者有的进展录入中间隔了n个月没录。

上面的代码是查询各进展最新月份(top 1)的记录， 如果要取top n，把最后的where 条件改为rowNum<=n即可

partition by 是用来分组的,可以理解为group by

该语句要实现的功能是：

查询工作内容(CriterionID)在当前月份(MissionMonth)的工作进展状态(MissionIsFinish)

问题在于：

如果我们要取工作内容某月份的进展状态，但是在该月份没有录入，则要取该月份之前的最新(最晚)的进展状态


结果为：

最低0.47元/天 解锁文章

200万优质内容无限畅学
row_number 和 cte 使用实例：求不连续的分段结果
文盲的专栏
04-03 690
我们长大之后，总觉得自己所学的知识没有用，其实不是没用，是因为你所学太少，根本想不到在哪里该用什么。
SQL 窗口函数深度解析：ROW_NUMBER 实战指南
hixiaoyang的博客
06-02 73
SQL窗口函数特别是ROW_NUMBER()实战指南摘要：窗口函数作为SQL高级功能，通过OVER()子句在特定行集合上计算而不合并结果行。ROW_NUMBER()按照指定排序为每行生成唯一序号，典型应用于分页查询(高效实现LIMIT-OFFSET)、数据去重(保留每组最新记录)和Top N分析(如各部门薪资前三)。相比RANK()和DENSE_RANK()，ROW_NUMBER()对相同值仍赋予不同序号。优化建议包括为PARTITION BY和ORDER BY字段创建复合索引，注意窗口函数在WHERE之后
Sql学习第七天——SQL 关于row_number()over()
weixin_30471561的博客
04-12 176
SQL 关于row_number()over() 今天用到了row_number()over()，现在就以知识点的形式总结一下，以便于以后的回顾。 -------------------参考资料MSDN------------------- 语法： ROW_NUMBER ( ) OVER ( [ PARTITION BY value_expression , ... [ n...
List＜Bean＞利用java8的特性进行分组排序过滤替换row_number() over（）函数
m0_50105929的博客
09-18 1055
最近在重构代码，原来的一个查询语句是这么写的。 select * from (select t.product_id, t.rate_id, t.year t.value, to_char(t.time, ‘‘yyyyMMddhh24miss’’) time, row_number() over(partition by t.product_id,t.rate_id,t.year order by t.time desc) rank from table t where t.id = :id) where
SQL中的分组与排序，提升查询性能的技巧
zgt_certificate的博客
08-11 2399
先过滤出价格不低于50的产品，然后按供应商分组，计算每个供应商的平均价格，最后筛选出平均价格大于100的供应商，并按平均价格降序排序。先按部门分组并统计每个部门的员工数量，然后按员工数量降序排列输出结果。此查询先按部门分组，统计员工数量并计算平均薪资，然后先按员工数量降序排列，再按平均薪资升序排列。在许多查询中，我们需要先对数据进行分组，再对分组后的结果进行排序。此查询按订单号分组，统计每个订单中商品的数量，并按商品数量降序排序。-此查询将所有员工按部门分组，并统计每个部门的员工数量。
mssql 追加行号 row_number()的用法
zha_zi的专栏
04-21 392
          在使用查询结果的时候需要生成一个新的行号用来做统计，oracle 中有这个功能，mssql 用得很少不清楚有没有这项功能， 搜索了一下，网上的解决方法乱七八糟，有限竟然需要一张临时表，我汗，最后发现了一个比较好的解决方法，row_number() select row_number() over(order by tablename.id) as row_number,*...
sql函数--04---ROW_NUMBER() OVER()函数用法详解
时光里的博客
10-26 2万+
ROW_NUMBER() OVER()函数 使用范围: 这个函数可以Oracle中使用 MySQL用不了 语法格式： row_number() over(partition by 分组列 order by 排序列 desc) 注意: 在使用 row_number() over()函数时候，over()里头的分组以及排序的执行晚于 where 、group by、 order by 的执行。 案例 1 表数据： create table TEST_ROW_NUMBER_OVER(
Mssql 增加排序列,分组排序与普通排序 ROW_NUMBER.docx
11-07
在Mssql中，`ROW_NUMBER()` 可以与 `OVER` 子句一起使用，来指定排序和分组规则。 1. **`ROW_NUMBER()` 语法**： `ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...) AS Seq` - `PARTITION BY` 子句用于将数据...
row_number 和 cte 使用实例：分组轮流排班
文盲的专栏
04-03 534
使用开窗函数，日期函数，列转行完成排班表
mssql sqlserver 分组排序函数row_number、rank、dense_rank用法简介及说明
qq_31864971的博客
07-11 5393
转载:http://www.maomao365.com/?p=5771摘要:在实际的项目开发中，我们经常使用分组函数,对组内数据进行群组后，然后进行组内排序：如：1：取出一个客户一段时间内，最大订单数的行记录2: 取出一个客户一段时间内，最后一次销售记录的行记录————————————————下文将讲述三个分组函数 row_number rank dense_rank的用法 ,以上三个函数的功能为...
SQL语句中ROW_NUMBER() OVER()函数的用法
qq_45748758的博客
05-14 1486
语法：ROW_NUMBER() OVER(PARTITION BY COLUMN ORDER BY COLUMN) PARTITION 分割 row_number() over()分组排序功能： 在使用 row_number() over()函数时候，over()里头的分组以及排序的执行晚于 where 、group by、 order by 的执行。 一、ROW_NUMBER() OVER (ORDER BY Column1 DESC) 根据Column1 降序排列，再为降序之后的每条数据加上序号。 二
mysql 获取行号 rownum
laokaizzz的专栏
08-03 6520
【一条sql语句完成，不set变量】 SELECT @rownum:=@rownum+1 AS rownum, frutas.*  FROM (SELECT @rownum:=0) r, frutas;
开窗函数ROW_NUMBER() OVER()函数用法详解，并巧妙使用ROW_NUMBER()函数进行数据去重
热门推荐
weixin_46574678的博客
01-18 3万+
ROW_NUMBER() OVER()函数用法详解，并巧用ROW_NUMBER()函数删除重复数据
MSSQL row_number简单使用语法
weixin_30918633的博客
07-24 370
MSSQL row_number简单使用语法 select * from ( select row_number() over(partition by threadid order by date desc) r,threadid,username from blogs ) tppp where tppp.r=1 tppp.r=1可以找出排重后按排序规则排名第...
使用Oracle的row_number() over函数过滤重复数据，取最新的记录
码疯的专栏
08-27 8840
问题：在项目中有一张设备检测信息表DEVICE_INFO_TBL, 每个设备每天都会产生一条检测信息，现在需要从该表中检索出每个设备的最新检测信息。也就是device_id字段不能重复，消除device_id字段重复的记录，而且device_id对应的检测信息test_result是最新的。 解决思路：用Oracle的row_number() over函数来解决该问题。解决过程： 1.查看表中的重复记录select     t.id,     t.device_id,     t.update_dtm,  
【SQL】ROW_NUMBER() OVER(partition by 分组列 order by 排序列)用法详解+经典实例
进三步的博客
12-03 7890
目录 0、填充数据 1、使用row_number()函数对订单进行编号，按照订单时间倒序。（此需求多用于分页） 2、所有订单按照客户进行分组，并按照客户下的订单的金额倒序排列。 3、筛选出客户第一次下的订单。 4、筛选出客户在‘2011年1月1日之后的第一次下的订单。 5、只保留每个客户的最近的一次订单，其余的订单删掉。（常用于删除重复数据） 6、统计每一个客户所有的订单中金额最大，并统计该订单是客户第几次购买； 正文 #用法说明 select row_number() ov..
MSSql Server基础学习系列———分组排序
ydm东方旭日的专栏
09-07 3091
--group by 分组 --分组后，select后只能显示分组依据和聚合函数 --如果一个sql语句中有group by和聚合函数,那么执行顺序是 --先分组,然后在每一组中执行聚合函数 select FClassId,count(*) as '班级人数',AVG(isnull(fage,0)) as '班级平均年龄' from newStudent group by FClassId se
rownum的SQL需求
bisal的专栏
02-13 2229
这是杂货铺的第424篇文章   CSDN正在进行博客之星的投票，还请为我投上宝贵的一票，各位能用下面两种方式，登陆投票地址， (1) 识别图中二维码 (2) 打开链接：https://bss.csdn.net/m/topic/blog_star2018/index 找到006号，点击“投票”，非常感谢！     《从一条"错误"的SQL，了解rownum的作用》提到的案例中，原始...
SQL rownum的用法
weixin_30788731的博客
03-30 2650
select * from v_video where reviewed=0 and diskserino='NA0DZA2G' and rownum<2 rownum只显示两行记录，第一行是字段名，第二行是满足查询条件的记录。 转载于:https://www.cnblogs.com/haoshine/p/5337317.html...
java中sql 语句中排序之后取第一条数据逻辑怎么实现
最新发布
08-23
<think>在Java中，执行SQL查询并获取排序后的第一条数据，通常可以通过在SQL语句中使用`ORDER BY`和`LIMIT`子句（或类似功能，取决于数据库系统）来实现。但是，不同的数据库系统可能有不同的语法。 例如： - 在MySQL中，使用`ORDER BY ... LIMIT 1` - 在Oracle中，可以使用`ORDER BY ... FETCH FIRST 1 ROW ONLY`（12c及以上版本）或者使用子查询和`ROWNUM` - 在SQL Server中，使用`TOP 1 ... ORDER BY` - 在PostgreSQL中，与MySQL类似，使用`ORDER BY ... LIMIT 1` 因此，在编写代码时，需要根据所使用的数据库类型来构建相应的SQL语句。 下面以几种常见的数据库为例，展示如何实现： ### 1. MySQL / PostgreSQL ```sql SELECT column1, column2, ... FROM table_name ORDER BY sort_column DESC LIMIT 1; ``` ### 2. Oracle (12c及以上) ```sql SELECT column1, column2, ... FROM table_name ORDER BY sort_column DESC FETCH FIRST 1 ROW ONLY; ``` ### 3. Oracle (11g及以下) ```sql SELECT * FROM ( SELECT column1, column2, ... FROM table_name ORDER BY sort_column DESC ) WHERE ROWNUM = 1; ``` ### 4. SQL Server ```sql SELECT TOP 1 column1, column2, ... FROM table_name ORDER BY sort_column DESC; ``` 在Java代码中，我们可以使用JDBC来执行这样的SQL查询。以下是一个使用JDBC的示例，假设我们使用的是MySQL数据库： ```java import java.sql.*; public class GetFirstSortedRecord { public static void main(String[] args) { String url = "jdbc:mysql://localhost:3306/your_database"; String user = "username"; String password = "password"; // 假设我们要从employees表中根据salary降序排序取第一条记录 String sql = "SELECT id, name, salary FROM employees ORDER BY salary DESC LIMIT 1"; try (Connection conn = DriverManager.getConnection(url, user, password); Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery(sql)) { if (rs.next()) { int id = rs.getInt("id"); String name = rs.getString("name"); double salary = rs.getDouble("salary"); System.out.println("ID: " + id + ", Name: " + name + ", Salary: " + salary); } else { System.out.println("No records found."); } } catch (SQLException e) { e.printStackTrace(); } } } ``` ### 解释： 1. 首先，我们建立数据库连接（需要替换url、user和password为实际的值）。 2. 定义SQL语句：使用`ORDER BY`对salary进行降序排序，然后使用`LIMIT 1`获取第一条记录。 3. 使用`Statement`对象执行查询，并获取`ResultSet`。 4. 由于我们只期望一条记录，使用`rs.next()`一次即可。如果存在记录，则从结果集中获取数据。 5. 在catch块中处理可能的SQL异常。 ### 注意： - 如果使用其他数据库，需要替换相应的JDBC驱动和SQL语句。 - 确保在项目中包含了对应数据库的JDBC驱动依赖。 ### 使用PreparedStatement（如果需要动态排序） 如果排序字段或顺序是动态的，为了避免SQL注入，建议使用`PreparedStatement`。但是，注意`ORDER BY`子句通常不能使用参数占位符（?）直接传递列名或排序方式（ASC/DESC），因为占位符只能用于值，不能用于列名或关键字。因此，在构建SQL语句时，需要确保动态部分的安全性，例如通过白名单验证。 示例（动态排序）： ```java String sortColumn = "salary"; // 假设从安全来源获取 String sortOrder = "DESC"; // 同样需要确保安全 // 注意：这里直接拼接了列名和排序顺序，需要确保它们的安全性（例如，通过白名单检查） String sql = "SELECT id, name, salary FROM employees ORDER BY " + sortColumn + " " + sortOrder + " LIMIT 1"; ``` 或者，使用不同的策略来构建安全的动态SQL。 ### 相关问题： §§相关问题§§: 1. 在Java中如何防止SQL注入？ 2. 如果数据库表数据量很大，使用排序取第一条数据是否会影响性能？如何优化？ 3. 除了使用SQL语句的LIMIT（或类似）子句，在Java中还有其他方式获取排序后的第一条数据吗？ 4. 在JDBC中如何处理多个排序字段的情况？</think>在Java中实现SQL查询排序后取第一条数据的逻辑，主要取决于使用的数据库类型。以下是不同数据库的实现方式及Java代码示例： ### 一、通用实现方案（推荐） 使用`ORDER BY`配合数据库特定的限制行数语法： ```java import java.sql.*; public class SqlFirstRecord { public static void main(String[] args) { String url = "jdbc:mysql://localhost:3306/your_db"; String user = "username"; String password = "password"; // 根据数据库类型选择SQL语句 String sql = ""; String dbType = "mysql"; // 根据实际数据库修改 switch(dbType.toLowerCase()) { case "mysql": case "postgresql": sql = "SELECT * FROM employees ORDER BY salary DESC LIMIT 1"; break; case "oracle": sql = "SELECT * FROM (SELECT * FROM employees ORDER BY salary DESC) WHERE ROWNUM = 1"; break; case "sqlserver": sql = "SELECT TOP 1 * FROM employees ORDER BY salary DESC"; break; } try (Connection conn = DriverManager.getConnection(url, user, password); Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery(sql)) { if (rs.next()) { System.out.println("Highest salary employee: " + rs.getString("name") + " - $" + rs.getDouble("salary")); } else { System.out.println("No records found"); } } catch (SQLException e) { e.printStackTrace(); } } } ``` ### 二、数据库特定语法详解 1. **MySQL/PostgreSQL/SQLite** ```sql SELECT * FROM table ORDER BY sort_column DESC LIMIT 1 ``` 2. **Oracle** ```sql SELECT * FROM ( SELECT * FROM table ORDER BY sort_column DESC ) WHERE ROWNUM = 1 ``` 3. **SQL Server** ```sql SELECT TOP 1 * FROM table ORDER BY sort_column DESC ``` ### 三、使用ROW_NUMBER()的通用写法（适用于所有支持窗口函数的数据库） ```java String sql = "SELECT * FROM (" + " SELECT *, ROW_NUMBER() OVER (ORDER BY salary DESC) as rn " + " FROM employees" + ") WHERE rn = 1"; ``` ### 四、PreparedStatement动态参数示例 ```java public Employee getTopEmployee(String sortColumn, boolean ascending) throws SQLException { String direction = ascending ? "ASC" : "DESC"; String sql = "SELECT * FROM employees ORDER BY " + sortColumn + " " + direction + " LIMIT 1"; try (Connection conn = dataSource.getConnection(); PreparedStatement pstmt = conn.prepareStatement(sql)) { ResultSet rs = pstmt.executeQuery(); if (rs.next()) { return new Employee( rs.getInt("id"), rs.getString("name"), rs.getDouble("salary") ); } } return null; } ``` ### 关键注意事项 1. **性能优化**：在排序列上建立索引可大幅提升查询速度 2. **空结果处理**：始终检查`ResultSet.next()`的返回值 3. **SQL注入防护**：排序字段名不能使用PreparedStatement参数化，需通过白名单验证 ```java // 安全验证示例 Set<String> validColumns = Set.of("salary", "hire_date"); if (!validColumns.contains(sortColumn)) { throw new IllegalArgumentException("Invalid sort column"); } ``` ### 不同数据库的JDBC驱动依赖 | 数据库 | Maven依赖 | |-----------|-----------| | MySQL | `mysql:mysql-connector-java` | | PostgreSQL| `org.postgresql:postgresql` | | Oracle | `com.oracle.database.jdbc:ojdbc11` | | SQL Server| `com.microsoft.sqlserver:mssql-jdbc` |
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值