表a
字段b 字段c
3 202450
1 202454
1 236100
1 306100
1 310000
23 310006
1 310101
1 312400
1 315040
1 315100现在要查询根据字段c前三位数字一样 b的个数,做一个统计。如何实现?
字段b 字段c
3 202450
1 202454
1 236100
1 306100
1 310000
23 310006
1 310101
1 312400
1 315040
1 315100现在要查询根据字段c前三位数字一样 b的个数,做一个统计。如何实现?
create table ta(字段b varchar2(1000),字段c varchar2(1000));
insert into ta values('3','202450');
insert into ta values('1','202454');
insert into ta values('1','236100');
insert into ta values('1','306100');
insert into ta values('1','310000');
insert into ta values('23','310006');
insert into ta values('1','310101');
insert into ta values('1','312400');
insert into ta values('1','315040');
insert into ta values('1','315100');
commit;select substr(字段c,1,3) 前三位,count(1) count
from ta
group by substr(字段c,1,3);前三位 COUNT
------ ----------
236 1
312 1
202 2
315 2
306 1
310 3select 字段c, count(1) over(partition by substr(字段c,1,3)) 统计数
from ta
字段C 统计数
------- ----------
202450 2
202454 2
236100 1
306100 1
310000 3
310101 3
310006 3
312400 1
315040 2
315100 2
create table ta(字段b varchar2(1000),字段c varchar2(1000));
insert into ta values('3','202450');
insert into ta values('1','202454');
insert into ta values('1','236100');
insert into ta values('1','306100');
insert into ta values('1','310000');
insert into ta values('23','310006');
insert into ta values('1','310101');
insert into ta values('1','312400');
insert into ta values('1','315040');
insert into ta values('1','315100');
commit;select substr(字段c,1,3) 前三位,count(1) count
from ta
group by substr(字段c,1,3);前三位 COUNT
------ ----------
236 1
312 1
202 2
315 2
306 1
310 3select 字段c, count(1) over(partition by substr(字段c,1,3)) 统计数
from ta
字段C 统计数
------- ----------
202450 2
202454 2
236100 1
306100 1
310000 3
310101 3
310006 3
312400 1
315040 2
315100 2