By

一个查询语句的延伸功能

注意本文中的所有SQL执行环境为SQL SERVER 2008 R2 SP2,如果在您的环境下执行有问题或是语法错误,请查看文档,作相应的修改.
row_numer为SQL 2005才出现的功能,因此呢,至少在sql 2005以上的才能执行下面的语句

今天在一个群里讨论一个小问题如下,(一个例子片断)

n1   n2   n3   n4
1    a    x    5
1    a    x    5
1    b    y    1
2    b    y    4
2    c    y    2
2    d    z    5
3    e    n    2

现在需要取出一个结果集,要求每个n1的值只出现一次,如下

n1   n2   n3   n4
1    b    y    1
2    d    z    5
3    e    n    2

我首先想到的是distinct,但发现如果只是distinct单个字段,记录就无法出来,因此不符合,如果distinct所有字段,这里没有完全重复的记录,因此有distinct与没有distinct的结果都是一样的.

select distinct * from test
select * from test

有人给出了一个解决方案如下

select *
from (select t.n1,
           t.n2,
           t.n3,
           t.n4,
           row_number() OVER(PARTITION BY t.n1 ORDER BY t.n2 desc) rn
      from test t) t
where t.rn = 1

这个解决方案OK的. 下面分析下这个解决方案,前面的几个简单的字段,相信大家都能看懂,就不说了,主要看row_number() OVER(PARTITION BY t.n1 ORDER BY t.n2 desc) rn 这里PARTITION BY t.n1是将结果集,按n1分为多个分区,意思就是对于n1中出现的每个相同值,会被划分到不同的分区中,分区结果如下

n1   n2   n3   n4   rn
1    b    y    1    1
1    a    x    5    2
1    a    x    5    3
1    a    y    5    4
2    d    z    5    1
2    c    y    2    2
2    b    y    4    3
3    e    n    2    1

Transact SQL对OVER(PARTION BY…)的说明是 将结果集分为多个分区,开窗函数分别应用于每个分区,并为每个分区重新启动计算

下面是本人对此种方案的一个扩展. 有一个非常经典的问题,就是筛选数据库中的重复记录 下面我就上面的方法,说说我想到的方法,先贴代码

select t.n1,
       t.n2,
       t.n3,
       t.n4,
       row_number() OVER(PARTITION BY t.n1,t.n2,t.n3,t.n4 ORDER BY t.n2 desc) rn
from test t

很简单的方法,上面语句的意义就是对全字段完全一样的记录,会被划分到不同的分区,这样一来,就可以通过取分区数来筛选重复记录了.执行结果如下

n1   n2   n3   n4   rn
1    a    x    5    1
1    a    x    5    2
1    a    y    4    1
1    a    y    5    1
1    b    y    1    1
2    b    y    4    1
2    c    y    2    1
2    d    z    5    1
3    e    n    2    1

这样一来,筛选重复记录,只需要根据rn列来筛选就OK了.呵呵,很简单的实现.

我将整个测试过程的代码贴一下

DECLARE @Temp TABLE
(
    n1 int,
    n2 varchar(10),
    n3 varchar(10),
    n4 int
)

insert into @Temp
values(1,'a','x',5),
(1,'a','x',5),
(1,'a','y',5),
(1,'a','y',4),
(1,'b','y',1),
(2,'b','y',4),
(2,'c','y',2),
(2,'d','z',5),
(3,'e','n',2)

SELECT * FROM @Temp

select t.n1,
       t.n2,
       t.n3,
       t.n4,
       row_number() OVER(PARTITION BY t.n1,t.n2,t.n3,t.n4 ORDER BY t.n2 desc) rn
from @Temp t

select *
from (select t.n1,
           t.n2,
           t.n3,
           t.n4,
           row_number() OVER(PARTITION BY t.n1 ORDER BY t.n2 desc) rn
      from @Temp t) t
where t.rn = 1

注意本文中的所有SQL执行环境为SQL SERVER 2008 R2 SP2,如果在您的环境下执行有问题或是语法错误,请查看文档,作相应的修改.row_numer为SQL 2005才出现的功能,因此呢,至少在sql 2005以上的才能执行下面的语句