如何从oracle表中选择不同的值及其出现次数?

Question

我有一个表，其中包含以下数据示例:

tbody> <<tr>13131313

id	first_numbers	second_numbers
12	111	112
12	111	123
12	111	122
12	110	123
	100	空
	100	101
	112	999
	空	999

你的问题与关系无关。将表值视为平面文本文件或Excel工作表来处理。我建议你复习一下关系概念和规范化。

无论如何，你可以扭曲SQL的手去做你想做的事，但这不是一个自然的解决方案。例如，可以这样做:

select
coalesce(x.id, y.id) as id,
coalesce(x.r, y.r) as r,
x.f, x.c,
y.s, y.c
from (
select id, f, count(*) as c,
row_number() over(partition by id order by f) as r from t group by id, f
) x
full join (
select id, s, count(*) as c,
row_number() over(partition by id order by s) as r from t group by id, s
) y on y.id = x.id and y.r = x.r
where f is not null or s is not null
order by id, r

结果:

ID  R  F    C  S    C 
--- -- ---- -- ---- - 
12  1  110  1  112  1 
12  2  111  3  122  1 
12  3          123  2 
13  1  100  2  101  1 
13  2  112  1  999  2 

参见运行示例:db<>fiddle。