通过Sql实现的组内排序(排名)

编辑
文档创建者:zsh331 (73316 )     浏览次数:6605次     编辑次数:8次     最近更新:jiangsr 于 2018-06-21     

目录:

1. 环境编辑

注意事项:由于9.0设计器内置的是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)

2. 描述编辑

对于一些特殊的数据表(年报,月报),可能只需要对它某一个时间点(某一年或某一个月)的数据进行分析,此时在你的脑海里肯定会闪现一个词--【组内排序】。设置单元格父格,进行分组扩展就能实现。但我们知道,这种方法是浏览器(客户端)获取到数据,通过决策报表处理再展示的,当我们面对的是大数据时,考虑到效率问题,上面的方法是否是最佳选择?有没有更好的选择方案呢?答案是肯定的!

3. 思路编辑

我们可以直接在新建数据集时将其处理成带组内排序字段的结果集,然后将数据交付给浏览器直接展示。大家都知道,数据在服务器端处理肯定比在客户端处理快的多。

4. 示例编辑

4.1 准备数据

-- ---------------------------- -- Table structure for zsh_0220 -- ---------------------------- DROP TABLE [dbo].[zsh_0220] GO CREATE TABLE [dbo].[zsh_0220] ( [t_time] int NULL , [code] int NULL , [name] varchar(255) NULL , [cl] int NULL ) GO -- ---------------------------- -- Records of zsh_0220 -- ---------------------------- INSERT INTO [dbo].[zsh_0220] ([t_time], [code], [name], [cl]) VALUES (N'2015', N'110000', N'北京市', N'7631') GO GO INSERT INTO [dbo].[zsh_0220] ([t_time], [code], [name], [cl]) VALUES (N'2015', N'120000', N'天津市', N'3861') GO GO INSERT INTO [dbo].[zsh_0220] ([t_time], [code], [name], [cl]) VALUES (N'2015', N'130000', N'河北省', N'7631') GO GO INSERT INTO [dbo].[zsh_0220] ([t_time], [code], [name], [cl]) VALUES (N'2015', N'140000', N'山西省', N'6651') GO GO INSERT INTO [dbo].[zsh_0220] ([t_time], [code], [name], [cl]) VALUES (N'2015', N'150000', N'内蒙古自治区', N'4030') GO GO INSERT INTO [dbo].[zsh_0220] ([t_time], [code], [name], [cl]) VALUES (N'2015', N'210000', N'辽宁省', N'4448') GO GO INSERT INTO [dbo].[zsh_0220] ([t_time], [code], [name], [cl]) VALUES (N'2016', N'110000', N'北京市', N'8122') GO GO INSERT INTO [dbo].[zsh_0220] ([t_time], [code], [name], [cl]) VALUES (N'2016', N'120000', N'天津市', N'3524') GO GO INSERT INTO [dbo].[zsh_0220] ([t_time], [code], [name], [cl]) VALUES (N'2016', N'130000', N'河北省', N'9006') GO GO INSERT INTO [dbo].[zsh_0220] ([t_time], [code], [name], [cl]) VALUES (N'2016', N'140000', N'山西省', N'6288') GO GO INSERT INTO [dbo].[zsh_0220] ([t_time], [code], [name], [cl]) VALUES (N'2016', N'150000', N'内蒙古自治区', N'7787') GO GO INSERT INTO [dbo].[zsh_0220] ([t_time], [code], [name], [cl]) VALUES (N'2016', N'210000', N'辽宁省', N'6288') GO GO 

222

4.2 执行(组内)排名sql

SELECT t_time, code, name, CL, row_number () OVER (partition BY t_time ORDER BY cl) AS 组内排名1, --T_time组内,cl排名 row_number () OVER (ORDER BY cl) AS 排名1_1, --所有cl的排名 rank () OVER (partition BY t_time ORDER BY cl) AS 组内排名2, --T_time组内,cl排名 rank () OVER (ORDER BY cl) AS 排名2_1, --所有cl的排名 dense_rank () OVER (partition BY t_time ORDER BY cl) AS 组内排名3, --T_time组内,cl排名 dense_rank () OVER (ORDER BY cl) AS 排名3_1 --所有cl的排名 FROM zsh_0220 ORDER BY t_time,code;

关键点分析:

OVER (partition BY t_time ORDER BY cl)

解释:t_time分组(示例将t_time分为2005,2006二个组), cl排序(默认:升序)。降序可设置ORDER BY cl desc

函数ROW_NUMBER() OVER ()  ,RANK() OVER ()RANK(),DENSE_RANK() OVER ()的区别在这就不详细介绍了,可参考:开窗函数-排名

4.3 预览结果:

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

222

条件的分析函数有:

row_number() over(partition by … order by …) 

rank() over(partition by … order by …) 

dense_rank() over(partition by … order by …) 

count() over(partition by … order by …) 

max() over(partition by … order by …) 

min() over(partition by … order by …) 

sum() over(partition by … order by …) 

avg() over(partition by … order by …) 

first_value() over(partition by … order by …) 

last_value() over(partition by … order by …) 

lag() over(partition by … order by …) 

lead() over(partition by … order by …) 

等等…有兴趣的朋友可以试试,这里就不详解了!

附件列表


主题: 专题总结
如果您认为本文档还有待完善,请编辑

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

此页面有帮助吗?