我想从一张表中选择案例,其中Code或DST或Short_text或long_text相等(在2行或更多行中)且ID不相等。
ID Code DST Short_text Long_text
1 B 01 B 1 Bez1 Bezirk1
1 B 01 B 1 Bez1 Bezirk1
2 B 02 B 2 Bez2 Bezirk2
3 B 03 B 3 Bez3 Bezirk3
4 B 04 B 4 Bez4 Bezirk4
4 B 04 B 4 Bez4 Bezirk4
5 B 05 B 5 Bez5 Bezirk5
6 B 06 B 6 Bez6 Bezirk6
7 B 07 B 7 Bez7 Bezirk7
8 B 08 B 8 Bez8 Bezirk8
9 B 09 B 9 Bez9 Bezirk9
97 M 51 M 52 MA 51 Sport
96 M 51 M 51 MA 51 Sport
我想得到以下结果:
97 M 51 M 52 MA 51 Sport
96 M 51 M 51 MA 51 Sport
因为它们具有不同的ID,但是它们具有相似的代码或SImilar Short_text或simmlar long_text。
这是我尝试过的:
select
ID,
CODE,
DST,
Short_text,
Long_text,
from Main_table tbl
where load_date = (select max(load_date) from Main_table)
and exists
(
select 1 from Main_table
where
tbl.ID != ID
and (tbl.CODE = CODE
or tbl.DST = DST
or tbl.short_text = short_text
or tbl.long_text = long_text)
);
但这并没有给我理想的结果。您有想法如何改善查询?
那是
SQL> select * from main_table;
ID CODE DST SHORT LONG_TE
---------- ---- ---- ----- -------
1 B 01 B 1 Bez1 Bezirk1
1 B 01 B 1 Bez1 Bezirk1
2 B 02 B 2 Bez2 Bezirk2
3 B 03 B 3 Bez3 Bezirk3
4 B 04 B 4 Bez4 Bezirk4
4 B 04 B 4 Bez4 Bezirk4
5 B 05 B 5 Bez5 Bezirk5
6 B 06 B 6 Bez6 Bezirk6
7 B 07 B 7 Bez7 Bezirk7
8 B 08 B 8 Bez8 Bezirk8
9 B 09 B 9 Bez9 Bezirk9
97 M 51 M 52 MA 51 Sport
96 M 51 M 51 MA 51 Sport
13 rows selected.
SQL> select a.*
2 from main_table a
3 join main_table b
4 on a.id <> b.id
5 and ( a.code = b.code
6 or a.dst = b.dst
7 or a.short_text = b.short_text
8 or a.long_text = b.long_text);
ID CODE DST SHORT LONG_TE
---------- ---- ---- ----- -------
97 M 51 M 52 MA 51 Sport
96 M 51 M 51 MA 51 Sport
SQL>