计算oracle表中每一行的出现次数

debugcn 发表于 Dev

超级甜甜圈

我有一个带有两列名称和疾病的表格，例如，我想计算在表格中发现每种疾病的时间

------------------------
name       | disease   |
------------------------
name1      | a         |
------------------------
name2      | b         |
------------------------
name3      | c         |
------------------------
name4      | a , b , d |
------------------------

我正在寻找可以给我这样结果的查询，可能吗？我尝试了很多查询，但没有一个以我想要的方式工作

------------------------
a          |  2        |
------------------------
b          |  2        |
------------------------
c          |  1        |
------------------------
d          |  1        |
-----------------------

小脚丫

阅读代码中的注释：

SQL> with test as
  2    -- sample data
  3   (select 'name1' as name, 'a' as disease from dual union all
  4    select 'name2' as name, 'b' as disease from dual union all
  5    select 'name3' as name, 'c' as disease from dual union all
  6    select 'name4' as name, 'a , b , d' as disease from dual
  7   ),
  8  splt as
  9   -- split the DISEASE column to rows
 10   (select name,
 11      trim(regexp_substr(disease, '[^,]+', 1, column_value)) disease
 12    from test cross
 13    join table(cast(multiset(select level from dual
 14                             connect by level <= regexp_count(disease, ',') + 1
 15                            ) as sys.odcinumberlist))
 16   )
 17  -- finally ...
 18  select disease,
 19         count(*) num
 20  from splt
 21  group by disease
 22  order by disease;

DISEASE           NUM
---------- ----------
a                   2
b                   2
c                   1
d                   1

SQL>

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。