从oracle中的特定列检索记录 [英] Retrieve records from a specific column in oracle

问题描述

S.NO     id        Pid
1        123       PAQ123  
2        433       WSD3FF  
3        565       PAS45E  
4        123       PAQ123X  
5        433       WSD3FFY  
6        123       PAQ123Z

假设以上是数据库中的示例记录.

suppose the above is the sample records in the database.

现在，我想在数据库中查找是否有任何单词(例如PAQ123)重复使用某些前缀/后缀，例如(PAQ123X，PAQ123Z)中的单词.

Now I want to find out in the database whether there is any word (example PAQ123) which is repeating with some prefixes/suffixes like in (PAQ123X,PAQ123Z).

如何编写一个查询，该查询将导致上述列表的情况?

How can I write a query which would result into the above list scenario?

推荐答案

Oracle安装程序:

CREATE TABLE table_name ( S_NO, id, Pid ) AS
SELECT 1, 123, 'PAQ123' FROM DUAL UNION ALL
SELECT 2, 433, 'WSD3FF' FROM DUAL UNION ALL  
SELECT 3, 565, 'PAS45E' FROM DUAL UNION ALL
SELECT 4, 123, 'PAQ123X' FROM DUAL UNION ALL
SELECT 5, 433, 'WSD3FFY' FROM DUAL UNION ALL
SELECT 6, 123, 'PAQ123Z' FROM DUAL;

查询:

SELECT *
FROM   (
  SELECT t.*,
         ( SELECT COUNT(*)
           FROM   table_name x
           WHERE  t.id = x.id
           AND    LENGTH( t.Pid ) < LENGTH( x.pid )
           AND    INSTR( x.Pid, t.Pid ) = 1 ) AS num_matches
  FROM   Table_name t
)
WHERE num_matches > 0;

输出:

      S_NO         ID PID     NUM_MATCHES
---------- ---------- ------- -----------
         1        123 PAQ123            2 
         2        433 WSD3FF            1 

如果要获取匹配项，则可以使用集合:

If you want to get the matches then you can use a collection:

CREATE TYPE stringlist AS TABLE OF VARCHAR2(100);
/

查询:

SELECT *
FROM   (
  SELECT t.*,
         CAST(
           MULTISET(
             SELECT PID
             FROM   table_name x
             WHERE  t.id = x.id
             AND    LENGTH( t.Pid ) < LENGTH( x.pid )
             AND    INSTR( x.Pid, t.Pid ) = 1
           )
           AS stringlist
         ) AS matches
  FROM   Table_name t
)
WHERE matches IS NOT EMPTY;

或(由于我不确定MULTISET是否在10g中):

or (since I'm not sure MULTISET is in 10g):

SELECT *
FROM   (
  SELECT t.*,
         CAST(
           ( 
             SELECT COLLECT( PID )
             FROM   table_name x
             WHERE  t.id = x.id
             AND    LENGTH( t.Pid ) < LENGTH( x.pid )
             AND    INSTR( x.Pid, t.Pid ) = 1
           )
           AS stringlist
         ) AS matches
  FROM   Table_name t
)
WHERE matches IS NOT EMPTY;

输出

      S_NO         ID PID     MATCHES 
---------- ---------- ------- ------------------------------------
         1        123 PAQ123  TEST.STRINGLIST('PAQ123X','PAQ123Z')
         2        433 WSD3FF  TEST.STRINGLIST('WSD3FFY')

这篇关于从oracle中的特定列检索记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！