我有这样的表格:
+-----+------+
| acc | CASE |
+-----+------+
| 001 | a |
| 001 | b |
| 001 | c |
| 002 | a |
| 002 | b |
| 003 | b |
| 003 | c |
| 004 | a |
| 005 | b |
| 006 | b |
| 007 | a |
| 007 | b |
| 007 | c |
| 008 | a |
| 008 | b |
| n | x |
+-----+------+
我不知道如何使用对数据进行分组和计数
+-----------+-----------+
| case | count_acc |
+-----------+-----------+
| a | 1 |
| b | 2 |
| c | 0 |
| a+b | 2 |
| b+c | 1 |
| a+b+c | 2 |
| a+b+c+…+x | n |
+-----------+-----------+
在A+B,B+C的情况下... a+b+c+...+x 我无法对大小写和计数进行分组。你有什么想法要分组和计数吗?
select b.case,count(distinct(a.acc)) as account from
test a , (select acc , rtrim(case,'+') case
from ( select acc , case , rn from test
model
partition by (acc)
dimension by (row_number() over (partition by acc order by case) rn )
measures (cast(case as varchar2(10)) case)
rules
(case[any] order by rn desc = case[cv()]||'+'||case[cv()+1])
)
where rn = 1) b
where a.acc = b.acc
group by b.case
你可以在oracle中使用LISTAGG实现相同的目标
with test as ( select 001 acc , 'a' case FROM DUAL UNION
SELECT 001 , 'b' FROM DUAL UNION
SELECT 001 , 'c' FROM DUAL UNION
SELECT 002 , 'a' FROM DUAL UNION
SELECT 002 , 'b' FROM DUAL UNION
SELECT 003 , 'b' FROM DUAL UNION
SELECT 003 , 'c' FROM DUAL UNION
SELECT 004 , 'a' FROM DUAL UNION
SELECT 005 , 'b' FROM DUAL UNION
SELECT 006 , 'b' FROM DUAL UNION
SELECT 007 , 'a' FROM DUAL UNION
SELECT 007 , 'b' FROM DUAL UNION
SELECT 007 , 'c' FROM DUAL UNION
SELECT 008 , 'a' FROM DUAL UNION
SELECT 008 , 'b' FROM DUAL )
select case,count(1) from
(
SELECT count(1),acc, LISTAGG(case, '+') WITHIN GROUP (ORDER BY acc) AS case
FROM test
GROUP BY acc) GROUP BY case order by 1;
SQL小提琴在这里
首先,您必须组织数据,对 ACC 进行分组并聚合 CASE
SELECT
LISTAGG (case,'+') WITHIN GROUP (ORDER BY case) case,
ACC
FROM TEST
GROUP BY ACC
他们将能够计数:
SELECT case, count(*) FROM (
SELECT
LISTAGG (case,'+') WITHIN GROUP (ORDER BY case) case,
ACC
FROM TEST
GROUP BY ACC
) GROUP BY case
ORDER BY CASE;
如果您没有LISTAGG功能,例如低于11g,请参阅此网站:
http://oracle-base.com/articles/misc/string-aggregation-techniques