开发者

SQL find similar rows without having all combination

I have this SQL table:

CREATE TABLE DATA (
  ID                NUMBER        NOT NULL,
  CODE              VARCHAR(32)   NOT NULL,
  DESCRIPTION       VARCHAR(256),
  PRIMARY KEY  (ID)
);

and some values in it like:

ID    CODE    DESC
1     'A'     'AAAA'
2     'B'     'BBB'
3     'A'     'AGAIN'
4     'C'     'CCC'
5     'A'     'ABBA'

I want to retrieve all lines with similar code because I need to add a UNIQUE constraint on the code column.

I have this query:

select distinct 
  t1.code, t1.id, t1.description, t2.id, t2.description
from 
  data t1, data t2  
where 
  t1.code = t2.code and t1.id != t2.id
order by t1.code

That is returning me this result set:

A   5   ABBA    1   AAAA
A   3   AGAIN   1   AAAA
A   1   AAAA开发者_JAVA百科    5   ABBA
A   1   AAAA    3   AGAIN
A   3   AGAIN   5   ABBA
A   5   ABBA    3   AGAIN

How can I tweak the query to discard combination of already listed differences ? In this example I want to have only this rows:

A   1   AAAA    3   AGAIN
A   1   AAAA    5   ABBA  
A   3   AGAIN   5   ABBA


you could take only one of each couple of rows, for instance:

select distinct 
  t1.code, t1.id, t1.description, t2.id, t2.description
from 
  data t1, data t2  
where 
  t1.code = t2.code and t1.id < t2.id
order by t1.code
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜