SELECT POST.ID, POST.TAG_ID, TAG.ID, TAG.NAME, COUNT(*) AS TOTAL
FROM POST
LEFT JOIN TAG ON ',' || TAG.ID || ',' LIKE '%,' || POST.TAG_ID || ',%'
GROUP BY TAG.NAME
;
with t as (
select regexp_substr(TAG_ID,'d',1,level) as ID
,count(*) as NUB_of_POSTS
from post
connect by ID = prior ID and level <= regexp_count(TAG_ID,'d') and sys_guid() <> prior sys_guid()
group by regexp_substr(TAG_ID,'d',1,level)
)
select NAME
,NUB_of_POSTS
from t join tag using(ID)
SQL> with
2 post (id, tag_id) as
3 (select 1, '1; 2; 3' from dual union all
4 select 2, '1; 2' from dual union all
5 select 3, '3' from dual union all
6 select 4, '1; 3' from dual union all
7 select 5, '2; 3' from dual
8 ),
9 tag (id, name) as
10 (select 1, 'TAG_1' from dual union all
11 select 2, 'TAG_2' from dual union all
12 select 3, 'TAG_3' from dual
13 ),
查询从此处开始；请参阅代码中的注解
14 -- split POSTs TAG_ID into rows
15 temp as
16 (select p.id,
17 trim(regexp_substr(p.tag_id, '[^;]+', 1, column_value)) tag_id
18 from post p cross join
19 table(cast(multiset(select level from dual
20 connect by level <= regexp_count(p.tag_id, ';') + 1
21 ) as sys.odcinumberlist ))
22 )
23 -- finally, a simple join between TAG and TEMP with the COUNT aggregate function
24 select t.name as tag,
25 count(*) num_of_posts
26 from tag t join temp p on p.tag_id = t.id
27 group by t.name;
TAG NUM_OF_POSTS
----- ------------
TAG_1 3
TAG_2 3
TAG_3 4
SQL>
2条答案
按热度按时间b1uwtaje1#
我们使用
regexp_substr来拆分TAG_ID，按结果分组，然后加入TAG TABLE。名称|nub_of_post
Tag_1|3
Tag_2|3
Tag_3|4
Fiddle
but5z9lq2#
同样的..。
样本数据：
查询从此处开始；请参阅代码中的注解