开窗函数-排名

编辑
  • 文档创建者:zsh331
  • 浏览次数:4113次
  • 编辑次数:8次
  • 最近更新:Kevin-s 于 2019-06-18
  • 注意事项:由于设计器内置的是 Sqlite 数据库,在使用上很多语法都不支持,请将 Sqlite 数据库文件迁移到主流数据库(MySql、MSSQL、Oracle…)后运行。

    迁移方法使用第三方软件迁移内置 FRDemo 数据库到指定数据库

    本文运行环境:Microsoft SQL Server 2012 - 11.0.2100.60 (X64) 

                         Copyright (c) Microsoft Corporation

                         Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)


    1. 问题描述

    对于一些特殊的数据表,我们想对它的某一数据列进行排序,表中又不存在排序字段,但又不想在模板中进行排序处理,此时我们就需要在建立数据集时直接通过 SQL 进行处理了。


    2. 实现思路

    在创建数据集时用 SQL 的开窗排名函数处理,然后进行直接调用。


    3. 函数介绍

    下面主要解析四种常用的排序开窗函数: 


    3.1、ROW_NUMBER() OVER ()  

    ROW_NUMBER 直接分组,前面的序号唯一且连续


    3.2、RANK() OVER ()RANK()

    RANK()并列排序,值相同序号并列,后面的值跳跃


    3.3、DENSE_RANK() OVER ()

    DENSE_RANK 并列排序,值相同序号并列,后面的值连续


    3.4、NTILE(n) OVER ()

    不常用,NTILE(4)将所有的行分为 4 组,然后 10/4=2 余 2,表示每组 2 行,前面的 2 行+1(3 行)


    4. 示例

    4.1准备数据

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

    -- Table structure for test_table

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

    DROP TABLE [dbo].[test_table]

    GO

    CREATE TABLE [dbo].[test_table] (

    [id] int NOT NULL IDENTITY(1,1) ,

    [name] nvarchar(50) NULL ,

    [price] nvarchar(50) NULL 

    )

    GO


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

    -- Records of test_table

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

    SET IDENTITY_INSERT [dbo].[test_table] ON

    GO

    INSERT INTO [dbo].[test_table] ([id], [name], [price]) VALUES (N'141', N'香蕉', N'20')

    GO

    GO

    INSERT INTO [dbo].[test_table] ([id], [name], [price]) VALUES (N'142', N'苹果', N'25')

    GO

    GO

    INSERT INTO [dbo].[test_table] ([id], [name], [price]) VALUES (N'143', N'梨', N'17')

    GO

    GO

    INSERT INTO [dbo].[test_table] ([id], [name], [price]) VALUES (N'144', N'菠萝', N'24')

    GO

    GO

    INSERT INTO [dbo].[test_table] ([id], [name], [price]) VALUES (N'145', N'西瓜', N'24')

    GO

    GO

    INSERT INTO [dbo].[test_table] ([id], [name], [price]) VALUES (N'146', N'油桃', N'16')

    GO

    GO

    INSERT INTO [dbo].[test_table] ([id], [name], [price]) VALUES (N'147', N'哈密瓜', N'9')

    GO

    GO

    INSERT INTO [dbo].[test_table] ([id], [name], [price]) VALUES (N'148', N'水蜜桃', N'33')

    GO

    GO

    INSERT INTO [dbo].[test_table] ([id], [name], [price]) VALUES (N'149', N'柚子', N'10')

    GO

    GO

    INSERT INTO [dbo].[test_table] ([id], [name], [price]) VALUES (N'150', N'橘子', N'8')

    GO

    GO

    SET IDENTITY_INSERT [dbo].[test_table] OFF

    GO


    4.2 排名 SQL

    SELECT name, price, ROW_NUMBER () OVER (ORDER BY price DESC) AS '排名1', --ROW_NUMBER直接分组,前面的序号唯一且连续 RANK () OVER (ORDER BY price DESC) AS '排名2', --RANK()并列排序,值相同序号并列,后面的值跳跃 DENSE_RANK () OVER (ORDER BY price DESC) AS '排名3', --DENSE_RANK并列排序,值相同序号并列,后面的值连续 NTILE (4) OVER (ORDER BY price DESC) AS '排名4' --不常用,NTILE(4)将所有的行分为4组,然后10/4=2余2,表示每组2行,前面的2行+1(3行) FROM test_table;



    4.3 预览效果

    执行上述 SQL 语句,可得到如下效果:



    附件列表


    主题: 报表应用
    标签: 暂无标签 编辑/添加标签
    如果您认为本文档还有待完善,请编辑

    文档内容仅供参考,如果你需要获取更多帮助,付费/准付费客户请咨询帆软技术支持
    关于技术问题,您还可以前往帆软社区,点击顶部搜索框旁边的提问按钮
    若您还有其他非技术类问题,可以联系帆软传说哥(qq:1745114201

    此页面有帮助吗?只是浏览 [ 去社区提问 ]