选择其中ColA = ColB的行，然后添加一个新列，该列指示是否重复ColA中的原始数据。

我试图弄清楚如何查询一个表（该表实际上是一个结果集，因此它将是一个子查询），将其按ColA=ColB分组（请参见下文），然后一步创建所有计算字段。所以，如果我的测试数据看起来像 ColA ColB ColC 1 1 aaa 1 2 bbbb 1 3 cccc 2 2 dddd 3 3 eeee 3 4 ffff 3 5 gggg 3 6 hhhh 4 4 iiii 5 5 jjjj 6 6 kkkk 6 7分 6 8毫米我只想检索ColA=ColB中的行，并添加一个新列，告诉我是否重复了ColA中的原始数据。见下文。 ColA ColB ColC倍数 1 1 aaaa是 2 2 dddd否 3 3 eeee是 4 4 iiii否 5 5 jjjj否 6 6 kkkk是有人可以帮我解决语法问题吗？我一直在玩Group By \和SubSelects都没有用。我是否需要对case字段使用case语句？

已邀请:

4 个回复

俯乡骚钵皆

发布创建表和插入语句而不是Desc表，然后从table_name中选择*更为有用。 http://tkyte.blogspot.com/2005/06/how-to-ask-questions.html

create table test_repeat(
   cola number,
   colb number,
   colc varchar2(20)
   );

insert into test_repeat values (1,1,\'aaa\'); 
insert into test_repeat values (1,2,\'bbbb\');    
insert into test_repeat values (1,3,\'cccc\');    
insert into test_repeat values (2,2,\'dddd\');    
insert into test_repeat values (3,3,\'eeee\'); 
insert into test_repeat values (3,4,\'ffff\');    
insert into test_repeat values (3,5,\'gggg\');    
insert into test_repeat values (3,6,\'hhhh\');    
insert into test_repeat values (4,4,\'iiii\');    
insert into test_repeat values (5,5,\'jjjj\');    
insert into test_repeat values (6,6,\'kkkk\');    
insert into test_repeat values (6,7,\'llll\');    
insert into test_repeat values (6,8,\'mmmm\');
commit;

1.您可以使用Oracle分析功能Lead来浏览您的结果集，以查看colA是否与下一行相同（在订购后）。

select * from
(select colA, colb,
       (case when colA = (lead(cola) over 
                          (partition by colA order by cola, colb))
            then \'Yes\'
            else \'No\'
       end) multiples,
       colc
  from test_repeat)
  where colA = colb
/

      COLA       COLB MUL COLC
---------- ---------- --- --------------------
         1          1 Yes aaa
         2          2 No  dddd
         3          3 Yes eeee
         4          4 No  iiii
         5          5 No  jjjj
         6          6 Yes kkkk

2.或者，您可以获取每个COLA值的计数并进行比较以查看是否存在重复...

select a.colA, a.colb, a.colc,
       (case when (select count(*) from test_repeat t where t.cola = a.colA) > 1
            then \'Yes\'
            else \'No\'
       end) Repeat
  from test_repeat a
  where colA = colB
/

      COLA       COLB COLC                 REP
---------- ---------- -------------------- ---
         1          1 aaa                  Yes
         2          2 dddd                 No
         3          3 eeee                 Yes
         4          4 iiii                 No
         5          5 jjjj                 No
         6          6 kkkk                 Yes

它们都同样简单，但是我建议使用解析函数方法，因为我发现对于过去使用过的所有查询，它通常都更快。

磐乓铝举

SQL> select *
  2    from test_repeat
  3   order by cola
  4  /

      COLA       COLB COLC
---------- ---------- --------------------
         1          2 bbbb
         1          1 aaa
         1          3 cccc
         2          2 dddd
         3          4 ffff
         3          3 eeee
         3          5 gggg
         3          6 hhhh
         4          4 iiii
         5          5 jjjj
         6          6 kkkk
         6          7 llll
         6          8 mmmm
         7          9 nnnn

14 rows selected.

SQL> select cola \"ColA\"
  2       , max(decode(colb,cola,colb)) \"ColB\"
  3       , max(decode(colb,cola,colc)) \"ColC\"
  4       , case count(*) when 1 then \'no\' else \'yes\' end \"multiples\"
  5    from test_repeat
  6   group by cola
  7  having cola = max(decode(colb,cola,colb))
  8   order by cola
  9  /

      ColA       ColB ColC                 mul
---------- ---------- -------------------- ---
         1          1 aaa                  yes
         2          2 dddd                 no
         3          3 eeee                 yes
         4          4 iiii                 no
         5          5 jjjj                 no
         6          6 kkkk                 yes

6 rows selected.

在性能上，此SQL大约等于Rajesh的第一个查询。因此，您可以选择自己更喜欢的一种。

SQL> set autotrace on
SQL> select * from
  2  (select colA, colb,
  3         (case when colA = (lead(cola) over
  4                            (partition by colA order by cola, colb))
  5              then \'Yes\'
  6              else \'No\'
  7         end) multiples,
  8         colc
  9    from test_repeat)
 10    where colA = colb
 11  /

      COLA       COLB MUL COLC
---------- ---------- --- --------------------
         1          1 Yes aaa
         2          2 No  dddd
         3          3 Yes eeee
         4          4 No  iiii
         5          5 No  jjjj
         6          6 Yes kkkk

6 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1491815685

-----------------------------------------------------------------------------------
| Id  | Operation           | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |             |    14 |   574 |     4  (25)| 00:00:01 |
|*  1 |  VIEW               |             |    14 |   574 |     4  (25)| 00:00:01 |
|   2 |   WINDOW SORT       |             |    14 |   532 |     4  (25)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| TEST_REPEAT |    14 |   532 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(\"COLA\"=\"COLB\")

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
          4  recursive calls
          0  db block gets
         15  consistent gets
          0  physical reads
          0  redo size
        421  bytes sent via SQL*Net to client
        238  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          6  rows processed

SQL> /

      COLA       COLB MUL COLC
---------- ---------- --- --------------------
         1          1 Yes aaa
         2          2 No  dddd
         3          3 Yes eeee
         4          4 No  iiii
         5          5 No  jjjj
         6          6 Yes kkkk

6 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1491815685

-----------------------------------------------------------------------------------
| Id  | Operation           | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |             |    14 |   574 |     4  (25)| 00:00:01 |
|*  1 |  VIEW               |             |    14 |   574 |     4  (25)| 00:00:01 |
|   2 |   WINDOW SORT       |             |    14 |   532 |     4  (25)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| TEST_REPEAT |    14 |   532 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(\"COLA\"=\"COLB\")

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          7  consistent gets
          0  physical reads
          0  redo size
        421  bytes sent via SQL*Net to client
        238  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          6  rows processed

SQL> select cola \"ColA\"
  2       , max(decode(colb,cola,colb)) \"ColB\"
  3       , max(decode(colb,cola,colc)) \"ColC\"
  4       , case count(*) when 1 then \'no\' else \'yes\' end \"multiples\"
  5    from test_repeat
  6   group by cola
  7  having cola = max(decode(colb,cola,colb))
  8   order by cola
  9  /

      ColA       ColB ColC                 mul
---------- ---------- -------------------- ---
         1          1 aaa                  yes
         2          2 dddd                 no
         3          3 eeee                 yes
         4          4 iiii                 no
         5          5 jjjj                 no
         6          6 kkkk                 yes

6 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3021378319

-----------------------------------------------------------------------------------
| Id  | Operation           | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |             |    14 |   532 |     4  (25)| 00:00:01 |
|*  1 |  FILTER             |             |       |       |            |          |
|   2 |   SORT GROUP BY     |             |    14 |   532 |     4  (25)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| TEST_REPEAT |    14 |   532 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(\"COLA\"=MAX(DECODE(\"COLB\",\"COLA\",\"COLB\")))

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          7  consistent gets
          0  physical reads
          0  redo size
        421  bytes sent via SQL*Net to client
        238  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          6  rows processed

SQL> /

      ColA       ColB ColC                 mul
---------- ---------- -------------------- ---
         1          1 aaa                  yes
         2          2 dddd                 no
         3          3 eeee                 yes
         4          4 iiii                 no
         5          5 jjjj                 no
         6          6 kkkk                 yes

6 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3021378319

-----------------------------------------------------------------------------------
| Id  | Operation           | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |             |    14 |   532 |     4  (25)| 00:00:01 |
|*  1 |  FILTER             |             |       |       |            |          |
|   2 |   SORT GROUP BY     |             |    14 |   532 |     4  (25)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| TEST_REPEAT |    14 |   532 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(\"COLA\"=MAX(DECODE(\"COLB\",\"COLA\",\"COLB\")))

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          7  consistent gets
          0  physical reads
          0  redo size
        420  bytes sent via SQL*Net to client
        238  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          6  rows processed

SQL> set autotrace off

问候，抢。

昧伎

我认为您需要对multiples列进行子选择。无需分组。写类似：

select ColA, ColB, ColC, 
       (CASE
        WHEN (select b.ColA from thistable b where b.ColA = a.ColA) > 1 THEN \'yes\'
        ELSE \'no\'
        END)
from thistable a
where ColA = ColB

布埃郝卞簿

我不知道Lead函数，非常好。 Rob和Rajesh，感谢您到目前为止的回答，我认为我能够使用两个子查询和一个外部联接部分地回答我自己的问题。我猜我的查询不如其他人发布的查询有效，但是我想知道人们是否可以对此查询发表评论。它会一直有效吗？可以对其进行调整以提高效率吗？它仍然需要输入\'yes \'或\'no \'作为倍数，现在它会输入倍数或为0的null。

SELECT BB.ColA, ColB, ColC, AA.CNT 
FROM
(SELECT ColA, count(*) as CNT FROM TMPY where ColA<>ColB Group by ColA)  AA, 
(SELECT ColA, ColB, ColC FROM TMPY WHERE COLA=COLB ) BB 
WHERE
AA.COLa(+)=BB.COLB order by bb.ColA;

再次感谢你！

要回复问题请先登录或注册

选择其中ColA = ColB的行，然后添加一个新列，该列指示是否重复ColA中的原始数据。

4 个回复

发起人

sql

oracle

group_by

case

having

问题状态

选择其中ColA = ColB的行，然后添加一个新列，该列指示是否重复ColA中的原始数据。

与内容相关的链接

4 个回复

发起人

sql

oracle

group_by

case

having

问题状态