首页 > 数据库 >SQL-ROWNUMBER-OVER汇总

SQL-ROWNUMBER-OVER汇总

时间:2024-03-26 11:55:19浏览次数:21  
标签:COMPANY ROWNUMBER ORDER field num 分组 SQL OVER SELECT

目标:分组并读分组内的数据进行编号排序

实现方案

ROW_NUMBER() OVER()

具体实现

ORACLE

Select b.*,b.Fbqd, b.Qzsm
From 
(
  Select Row_Number() Over(Partition By Cpdm, Fbqd Order By Ksrq Desc) iRow, Cpid, Cpdm,Djxh,Djmc,Fbqd   From t_biao
) b

SQLite

SELECT year, product_id, amount,
       ROW_NUMBER() OVER (PARTITION BY year, product_id ORDER BY year,     product_id, amount) AS rownum
FROM sales;

 

MSSQL

SELECT 
  ROW_NUMBER() OVER(PARTITION BY recovery_model_desc ORDER BY name ASC) 
    AS Row#,
  name, recovery_model_desc
FROM sys.databases WHERE database_id < 5;

MYSQL

示例一:

MYSQL(<5.8)

用法:
SELECT IF(@temp=列名1, @rank:=@rank+1,@rank:=1) iRow, @temp:=列名1, 列名2, ......, 列名n FROM 表 ORDER BY 列名1,列名2, 升序或降序;
SELECT 
    IF(@temp=datetime, @rank:=@rank+1, @rank:=1) rn, /*结果集编号*/
    @temp:=datetime, 
    createdate 
FROM jd_byjhplan 
ORDER BY datetime, createdate ASC;/*分组、排序*/

 

MYSQL(>5.8)

SELECT *
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (ORDER BY column_name) AS row_num
    FROM your_table
) AS subquery
WHERE row_num <= 10;

 

示例二:

单字段分组

按照公司分组、创建时间排序,并对每一组内的行进行编号

现在这里有一张用户表 user,里面包含以下字段:ID 主键、USERNAME 用户名、PASSWORD 密码、COMPANY 公司、DEPT 部门、CREATE_TIME 创建时间

MYSQL(8.0)

SELECT
    t.*,
    row_number() over(PARTITION BY t.COMPANY ORDER BY t.CREATE_TIME) AS ROW_NO 
FROM
    user t 
WHERE
    t.ID > 0;

MYSQL(5.7)

SELECT
    t.*,
    @num := IF(@field_1 <=> t.COMPANY, @num + 1, 1) AS ROW_NO,
    @field_1 := t.COMPANY AS FIELD_1
FROM
    user t,
    (SELECT @num := 0, @field_1 := NULL) a 
WHERE
    t.ID > 0 
ORDER BY
    t.COMPANY,
    t.CREATE_TIME;

说明:

这个 SQL 语句中使用用户变量 @num 和 @field_1,分别表示当前行的排序编号和前一个分组的 COMPANY 字段的值。

在 SELECT 子句中,使用了 IF 函数来判断当前行是否与前一行属于同一分组,如果是,将当前行的排序编号加 1,否则,将排序编号重置为 1。

在 ORDER BY 子句中,需要将分组字段 COMPANY 放在前面,将排序字段 CREATE_TIME 放在后面。

 多字段分组

按照公司和部门分组、创建时间排序,并对每一组内的行进行编号

MYSQL(8.0)

SELECT
    t.*,
    row_number() over(PARTITION BY t.COMPANY,t.DEPT ORDER BY t.CREATE_TIME) AS ROW_NO 
FROM
    user t 
WHERE
    t.ID > 0;

MYSQL(5.7)

SELECT
    t.*,
    @num := IF(@field_1 <=> t.COMPANY && @field_2 <=> t.DEPT, @num + 1, 1) AS ROW_NO,
    @field_1 := t.COMPANY AS FIELD_1,
    @field_2 := t.DEPT AS FIELD_2 
FROM
    user t,
    (SELECT @num := 0, @field_1 := NULL, @field_2 := NULL) a 
WHERE
    t.ID > 0 
ORDER BY
    t.COMPANY,
    t.DEPT,
    t.CREATE_TIME;

说明:

这个 SQL 语句中使用用户变量 @num 、@field_1 和 @field_2,分别表示当前行的排序编号、前一个分组的 COMPANY 字段的值和 DEPT 字段的值。

在 SELECT 子句中,使用了 IF 函数来判断当前行是否与前一行属于同一分组,如果是,将当前行的排序编号加 1,否则,将排序编号重置为 1。

在 ORDER BY 子句中,需要将分组字段 COMPANY 和 DEPT 放在前面,将排序字段 CREATE_TIME 放在后面。

 

注意:
使用用户变量来实现类似 ROW_NUMBER() OVER(PARTITION BY ... ORDER BY ...) 函数的分组排序编号效果,可能会影响查询的性能和稳定性,应该谨慎使用

 

标签:COMPANY,ROWNUMBER,ORDER,field,num,分组,SQL,OVER,SELECT
From: https://www.cnblogs.com/oumi/p/18096339

相关文章

  • c# 操作SQLite数据库
    1、在nuget工具中安装“System.Data.SQLite”插件;2、将数据库文件放在DEBUG目录下(表文件可以通过navicate建立也);3、创建DbHelperSQLite.cs文件,指定数据库路径等信息;usingSystem;usingSystem.Collections;usingSystem.Data.Common;usingSystem.Data;usingSystem.Da......
  • SQL-部分函数替代
    SQL-优化部分函数替代方案:LIKE替换1.MSSQL替代函数MSSQL语法selectCHARINDEX(‘Zhang’,‘Devil_Zhang’)运行结果:72.MySQL替代函数MySQL语法selectinstr('Devil_Zhang','Zhang')运行结果:73.Oracle替代函数SELECT INSTR('012134','0121')FROMDUAL;运行......
  • 数据库的四个特性?MySQL是如何实现的?
    首先MySQL中,数据库的四个特性分为:原子性一致性隔离性持久性也就是我们常说的ACID。那么这四个特性数据库是如何实现的呢?持久性--->redolog:redolog(重做日志):redolog本身是一种日志,记录的是事务提交时对数据页(MySQL在硬盘中存储数据文件的最小单位,由数据行组成)的......
  • Docker下/var/lib/docker/overlay2清理
     查询镜像(Images)、容器(Containers)和本地卷(LocalVolumes)等空间使用大户的空间占用情况dockersystemdf#详细信息dockersystemdf-v清理磁盘,删除关闭的容器、无用的数据卷和网络,以及dangling镜像(即无tag的镜像)dockersystemprune#命令清理得更加彻底,可以将没......
  • 别让 Docker 毁了你的 MySQL!
    Docker容器技术的火热,越来越多的应用开始被"装箱"。我们这些开发者享受着Docker带来的便捷和灵活,几乎恨不得把所有东西都塞进容器里。于是,连MySQL这样的有状态服务,也开始频频出现在Docker的舞台上。但是,把MySQL装进Docker,真的是个明智的选择吗?是否会给系统稳......
  • 关于SQL Server数据库中的用户权限和角色管理
    简介在SQLServer数据库系统中,管理用户权限和角色对于确保数据安全、完整性和可访问性至关重要。在本文中,我们将探讨在SQLServer数据库中创建用户、分配权限和管理角色的过程。我们将涵盖基本概念,并提供带有SQL代码片段的实际示例。引言用户管理涉及创建用户帐户、分配适当的......
  • Centos7安装mysql8.0
    一、卸载MariaDB#查看版本rpm–qa|grepmariadb#卸载rpm–e--nodeps文件名#查看是否卸载干净rpm–qa|grepmariadb 二、安装mysql1、下载资源包 官网下载https://dev.mysql.com/downloads/mysql/Wget下载1、下载​wgethttps://dev.mysql.com/get/D......
  • MySQL Delete 表数据后,磁盘空间并未释放,为什么?
    有开发小哥咨询了一个问题,记录一下处理过程分享给有需要的朋友。问题如下:MySQL数据库中有几张表增删比较频繁、数据变动剧烈且数据量大,导致数据增长过快,磁盘占用多。为了节约成本,定期进行数据备份,并通过delete删除表记录,但是执行delete操作后发现磁盘空间并未释放,这是为什么?MySQL......
  • [附源码]计算机毕业设计高校教材管理系统(JSP+java+springmvc+mysql+MyBatis)
    本项目包含程序+源码+数据库+LW+调试部署环境,文末可获取一份本项目的java源码和数据库参考。项目文件图项目介绍随着高校教育资源的不断扩充和教育体系的日益复杂化,高效的教材管理变得尤为重要。一个专业的高校教材管理系统能够实现教材信息的集中管理、库存状态实时监控、......
  • [附源码]计算机毕业设计大学生创新项目管理系统(JSP+java+springmvc+mysql+MyBatis)
    本项目包含程序+源码+数据库+LW+调试部署环境,文末可获取一份本项目的java源码和数据库参考。项目文件图项目介绍随着高等教育的不断发展,大学生创新项目成为培养学生创新能力和实践能力的重要途径。有效的项目管理对于确保创新项目的顺利进行和高质量完成至关重要。然而,目前......