英文:

may i know how to write a SQL query to check if the superset of value exists in the same column

问题 {#heading}

以下是翻译好的部分：

我正在尝试检查是否存在相同列中的值的超集，如果存在，则只选择超集行。
这是我的数据。
这是数据

| ID | 值 | |----|-------| | 1 | abc | | 2 | abcd | | 3 | abcde | | 4 | xyz | | 5 | lmn | | 6 | lmno |

ID 3 是 1 和 2 的超集，ID 4 没有任何超集，ID 6 是 5 的超集。因此，我期望结果集中包括 ID 3、4 和 6。

感谢任何关于此的输入。
注意：我正在使用 Oracle 作为我的数据库。

以下是预期结果：

预期结果

| ID | 值 | |----|-------| | 3 | abcde | | 4 | xyz | | 6 | lmno |

英文:

i am trying to check if a superset of value exists in the same column, if exists select only the superset row.
Here is my data.
Here is the data

| ID | Value | |----|-------| | 1 | abc | | 2 | abcd | | 3 | abcde | | 4 | xyz | | 5 | lmn | | 6 | lmno |

ID 3 is the superset of 1 and 2, ID 4 doesn't have any superset and ID 6 is the superset of 5. so i am expecting ID 3,4 and 6 in the result set.

Appreciate any inputs on this.
Note: i am using Oracle as my DB.

Following is the expected result:

Expected Result

| ID | Value | |----|-------| | 3 | abcde | | 4 | xyz | | 6 | lmno |

答案1 {#1}

得分: 1

6 lmno
4 xyz
3 abcde 英文:

with data(ID, Value) as (
	select 1, 'abc' from dual union all
	select 2, 'abcd' from dual union all
	select 3, 'abcde' from dual union all
	select 4, 'xyz' from dual union all
	select 5, 'lmn' from dual union all
	select 6, 'lmno' from dual -- union all
)
select * from data d1
where not exists(select 1 from data d2 where d1.id <> d2.id and substr(d2.value, 1, length(d1.value)) = d1.value)
;
`6	lmno
4	xyz
3	abcde
`