如何从逗号分隔的字符串中获取出现次数 [英] How to get the count of occurrence from comma separated string

问题描述

我在Oracle 8i

对于T1表中的每个col1，我们需要找出T2表中的出现次数(用于匹配col3).

For each col1 in T1 table, we need to find out the number of occurrences in T2 table (for matching col3).

问题

在Oracle中使用单个查询(不创建临时表)的最佳方法是什么?

What is the best way to achieve this in Oracle using a single query (without creating temporary table)?

小提琴

预期结果

"C1"-10-1

‘C1’-8-1

"C2"-10-1

"C3"-10-0

"C4"-10-2

SQL

--Table 1
CREATE TABLE T1 (col1 varchar2(2), col2 varchar(8), col3 NUMBER);

INSERT INTO T1  (col1, col2, col3) VALUES  ('C1', 'john',10);
INSERT INTO T1  (col1, col2, col3) VALUES ('C1', 'nishal',8);
INSERT INTO T1  (col1, col2, col3) VALUES  ('C2', 'piers',10);
INSERT INTO T1  (col1, col2, col3) VALUES  ('C3', 'sara',10);
INSERT INTO T1  (col1, col2, col3) VALUES  ('C4', 'lijo',10);


--Table 2

CREATE TABLE T2 (col1 varchar2(2), col2 varchar(8), col3 NUMBER);

INSERT INTO T2  (col1, col2 , col3) VALUES ('R0', 'C1,C4',10);
INSERT INTO T2  (col1, col2 , col3) VALUES ('R1', 'C1',8);
INSERT INTO T2  (col1, col2 , col3) VALUES ('R2', 'C2,C4',10);

小提琴

推荐答案

select  T1.col1
,       T1.col3
,       count(T2.col1)
from    T1
left join
        T2
on      T1.col3 = T2.col3
        and ',' || T2.col2 || ',' like '%,' || T1.col1 || ',%'
group by
        T1.col1
,       T1.col3

SQL Fiddle中的示例.

这篇关于如何从逗号分隔的字符串中获取出现次数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！