时间戳

首页 > 解决方案 > 返回表中重复值的所有列名

sql - 返回表中重复值的所有列名

问题描述

我想知道是否可以为 oracle sql 返回具有相同 ID 值的重复列的所有值

我的桌子设计会在下面

表 A

Name    ID      Order       Year
------  ------  -------     ------
JOHN    1       ORD123      2017
JAKE    2       ORD122      2018
JES     2       ORD111      2017
JOHN    3       ORD323      2012
NICK    4       ORD133      2011
AMY     4       ORD222      2010
MUS     4       ORD132      2010

我希望查询的结果如下

Name    ID      Order       Year
------  ------  -------     ------
JAKE    2       ORD122      2018
JES     2       ORD111      2017
NICK    4       ORD133      2011
AMY     4       ORD222      2010
MUS     4       ORD132      2010

标签： sqloracle11g

解决方案

您可以使用分析函数在单个表扫描中执行此操作：

SQL小提琴

Oracle 11g R2 模式设置：

CREATE TABLE TableA ( Name, ID, "Order", Year ) AS
  SELECT 'JOHN', 1, 'ORD123', 2017 FROM DUAL UNION ALL
  SELECT 'JAKE', 2, 'ORD122', 2018 FROM DUAL UNION ALL
  SELECT 'JES',  2, 'ORD111', 2017 FROM DUAL UNION ALL
  SELECT 'JOHN', 3, 'ORD323', 2012 FROM DUAL UNION ALL
  SELECT 'NICK', 4, 'ORD133', 2011 FROM DUAL UNION ALL
  SELECT 'AMY',  4, 'ORD222', 2010 FROM DUAL UNION ALL
  SELECT 'MUS',  4, 'ORD132', 2010 FROM DUAL;

查询 1：

SELECT Name, ID, "Order", Year
FROM   (
  SELECT t.*,
         COUNT(*) OVER ( PARTITION BY id ) AS num_duplicates
  FROM   tableA t
)
WHERE  num_duplicates > 1

结果：

| NAME | ID |  Order | YEAR |
|------|----|--------|------|
| JAKE |  2 | ORD122 | 2018 |
|  JES |  2 | ORD111 | 2017 |
| NICK |  4 | ORD133 | 2011 |
|  AMY |  4 | ORD222 | 2010 |
|  MUS |  4 | ORD132 | 2010 |

IN ( SELECT ... GROUP BY id HAVING COUNT(*) > 1 )在自连接中使用或聚合将需要两次表/索引扫描。

更新

如果我有两个条件，是否也可以，也许是 id 和 year？

查询 2：只需将附加列添加到PARTITION BY子句：

SELECT Name, ID, "Order", Year
FROM   (
  SELECT t.*,
         COUNT(*) OVER ( PARTITION BY id, year ) AS num_duplicates
  FROM   tableA t
)
WHERE  num_duplicates > 1

结果：

| NAME | ID |  Order | YEAR |
|------|----|--------|------|
|  AMY |  4 | ORD222 | 2010 |
|  MUS |  4 | ORD132 | 2010 |

推荐阅读