oracle - Oracle选择两个相邻的行对于给定列的值为相同

  显示原文与译文双语对照的内容

如何在Oracle中执行以下操作:

我有一张( 简化) 表:


+-----+-----+-----+
| a | b |.. . |
+-----+-----+-----+
| 1 | 7 |.. . |
| 2 | 5 |.. . |
| 1 | 7 |.. . |
+-----+-----+-----+

a 作为一个人的唯一标识符,b 是我对行匹配的字段。 我如何构造一个基本上说"告诉我person有多个 b 值的人的位置( 例如 。)的查询?

到目前为止,我尝试过:


SELECT a FROM mytable GROUP BY a HAVING COUNT(DISTINCT b)> 1;

这种感觉很接近,只是它只是给了我用户 multiple唯一的b,我怀疑它来自于 DISTINCT 部分,但是我不知道如何更改查询来达到我想要的。

时间: 作者:

try


group by a,b having count(b)> 1

你的7,5,7 将计数为 2 ( 一个 7,一个 5 ) 。 这一项将计算任何分组中的总 Bs,因此你将获得 1,7 -> 21,5 -> 1

作者:

Oracle R2架构设置的收费:


CREATE TABLE mytable ( a, b ) AS
SELECT LEVEL, LEVEL FROM DUAL CONNECT BY LEVEL <= 2000
UNION ALL
SELECT LEVEL *2, LEVEL * 2 FROM DUAL CONNECT BY LEVEL <= 1000;

查询 1:


WITH data AS (
 SELECT a
 FROM mytable
 GROUP BY a
 HAVING COUNT(b)> COUNT( DISTINCT b )
 ORDER BY a
),
numbered AS (
 SELECT a,
 ROWNUM AS rn
 FROM data
)
SELECT a
FROM numbered
WHERE rn <= 20

结果:


| A |
|----|
| 2 |
| 4 |
| 6 |
| 8 |
| 10 |
| 12 |
| 14 |
| 16 |
| 18 |
| 20 |
| 22 |
| 24 |
| 26 |
| 28 |
| 30 |
| 32 |
| 34 |
| 36 |
| 38 |
| 40 |

作者:
...