根据oracle中的某些条件逐行获取数据

Question

我在table数据库中有一个Oracle。我想逐行获取具有相同列值的数据。例如：

1   871412  007607000176    12.02.2015 15:03:29 007607000176    ELTN    007607000176    ELTN    52079   28  156608  654108
2   753281  007607000176    23.01.2014 13:13:38 007607000176    ELTN    007607000176    ELTN    43494   24  82860   580360
3   739033  007607000176    23.01.2014 13:10:53 007607000176    ELTN    007607000176    ELTN    43494   24  82860   580360
4   528902  007607000176    22.02.2013 16:19:37 007607000176    ELTN    007607000176    ELTN    46776   26  19521   517021
5   468832  007607000176    15.02.2012 16:00:58 007607000176    ELTN    007607000176    ELTN    44708   32  10395   442895
6   36782   007607000176    01.04.2011  007607000176    ELTN    007607000176    ELTN                
7   36781   007607000176    01.04.2010  007607000176    ELTN    007607000176    ELTN                
8   36780   007607000176    01.04.2009  007607000176    ELTN    007607000176    ELTN                
9   36779   007607000176    30.12.2007  007607000176    ELTN    007607000176    PRTG                
10  36778   007607000176    01.04.2007  007607000176    PRTG    007607000176    BASC                
11  36777   007607000176    01.04.2006  007607000176    BASC    007607000176    PRTG                
12  36776   007607000176    01.04.2005  007607000176    PRTG    007607000176    PRTG    

我想要6-7-8行，就像below.Because一样，它们的一些列是相同的。

ELTN    007607000176    ELTN                
ELTN    007607000176    ELTN                
ELTN    007607000176    ELTN    

我希望表中的所有记录都像这种情况一样，一行行。我该怎么做？

编辑

我使用WITH子句来解决这个问题。首先，我把数据和小组一起带来了。之后，我逐行地带来了重复的数据信息。

Answer 1

SQL Fiddle

Oracle 11g R2架构设置

CREATE TABLE TEST ( A, B, C, D, E, F, G, H, I, J, K, L ) AS
          SELECT 1,   871412,  '007607000176',    TIMESTAMP '2015-02-12 15:03:29', '007607000176',    'ELTN',    '007607000176',    'ELTN',    52079,   28,  156608,  654108 FROM DUAL
UNION ALL SELECT 2,   753281,  '007607000176',    TIMESTAMP '2014-01-23 13:13:38', '007607000176',    'ELTN',    '007607000176',    'ELTN',    43494,   24,  82860,   580360 FROM DUAL
UNION ALL SELECT 3,   739033,  '007607000176',    TIMESTAMP '2014-01-23 13:10:53', '007607000176',    'ELTN',    '007607000176',    'ELTN',    43494,   24,  82860,   580360 FROM DUAL
UNION ALL SELECT 4,   528902,  '007607000176',    TIMESTAMP '2013-02-22 16:19:37', '007607000176',    'ELTN',    '007607000176',    'ELTN',    46776,   26,  19521,   517021 FROM DUAL
UNION ALL SELECT 5,   468832,  '007607000176',    TIMESTAMP '2012-02-15 16:00:58', '007607000176',    'ELTN',    '007607000176',    'ELTN',    44708,   32,  10395,   442895 FROM DUAL
UNION ALL SELECT 6,   36782,   '007607000176',    TIMESTAMP '2011-04-01 00:00:00', '007607000176',    'ELTN',    '007607000176',    'ELTN', NULL, NULL, NULL, NULL FROM DUAL
UNION ALL SELECT 7,   36781,   '007607000176',    TIMESTAMP '2010-04-01 00:00:00', '007607000176',    'ELTN',    '007607000176',    'ELTN', NULL, NULL, NULL, NULL FROM DUAL
UNION ALL SELECT 8,   36780,   '007607000176',    TIMESTAMP '2009-04-01 00:00:00', '007607000176',    'ELTN',    '007607000176',    'ELTN', NULL, NULL, NULL, NULL FROM DUAL
UNION ALL SELECT 9,   36779,   '007607000176',    TIMESTAMP '2007-12-30 00:00:00', '007607000176',    'ELTN',    '007607000176',    'PRTG', NULL, NULL, NULL, NULL FROM DUAL
UNION ALL SELECT 10,  36778,   '007607000176',    TIMESTAMP '2007-04-01 00:00:00', '007607000176',    'PRTG',    '007607000176',    'BASC', NULL, NULL, NULL, NULL FROM DUAL
UNION ALL SELECT 11,  36777,   '007607000176',    TIMESTAMP '2006-04-01 00:00:00', '007607000176',    'BASC',    '007607000176',    'PRTG', NULL, NULL, NULL, NULL FROM DUAL
UNION ALL SELECT 12,  36776,   '007607000176',    TIMESTAMP '2005-04-01 00:00:00', '007607000176',    'PRTG',    '007607000176',    'PRTG', NULL, NULL, NULL, NULL FROM DUAL

查询1

SELECT A, C, E, F, G, H, I, J, K, L
FROM   TEST t
WHERE EXISTS (
  SELECT 'X'
  FROM   TEST x
  WHERE  x.ROWID <> t.ROWID
  AND    x.C = t.C
  AND    x.E = t.E
  AND    x.F = t.F
  AND    x.G = t.G
  AND    x.H = t.H
  AND    (x.I = t.I OR ( x.I IS NULL AND t.I IS NULL ))
  AND    (x.J = t.J OR ( x.J IS NULL AND t.J IS NULL ))
  AND    (x.K = t.K OR ( x.K IS NULL AND t.K IS NULL ))
  AND    (x.L = t.L OR ( x.L IS NULL AND t.L IS NULL ))
)

结果

| A |            C |            E |    F |            G |    H |      I |      J |      K |      L |
|---|--------------|--------------|------|--------------|------|--------|--------|--------|--------|
| 2 | 007607000176 | 007607000176 | ELTN | 007607000176 | ELTN |  43494 |     24 |  82860 | 580360 |
| 3 | 007607000176 | 007607000176 | ELTN | 007607000176 | ELTN |  43494 |     24 |  82860 | 580360 |
| 6 | 007607000176 | 007607000176 | ELTN | 007607000176 | ELTN | (null) | (null) | (null) | (null) |
| 7 | 007607000176 | 007607000176 | ELTN | 007607000176 | ELTN | (null) | (null) | (null) | (null) |
| 8 | 007607000176 | 007607000176 | ELTN | 007607000176 | ELTN | (null) | (null) | (null) | (null) |

Answer 2

一种解决方案可以是自己连接表，并检查每个相关列都是相同的，除了id。因此，如果除了id之外，有完全相同数据的行，则只能获得一个联接：

   Select a.colname, a.colname, a.colname from table as a
   inner join table as b on a.Id != b.ID 
  and a.date = b.date and a.Code = b.Code ... all other relevant columns... and a.code2 = b.code2