PLSQL：如何計算列中所有值中子字串的出現次數

我在 Oracle 資料庫中有這樣的表：

我需要計算每個substring列在所有行中的出現次數string。一個示例結果是：

我怎樣才能達到這個結果？

uj5u.com熱心網友回復：

幾個解決方案： DBFiddle

1. cross apply（或橫向或交叉連接）：

select *
from t 
    cross apply(
       select count(*) cnt
       from t t2 
       where t2.string like '%'||t.substring||'%'
    ) a
order by substring;

2. 子查詢：

select
  t.*
  ,(
       select count(*) cnt
       from t t2 
       where t2.string like '%'||t.substring||'%'
   ) cnt
from t
order by substring;

3. connect by：

select 
   substring,string,count(*)
from (
  select
     connect_by_root substring as substring
    ,connect_by_root string    as string
  from t
  connect by nocycle
     level<=2
     and string like '%'||(prior t.substring)||'%'
)
group by substring,string
order by substring;

4. model：

select 
  substring,string,cnt
from t
model
  dimension by (substring,string)
  measures(0 as cnt,string as string2)
  rules(
    cnt[any,any] order by substring = count(*)[any,string like '%'||cv()||'%']
  )
order by substring;

5. xmlquery xmlagg：

select--  NO_XML_QUERY_REWRITE
  substring,string,
  xmlcast(
         xmlquery(
           'count($D/ROW/VAL[contains(., $X)])'
           passing
              xmlelement("ROW", (xmlagg(xmlelement(VAL, string)) over())) as d, 
              substring as x
           returning content) as number) as cnt
from t
order by substring;

uj5u.com熱心網友回復：

這可能是一種選擇。

對于樣本資料

SQL> with test (substring, string) as
  2    (select 'abc', '123-def-abc' from dual union all
  3     select 'def', '123-def'     from dual union all
  4     select 'ghi', '123-def-ghi' from dual union all
  5     select 'jkl', '123-456-jkl' from dual union all
  6     select 'mno', '123-456-jkl-mno' from dual
  7    ),

檢查是否substring存在string（并使用交叉連接，因為您必須檢查所有組合）

  8  temp as
  9    (select a.substring,
 10            case when instr(b.string, a.substring) > 1 then 1 else 0 end cnt
 11     from test a cross join test b
 12  --   group by a.substring
 13    )

最后，通過連接“原始”表和tempCTE 回傳結果：

 14  select a.substring, a.string, sum(b.cnt) cnt
 15  from test a join temp b on a.substring = b.substring
 16  group by a.substring, a.string
 17  order by a.substring;

SUB STRING                 CNT
--- --------------- ----------
abc 123-def-abc              1
def 123-def                  3
ghi 123-def-ghi              1
jkl 123-456-jkl              2
mno 123-456-jkl-mno          1

SQL>

uj5u.com熱心網友回復：

這可以通過對原始表進行 2 次呼叫的 CTE 來完成，一次使用 distinct 獲取子字串，另一次使用交叉連接的每個字串的所有出現。然后我們使用聚合來計算每個匹配項的出現次數。

create table if not exists strings(
   substrin varchar(3),
   strin varchar(20)
   );
delete from strings;
insert into strings values('abc','123-def-abc');
insert into strings values('def','123-def');
insert into strings values('def','123-def');
insert into strings values('ghi','123-def-ghi');
insert into strings values('jkl','123-456-jkl');
insert into strings values('jkl','123-456-jkl');
insert into strings values('mno','123-456-jkl-mno');
with sss as
   ( select s1.substrin sb, s2.strin sg
   from (Select distinct substrin from strings ) s1,
   strings s2)
select
   sb 'substring',
   sg 'string',
   count(*) 'count'
from sss
where sg like concat('%',sb,'%')
group by sg,sb;

請注意，這并沒有給出與子字串匹配的總行數，而是每對出現的次數。

標籤：sql 甲骨文 plsql

上一篇：查找過去一年銷量少于20件的產品的最有效方法是什么？(SQL)

下一篇：在SQL中限制LAG