如何抑制或隐藏SQL中的重复值? [英] How do you suppress or hide duplicate values in SQL?

问题描述

我一直在寻找一些东西，但是还没有找到如何执行此操作的方法(尽管已经发现了很多有关性能分析的方法！！)

我想执行一次选择，该选择返回几列数据，然后执行另一个表的嵌套行(相关联的嵌套表)(实际上是同一张表本身相连，但我认为这是无关紧要的).

所以数据是这样的:

id  name   registered
1   Dan      N
2   Bill     N
3   Bob      N
4   Dan      N
5   Bill     Y
6   Dan      Y

这个想法是执行选择，以查找可能与注册帐户相关的所有未注册人员.

所以结果看起来像

registered.id   name   notreg.id   name
  5             Bill     2          Bill
  6             Dan      1          Dan
                         4          Dan

我可以使用SQL来处理所有选择条件等，并且有一个查询返回一个普通的内部联接来查找此条件，但想知道是否有可能获得像这样的结果集，因此没有重复的值在左侧?

解决方案

您最好在客户端中抑制它的重复(例如，在Jasper Reports中取消选中Print Repeated Value或在XML集isPrintRepeatedValues="false"中)

但是在支持WITH和ROW_NUMBER()的任何产品上(例如Oracle，SQL Server 2005 +).

WITH ns 
         AS (SELECT Row_number() OVER (PARTITION BY name ORDER BY id) rn, 
                    id, 
                    name, 
                    registered 
             FROM   t 
             WHERE  registered = 'N') 
    SELECT t.id, 
           t.name, 
           ns.id, 
           ns.name 
    FROM   ns 
           LEFT JOIN t 
             ON ns.name = t.name 
                AND t.registered = 'Y' 
                AND ns.rn = 1 
    WHERE  ns.name IN (SELECT name 
                       FROM   t 
                       WHERE  registered = 'Y')  

    ORDER  BY ns.name 

查看工作示例

如果您没有WITH和ROW_NUIMBER，则可以这样做

SELECT t.id, 
       t.name, 
       ns.id, 
       ns.name 
FROM   t ns 
       LEFT JOIN (SELECT MIN(id) id, 
                         name 
                  FROM   t 
                  WHERE  registered = 'N' 
                  GROUP  BY name) MINNS 
         ON ns.id = MINNS.id 
       LEFT JOIN t 
         ON ns.name = t.name 
            AND t.registered = 'Y' 
            AND MINNS.id IS NOT NULL 
WHERE  ns.registered = 'N' 
       AND ns.name IN (SELECT name 
                       FROM   t 
                       WHERE  registered = 'Y') 
ORDER  BY ns.name, 
          ns.id

参见工作示例

I've been looking around for a bit and have not been able to find out how to do this yet (though have found alot about analyzing it's performance?!)

I want to perform a select which returns a few columns of data, and then also a nested table of related rows from another table (actually the same table joined on itself but I 'think' this is irelevant).

So the data is something like this:

id  name   registered
1   Dan      N
2   Bill     N
3   Bob      N
4   Dan      N
5   Bill     Y
6   Dan      Y

The idea is perform a select which finds all the unregistered people who could be related to a registered account.

So the result would look like

registered.id   name   notreg.id   name
  5             Bill     2          Bill
  6             Dan      1          Dan
                         4          Dan

I alright with SQL can handle all the selection criteria etc, and have a query that returns a normal inner join that finds this, but want to know if it's possible to get a result set kind of like this, so no repeated values on the left side??

解决方案

You're probably better off suppressing duplicates it in the Client (for example in Jasper Reports uncheck Print Repeated Value or in the XML set isPrintRepeatedValues="false")

However on anything that supports WITH and ROW_NUMBER() (e.g. Oracle, SQL Server 2005+) .

WITH ns 
         AS (SELECT Row_number() OVER (PARTITION BY name ORDER BY id) rn, 
                    id, 
                    name, 
                    registered 
             FROM   t 
             WHERE  registered = 'N') 
    SELECT t.id, 
           t.name, 
           ns.id, 
           ns.name 
    FROM   ns 
           LEFT JOIN t 
             ON ns.name = t.name 
                AND t.registered = 'Y' 
                AND ns.rn = 1 
    WHERE  ns.name IN (SELECT name 
                       FROM   t 
                       WHERE  registered = 'Y')  

    ORDER  BY ns.name 

See working example

If you don't have WITH and ROW_NUIMBER you can do this

SELECT t.id, 
       t.name, 
       ns.id, 
       ns.name 
FROM   t ns 
       LEFT JOIN (SELECT MIN(id) id, 
                         name 
                  FROM   t 
                  WHERE  registered = 'N' 
                  GROUP  BY name) MINNS 
         ON ns.id = MINNS.id 
       LEFT JOIN t 
         ON ns.name = t.name 
            AND t.registered = 'Y' 
            AND MINNS.id IS NOT NULL 
WHERE  ns.registered = 'N' 
       AND ns.name IN (SELECT name 
                       FROM   t 
                       WHERE  registered = 'Y') 
ORDER  BY ns.name, 
          ns.id

See working example

这篇关于如何抑制或隐藏SQL中的重复值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！