数据库:ROWNUMBE奇骏() OVE帕杰罗( PARTITION BY COL1

作者:数据库

新近在MySQL中相见分组排序查询时,猝然意识MySQL中绝非row_number() over(partition by colname)那样的分组排序。
与此同不平日间由于MySQL中一贯不相符于SQL Server中的row_number()、rank()、dense_rank()等排行函数,全部找到以下达成形式,在这简单记录一下。

本文为原创,如需转发,请注脚小编和出处,多谢!
上一篇:SQL Server2007随笔(2卡塔 尔(英语:State of Qatar):公用表表达式(CTE卡塔尔的递归调用

ROWNUMBER() OVER( PARTITION BY COL1 ORDER BY COL2)用法

前天在行使多字段去重时,由于有个别字段有七种或者性,只需依照一些字段实行去重,在网络来看了rownumber() over(partition by col1 order by col2)去重的不二等秘书诀,很正确,在这里记录分享下:
  row_number() OVE中华V ( PARTITION BY COL1 ORAV4DE中华V BY COL2) 表示依照COL1分组,在分组内部依照COL2排序,而此函数总括的值就表示每组内部排序后的依次编号(组内延续的天下第一的).
  与rownum的分化在于:使用rownum举行排序的时候是先对结果集参预伪列rownum然后再拓宽排序,而此函数在包罗排序从句后是先排序再计算行号码.

row_number()rownum大致,作用更加强一点(能够在逐条分组内从1开时排序卡塔尔国.
rank()是跳跃排序,有多个第二名时接下去正是第四名(肖似是在依次分组内卡塔尔.
dense_rank()l是三回九转排序,有五个第二名时依旧跟着第三名。比较之下row_number是未有重复值的.
lag(arg1,arg2,arg3):
  arg1是从其余行再次回到的表明式
  arg2是愿意物色的一时一刻行分区的偏移量。是三个正的偏移量,是二个往回检索早先的行的数量。
  arg3是在arg2象征的数额超过了分组的节制时重回的值。

函数语法:
OPAP函数语法四有个别:
1.function 自己用于对窗口中的数据开展操作;
2.partitioning clause 用于将结果集分区;
3.order by clause 用于对分区中的数据进行排序;
4.windowing clause 用于定义function在其上操作的行的会集,即function所影响的范围;

RANK()
dense_rank()
【语法】RANK ( ) OVER ( [query_partition_clause] order_by_clause )
dense_RANK ( ) OVER ( [query_partition_clause] order_by_clause )

【效能】聚合函数RANK 和 dense_rank 首要的法力是估测计算生龙活虎组数值中的排序值。
【参数】dense_rank与rank()用法格外,
【区别】dence_rank在并列关系是,相关等级不会跳过。rank则跳过
rank()是跳跃排序,有八个第二名时接下去就是第四名(相通是在依次分组内卡塔尔国
dense_rank()l是连连排序,有八个第二名时依然跟着第三名。
【表达】Oracle解析函数

ROW_NUMBER()
【语法】ROW_NUMBER() OVER (PARTITION BY COL1 ORDER BY COL2)
【效率】表示依据COL1分组,在分组内部根据COL2排序,而以此值就代表每组内部排序后的各类编号(组内三翻五次的独占鳌头的卡塔 尔(阿拉伯语:قطر‎
row_number() 重返的重大是“行”的音讯,并从未排行
【参数】
【表达】Oracle解析函数

根本作用:用于取前几名,或然最终几名等
sum(...) over ...
【作用】三番五次求和剖析函数
【参数】具体参示例
【表明】Oracle分析函数

lag()lead()
【语法】
lag(EXPR,<OFFSET>,<DEFAULT>) OVER ( [query_partition_clause] order_by_clause )
LEAD(EXPR,<OFFSET>,<DEFAULT>) OVER ( [query_partition_clause] order_by_clause )
【功用】表示根据COL1分组,在分组内部根据COL2排序,而以此值就意味着每组内部排序后的逐个编号(组内延续的独步天下的卡塔尔国
lead (卡塔尔 下多个值 lag(卡塔尔 上一个值

【参数】
EXP凯雷德是从其余行重回的表明式
OFFSET是缺省为1 的正数,表示绝对行数。希望物色的当前进分区的偏移量
DEFAULT是在OFFSET表示的多寡超过了分组的限量时重临的值。
【表明】Oracle分析函数

---TEST FOR ROW_NUMBER() OVER(PARTITION BY COL1 ORDER BY COL2)
DROP TABLE TEST_Y
CREATE TABLE TEST_Y(
       ID VARCHAR2 (32) PRIMARY KEY ,
       NAME VARCHAR2 (20),
       AGE NUMBER(3 ),
       DETAILS VARCHAR2 (1000)
);
INSERT INTO TEST_Y VALUES(SYS_GUID(), '海子',20 ,'面朝大海,春暖花开');
INSERT INTO TEST_Y VALUES(SYS_GUID(), '海子',30 ,'面朝大海,春暖花开');
INSERT INTO TEST_Y VALUES(SYS_GUID(), '贝多芬',43 ,'致爱丽丝');
INSERT INTO TEST_Y VALUES(SYS_GUID(), '普希金',34 ,'假如生活欺骗了你');
INSERT INTO TEST_Y VALUES(SYS_GUID(), '杨过',23 ,'黯然销魂掌');
INSERT INTO TEST_Y VALUES(SYS_GUID(), '小龙女',32 ,'神雕侠侣');
INSERT INTO TEST_Y VALUES(SYS_GUID(), '李清照',21 ,'寻寻觅觅、冷冷清清');
INSERT INTO TEST_Y VALUES(SYS_GUID(), '周芷若',18 ,'峨眉');
INSERT INTO TEST_Y VALUES(SYS_GUID(), '赵敏',18 ,'自由');
INSERT INTO TEST_Y VALUES(SYS_GUID(), '张无忌',20 ,'倚天屠龙记');
INSERT INTO TEST_Y VALUES(SYS_GUID(), '张无忌',30 ,'倚天屠龙记');

SELECT * FROM TEST_Y;


----1. ROW_NUMBER() OVER(PARTITION BY COL1 ORDER BY COL2)
---查询所有姓名,如果同名,则按年龄降序
SELECT NAME ,AGE,DETAILS,ROW_NUMBER() OVER(PARTITION BY NAME ORDER BY AGE DESC) FROM TEST_Y;

数据库 1

----通过上面的语句可知,ROW_NUMBER() OVER(PARTITION BY COL1 ORDER BY COL2)中是按照NAME字段分组,按AGE字段排序的。
----如果只需查询出不重复的姓名即可,则可使用如下的语句
SELECT * FROM (SELECT NAME,AGE,DETAILS ,ROW_NUMBER() OVER( PARTITION BY NAME ORDER BY AGE DESC)RN FROM TEST_Y )WHERE RN= 1;

数据库 2

----由查询结果可知,姓名相同年龄小的数据被过滤掉了;可以使用ROW_NUMBER() OVER(PARTITION BY COL1 ORDER BY COL2)对部分子弹进行去重处理


----2.RANK() OVER(PARTITION BY COL1 ORDER BY COL2)
----跳跃排序
SELECT NAME ,AGE,DETAILS , RANK() OVER (PARTITION BY NAME ORDER BY AGE DESC) FROM TEST_Y;

数据库 3

----由查询结果可知,相同的并列,下一个则跳跃到并列所替的序列后:如有两个并列1,那么下一个则直接排为3,跳过2;

----3.DENSE_RANK() OVER(PARTITION BY COL1 ORDER BY COL2)
----连续排序,当有多个并列时,下一个仍然连续有序

数据库 4

----由查询结果可知,当两个并列为1时,下一个仍连续有序为2,不跳跃到3

 Lag和Lead函数可以在一次查询中取出同一字段的前N行的数据和后N行的值。这种操作可以使用对相同表的表连接来实现,不过使用LAG和LEAD有更高的效率.
  Lag和Lead偏移量函数,其用途是:可以查出同一字段下一个值或上一个值,并作为新列存在表中.
-----4.LAG(exp_str,offset,defval) OVER(PARTITION BY NAME ORDER BY AGE)
-----exp_str 返回显示的字段;offset是exp_str字段的偏移量,默认是1,如offset=1表示返回当前exp_str的下一个exp_str;defval当该函数无值可用的情况下返回该值。
(1) SELECT NAME ,AGE,DETAILS, LAG(NAME ,1, 'sue') OVER (PARTITION BY NAME ORDER BY AGE DESC ) FROM TEST_

数据库 5

(2) SELECT NAME ,AGE,DETAILS, LAG(NAME ,2, 'sue') OVER (PARTITION BY NAME ORDER BY AGE DESC ) FROM TEST_Y

数据库 6

(3) SELECT NAME ,AGE,DETAILS, LAG(NAME ,2, 'sue') OVER (ORDER BY AGE DESC ) FROM TEST_Y;

数据库 7

----5.LEAD(EXP_STR,OFFSET,DEFVAL) OVER(PARTITION BY NAME ORDER BY AGE)
-----exp_str 返回显示的字段;offset是exp_str字段的偏移量,默认是1,如offset=1表示返回当前exp_str的上一个exp_str;
-----defval当该函数无值可用的情况下返回该值。
(1)SELECT NAME ,AGE,DETAILS, LEAD(NAME ,1, 'sue') OVER (PARTITION BY NAME ORDER BY AGE DESC ) FROM 

数据库 8

(2) SELECT NAME ,AGE,DETAILS, LEAD(NAME ,2, 'sue') OVER (PARTITION BY NAME ORDER BY AGE DESC ) FROM TEST_Y

数据库 9

(3) SELECT NAME ,AGE,DETAILS, LEAD(NAME ,1, 'sue') OVER (ORDER BY AGE DESC ) FROM TEST_Y;

数据库 10

-----6.SUM(COL1) OVER([PARTITION BY COL2 ] [ORDER BY COL3])
(1) SELECT NAME ,AGE,DETAILS,ROW_NUMBER() OVER(PARTITION BY NAME ORDER BY AGE DESC),SUM (AGE) OVER( PART

数据库 11

(2) SELECT NAME ,AGE,DETAILS,ROW_NUMBER() OVER(PARTITION BY NAME ORDER BY AGE DESC),SUM (AGE) OVER( PAR

数据库 12

(3)SELECT NAME ,AGE,DETAILS,ROW_NUMBER() OVER(PARTITION BY NAME ORDER BY AGE DESC),SUM (AGE) OVER( ORDE

数据库 13

(4) SELECT NAME ,AGE,DETAILS, SUM(AGE) OVER ()  FROM TEST_Y;

数据库 14

 

如上内容摘要自:

 

over()深入分析函数用于总结基于组的某种聚合值,它和聚合函数的差异之处是:对于每一个组重回多行,而聚合函数对于各种组只重回黄金年代行。
例子:

 

    排名函数是SQL Server二零零七新加的遵循。在SQL Server2007中好似下五个排行函数:

sum(x) over( partition by y ORDER BY z ) 分析

 

后边用过row_number(),rank()等排序与over( partition by ... O帕杰罗DEEvoque BY ...),那多少个比较好领悟: 先分组,然后在组内排行。

前几天黑马遇上sum(...) over( partition by ... O奥德赛DE奥德赛 BY ... ),居然搞不拔除怎么实行的,所以查了些资料,做了下实际操作。

  1. 从最简易的发端

  sum(...) over( ),对富有行求和

  sum(...) over( order by ... ),和 = 第少年老成行 到 与当前进同序号行的末段大器晚成行的持有值求和,文字不太好理解,请看下图的算法剖析。

with aa as
( 
SELECT 1 a,1 b, 3 c FROM dual union
SELECT 2 a,2 b, 3 c FROM dual union
SELECT 3 a,3 b, 3 c FROM dual union
SELECT 4 a,4 b, 3 c FROM dual union
SELECT 5 a,5 b, 3 c FROM dual union
SELECT 6 a,5 b, 3 c FROM dual union
SELECT 7 a,2 b, 3 c FROM dual union
SELECT 8 a,2 b, 8 c FROM dual union
SELECT 9 a,3 b, 3 c FROM dual
)
SELECT a,b,c,
sum(c) over(order by b) sum1,--有排序,求和当前行所在顺序号的C列所有值
sum(c) over() sum2--无排序,求和 C列所有值

sum() over()

数据库 15

  1. 与 partition by 结合

  sum(...) over( partition by... ),同组内所行求和

  sum(...) over( partition by... order by ... ),同第1点中的排序求和原理,只是范围限制在组内

with aa as
( 
SELECT 1 a,1 b, 3 c FROM dual union
SELECT 2 a,2 b, 3 c FROM dual union
SELECT 3 a,3 b, 3 c FROM dual union
SELECT 4 a,4 b, 3 c FROM dual union
SELECT 5 a,5 b, 3 c FROM dual union
SELECT 6 a,5 b, 3 c FROM dual union
SELECT 7 a,2 b, 3 c FROM dual union
SELECT 7 a,2 b, 8 c FROM dual union
SELECT 9 a,3 b, 3 c FROM dual
)
SELECT a,b,c,sum(c) over( partition by b ) partition_sum,
sum(c) over( partition by b order by a desc) partition_order_sum
  FROM aa;

view sql

数据库 16

 

以上内容摘要自:

 

案例:

有世界表CMSocial,圈子成员表CMSocialMember,圈子核实表CMSocialCheck,在那之中世界核实被反驳回绝的话,改进消息后能够再一次提交考察,也正是说圈子能够变动多条世界检查核对音讯。

大器晚成经要查询某客户的一切领域,同期获得其中每条世界对应的那二日一条审查处境?(假如某顾客MemberID=1 卡塔尔国

SQL语句能够如此写:

SELECT 
S.CMSocialID,
S.SocialName,
S.SocialDescription,
S.SocialLogo,
S.SocialAuthority,
S.Integral,
S.SocialState,
S.IsAvailable,
SC.CheckState,
SC.Notes,
SM.CMSocialMemberID,
SM.MemberID,
SM.MemberName,
SM.MemberIntegral,
SM.EnterTime,
SM.MemberState,
SM.MemberRank,
SM.IsRecommend
FROM (SELECT * FROM CMSocialMember WHERE MemberID=1 AND IsDelete<>1 AND IsAvailable=1) AS SM 
LEFT JOIN CMSocial AS S ON S.CMSocialID=SM.CMSocialID 
LEFT JOIN (
SELECT *
FROM ( 
    SELECT ROW_NUMBER() OVER (PARTITION BY CMSocialID ORDER BY CreateTime DESC) AS group_index ,*  /* 根据 CMSocialID 分组,CreateTime倒序,生成分组内部序号 */
    FROM CMSocialCheck WHERE IsDelete<>1
    ) AS SCsub WHERE SCsub.group_index=1 /*取每个分组内部序号=1 的信息*/
) AS SC ON SC.CMSocialID=S.CMSocialID

 

注意:

SELECT ROW_NUMBER() OVER (PARTITION BY CMSocialID ORDER BY CreateTime DESC) AS group_index ,*      /*数据库, 依据 CMSocialID 分组,CreateTime倒序,生成分组内部序号 */
FROM CMSocialCheck WHERE IsDelete<>1
) AS SCsub WHERE SCsub.group_index=1 /*取各类分组内部序号=1 的音讯*/

 

sql根据某一个字段重复只取第一条数据
动用深入分析函数row_number() over (partiion by ... order by ...)来进展分组编号,然后取分组标号值为1的记录就可以。近日主流的数据库都有支撑剖析函数,很好用。
在那之中,partition by 是点名按怎么样字段实行分组,这个字段值相符的笔录将要联合具名编号;order by则是点名在平等组中开展编号时是坚决守住什么样的相继。
演示(SQL Server 2006或以上适用):

select s.*  
from ( 
    select *, row_number() over (partition by [手机号] order by [店铺]) as group_idx  
    from table_name
) s
where s.group_idx = 1

 

主表1条数据,对应子表,附表多条数据,取唯风流倜傥:

DECLARE @Status INT;
SET @Status=1;
SELECT  p.CFProjectID AS ID,pc.Status AS StatusID,* FROM dbo.CFProject p
LEFT JOIN (
    select s.*  
    from ( 
        select *, row_number() over (partition by [SourceProjectID] order by [CFProjectID]) as group_idx  
        from dbo.CFProjectToCrowdSpace WHERE [Status]=@Status OR @Status IS NULL
    ) s
    where s.group_idx = 1
) pc ON pc.SourceProjectID=p.CFProjectId
WHERE p.CreatorID=100273  AND p.Status=3  AND p.OrganizationID=180  ORDER  BY p.CreationTime DESC

 

select *from (select name,class,score,rank() over(partition by class order by score desc) mm from t2 ) where mm=1

首先创造多少个表并插入测验数据。

1. row_number

通过class班级进行分组,并依赖score分数实行排序,用rank(卡塔尔函数排序方法为mm列授予序号,然后mm=1就能够找到每组的首先名,当然能够借助score就能够倒序能够找到最后一名。

create table demo.Student (
   ID int(11) NOT NULL AUTO_INCREMENT,
   StuNo varchar(32) NOT NULL,
   StuName varchar(10) NOT NULL,
   StuAge int(11) DEFAULT NULL,
   PRIMARY KEY (ID)
 )
 engine=innodb auto_increment=1 default charset=utf8 collate=utf8_general_ci;

insert into demo.Student(StuNo,StuName,StuAge) values('A001','小明',22);
insert into demo.Student(StuNo,StuName,StuAge) values('A005','小李',23);
insert into demo.Student(StuNo,StuName,StuAge) values('A007','小红',24);
insert into demo.Student(StuNo,StuName,StuAge) values('A003','小明',22);
insert into demo.Student(StuNo,StuName,StuAge) values('A002','小李',23);
insert into demo.Student(StuNo,StuName,StuAge) values('A004','小红',24);
insert into demo.Student(StuNo,StuName,StuAge) values('A006','小王',25);

select * from demo.Student;

2. rank

row_number() over(partition by ... order by ...)

测量试验数据如下:

3. dense_rank

粗略的说row_number()从1发端,为每一条分组记录重临多少个数字, row_number() over(order by score desc)是先把score 列降序,再为降序现在的没条xlh记录再次来到一个序号。(若无分组能够理解成将全体结果作为贰个分组卡塔 尔(阿拉伯语:قطر‎

数据库 17

4. ntile   
    下边分别介绍一下那多个排行函数的功力及用法。在介绍以前纵然有贰个t_table表,表结构与表中的数码如图1所示:

row_number() over(partition by class order by score desc)表示依照class分组,在分组内部遵照 score 排序,而此函数总括的值就象征每组内部排序后的次第编号(组内一连的唯生机勃勃的)

 

数据库 18

rank() over(partition by ... order by ...)
dense_rank() over(partition by ... order by ...)

实现row_number()排行函数,按学号(StuNo)排序。

图1

用作分数函数中关于排序的rank(),dense_rank(),row_number()。

-- @row_number:=0,设置变量@row_number的初始值为0。
-- @row_number:=@row_number 1,累加@row_number的值。
select ID,StuNo,StuName,StuAge,@row_number:=@row_number 1 as row_number 
from demo.Student a,
(
    select @row_number:=0
) b
order by StuNo asc;

里头田野1字段的花色是int,田野同志2字段的花色是varchar

rank() over是的效率是摸清钦点条件后开展贰个排名,然则有一个特性。纵然是对学子排行,那么实用那些函数,成绩同样的两名是相提并论(排名为1,2,2,4卡塔尔国

结果如下:

一、row_number

dense_rank()的效应和rank()很像,唯大器晚成分化正是,形似成绩并列未来,下壹人同学并不空出并列所占的排行(排行为1,2,2,3卡塔尔

数据库 19

    row_number函数的用途是非常布满,这么些函数的功用是为查询出来的每大器晚成行记录生成一个序号。row_number函数的用法如下边包车型客车SQL语句所示:

row_number()就不朝气蓬勃致了,它和方面二种的区分就很显眼了,那些函数无需思忖是不是并列,哪怕依据标准查询出来的数值相符也会开展连接排行。

 

 

对于多表查询,可感到空置加上二个论断来显示查询数据为空的数码。

完毕rank()排行函数,按学子年龄(StuAge)排序。

select row_number() over(order by field1) as row_number,* from t_table

case when score is null then 1 else rank() over (partition by class order by score desc ) end as mm

-- @StuAge:=null,设置变量@StuAge的初始值为null
-- @rank:=0,设置变量@rank的初始值为0
-- @inRank:=1,设置变量@inRank的初始值为1
-- if(@StuAge=StuAge,@rank,@rank:=@inRank),指定排序列的值不变时,@rank的值不变;指定排序列的值变化时,@rank的值跳变为@inRank内部计数的值
-- @inRank:=@inRank 1,每一行自增1,用于实现内部计数
select t.ID,t.StuNo,t.StuName,t.StuAge,t.row_rank 
from 
(
    select ID,StuNo,StuName,StuAge,if(@StuAge=StuAge,@rank,@rank:=@inRank) as row_rank,@inRank:=@inRank 1,@StuAge:=StuAge 
    from demo.Student a,
    (
        select @StuAge:=null,@rank:=0,@inRank:=1 
    ) b 
    order by StuAge asc 
) t;

    上边的SQL语句的查询结果如图2所示。

其余常用的剖析函数:

结果如下:

数据库 20

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 ...)

数据库 21

图2

 

    其中row_number列是由row_number函数生成的序号列。在应用row_number函数是要运用over子句采纳对某一列举办排序,然后技术生成序号。

实现dense_rank()排行函数,按学子年龄(StuAge)排序。

    实际上,row_number函数生成序号的基本原理是先使用over子句中的排序语句对记录实行排序,然后按着这几个顺序生成序号。over子句中的order by子句与SQL语句中的order by子句未有别的涉及,这两处的order by 能够完全差异,如下边包车型客车SQL语句所示:

本文由ca88发布,转载请注明来源

关键词: ca88网址 DataBase Sql Server 原创 排名函数