Oracle选择共同的子字符串 [英] Oracle select mutual sub string

问题描述

我想在ORACLE中返回新表，其中所有行"col"列中的值相同并且说明"列将仅包含相互的子字符串何时将不同的字符替换为"..."

I would like to return new table in ORACLE where all rows that have same the values in 'col' columns group together and the 'description' column will contain only the mutual sub strings when the different characters will replaced by '...'

我该怎么做?我可以帮忙吗?

how can I do that? May i get your help please?

以以下代码开头的基本代码:选择列，描述从表按col分组；

Basic code to start with: SELECT col,description FROM table group by col;

示例1:

col description
1   Today is 
1   Today is a good day
1   Today is perfect day
2   Hello world
2   Hello

结果:

col description
1   Today is …
2   Hello…

示例2:

col description
1   Today is a good day
1   Today is perfect day
2   Hello world
2   Hello I'm here
3   Hi

结果:

col description
1   Today is …
2   Hello…
3   Hi

谢谢！

推荐答案

这将回答问题的原始版本.

This answers the original version of the question.

您可以使用不存在:

select col, description || ' ...'
from t
where not exists (select 1
                  from t t2
                  where t2.description like t.description || '%' and
                        t2.descriptoin <> t.description
                 );

请注意，在大桌子上，这样效率不高！

Note that on a large table, this will not be efficient!

这篇关于Oracle选择共同的子字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！