你的位置:首页 > 数据库

[数据库]自关联去掉组内重复数据


数据库环境:SQL SERVER 2005

  现有一个表的数据如下,id是主键,p1,p2是字符串类型,如果当前行的p1,p2字段的值分别等于其它行

的字段p2,p1的值,则视这2行记录为一组。比如,id=1和id=5就属于同一组数据。同一组数据只显示id最小

的那行记录,没有组的数据全部显示。

实现思路:

  将表进行自关联左联,假设表的别名是a,b,根据id进行关联,对关联后的结果集进行过滤。如果b.id是空的,则保留,

如果b.id不为空,则只保留a.id比b.id小的记录。

实现的SQL脚本:

/*1.数据准备*/WITH  x0     AS ( SELECT  1 AS id ,            'A' AS p1 ,            'B' AS p2        /*UNION ALL        SELECT  0 AS id ,            'A' AS p1 ,            'B' AS p2*/        UNION ALL        SELECT  2 AS id ,            'C' AS p1 ,            'D' AS p2        UNION ALL        SELECT  3 AS id ,            'E' AS p1 ,            'F' AS p2        UNION ALL        SELECT  4 AS id ,            'D' AS p1 ,            'C' AS p2        UNION ALL        SELECT  5 AS id ,            'B' AS p1 ,            'A' AS p2        UNION ALL        SELECT  6 AS id ,            'H' AS p1 ,            'J' AS p2        UNION ALL        SELECT  7 AS id ,            'T' AS p1 ,            'U' AS p2        UNION ALL        SELECT  8 AS id ,            'J' AS p1 ,            'H' AS p2        /*UNION ALL        SELECT  9 AS id ,            'I' AS p1 ,            'L' AS p2        UNION ALL        SELECT  10 AS id ,            'J' AS p1 ,            'K' AS p2*/       ),/*2.去重*/    x1     AS ( SELECT  id ,            p1 ,            p2        FROM   ( SELECT  id ,                  p1 ,                  p2 ,                  ROW_NUMBER() OVER ( PARTITION BY p1, p2 ORDER BY id ) AS rn             FROM   x0            ) t        WHERE  rn = 1       )  /*3.求值*/  SELECT a.id ,      a.p1 ,      a.p2  FROM  x1 a      LEFT JOIN x1 b ON b.p1 = a.p2               AND b.p2 = a.p1  WHERE  b.id IS NULL      OR a.id < b.id

View Code

最终实现的效果如图:

也有网友提出通过ASCII来实现,他的实现SQL脚本如下:

WITH  c1     AS ( SELECT  1 AS id ,            'A' AS p1 ,            'B' AS p2        /*UNION ALL        SELECT  0 AS id ,            'A' AS p1 ,            'B' AS p2*/        UNION ALL        SELECT  2 AS id ,            'C' AS p1 ,            'D' AS p2        UNION ALL        SELECT  3 AS id ,            'E' AS p1 ,            'F' AS p2        UNION ALL        SELECT  4 AS id ,            'D' AS p1 ,            'C' AS p2        UNION ALL        SELECT  5 AS id ,            'B' AS p1 ,            'A' AS p2        UNION ALL        SELECT  6 AS id ,            'H' AS p1 ,            'J' AS p2        UNION ALL        SELECT  7 AS id ,            'T' AS p1 ,            'U' AS p2        UNION ALL        SELECT  8 AS id ,            'J' AS p1 ,            'H' AS p2        /*UNION ALL        SELECT  9 AS id ,            'I' AS p1 ,            'L' AS p2        UNION ALL        SELECT  10 AS id ,            'J' AS p1 ,            'K' AS p2*/       ),    c2     AS ( SELECT  MIN(id) AS min_id        FROM   c1        GROUP BY ASCII(p1) + ASCII(p2)       )  SELECT c1.*  FROM  c1      JOIN c2 ON id = min_id

View Code

咋一看,似乎也可以实现同样的需求。实际上,这种写法存在2个问题:

  1.如果p1,p2是多个字符,ASCII的方式只会取第一个字符的ASCII

  2.ASCII('A')+ASCII('D')=ASCII('B')+ASCII('C'),对于这样的数据,用ASCII的方式无法区分

(本文完)