a(字段)
01,02
01,03
01,04,02
02
01,02,03,04
02,04
就这么一个字段 求如何通过sql统计出
01出现的次数
02出现的次数
03 出现的次数
04出现的次数一个sql语句实现
01,02
01,03
01,04,02
02
01,02,03,04
02,04
就这么一个字段 求如何通过sql统计出
01出现的次数
02出现的次数
03 出现的次数
04出现的次数一个sql语句实现
解决方案 »
- Oracle - OraClient10g_home1里的enterprise manager console 重启电脑后 不见了
- 关于oralce打补丁升级问题[初学者的问题]
- 如何把excel中的数据导入到oracle数据库中
- 有关于存储过程?在线!
- Oracle学习笔记(chstone原创)
- OracleTypes.CURSOR在java的jdbc驱动中中对应的是什么类型??
- HP-UNIX上的ORACLE错误,急!等待!
- 关于建立ID(sequences)主键和用两个外键做联合主键的问题.
- 十万火急!!!插入CLOB大对象时出错!!!
- Oracle结构
- 为什么下面的两段代码结果会不一样呢?
- sql server存储过程迁移到oracle上时出现的问题?
2 sum(decode(instr(name,'01'),0,0,1)),
3 sum(decode(instr(name,'02'),0,0,1)),
4 sum(decode(instr(name,'03'),0,0,1)),
5 sum(decode(instr(name,'04'),0,0,1)) from t
6 ;SUM(DECODE(INSTR(NAME,'01'),0, SUM(DECODE(INSTR(NAME,'02'),0, SUM(DECODE(INSTR(NAME,'03'),0, SUM(DECODE(INSTR(NAME,'04'),0,
------------------------------ ------------------------------ ------------------------------ ------------------------------
4 5 2 3SQL>
sum(length(a) - nvl(length(replace(a,'02')),0)/2 as "02",
sum(length(a) - nvl(length(replace(a,'03')),0)/2 as "03",
sum(length(a) - nvl(length(replace(a,'04')),0)/2 as "04"
from t;
select sum(length(a) - nvl(length(replace(a,'01')),0)/2 as "01",
sum(length(a) - nvl(length(replace(a,'02')),0)/2 as "02",
sum(length(a) - nvl(length(replace(a,'03')),0)/2 as "03",
sum(length(a) - nvl(length(replace(a,'04')),0)/2 as "04"
from t;方法有缺陷:
如果存在01,02,0101,0102这样的记录,会错,
正确的方法应该是按照数据的规则,用“,”分割才行。,估计至少要写个Function。。否则一味追求一条SQL语句,会很复杂难懂。
create table T
(
A VARCHAR2(50)
)
//执行语句
select "a",(
select sum((select sum(decode(substr(a,rownum,2),w."a",1,0))
from dual connect by level <=length(a)
)) as "c" from t) as "q"
from (select distinct substr(a,1,2) as "a" from t) w
where tc1.a=tc2.code
group by tc2.code上面tbl为表名,a为字段名
sum((lengthb(a) - repleace(a,',01,',''))/4) "01",
sum((lengthb(a) - repleace(a,',02,',''))/4) "02",
sum((lengthb(a) - repleace(a,',03,',''))/4) "03",
sum((lengthb(a) - repleace(a,',04,',''))/4) "04"
from
(select ','||replace(l.a,',',',,'||',' a
from table_name l)
union
select '02->'||to_char(count(1)) from t_temp where instr(a,'02')>0
union
select '03->'||to_char(count(1)) from t_temp where instr(a,'03')>0
union
select '04->'||to_char(count(1)) from t_temp where instr(a,'04')>0
union
select '05->'||to_char(count(1)) from t_temp where instr(a,'05')>0
union
select '06->'||to_char(count(1)) from t_temp where instr(a,'06')>0
每行记录号,不能有得复记录
v_strSQL varchar2(3000);
being
v_strSQL='select sum(length(a) - nvl(length(replace(a,''|| v_YourFileds ||'')),0)/2 as mCount,
from t';
execute immediate v_strSQL into v_Count; return v_Count;
end;//****调用上面函数就可以了.