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语句实现
解决方案 »
- nvarchar2在oracle10g和oracle9i中的怪异问题
- oracle 服务器无法启动问题。急急急急急@@@
- 数据库出现java.sql.SQLException: ORA-04031: 无法分配 4096 字节的共享内存 ("large pool","unknown object","sort subheap","sort key")
- plsql创建函数错误
- logminer的问题
- oracle客户端已经安装,但在用EXCEL连接不了
- 第一次写Oracle触发器,不太会写,请帮忙啊。
- 一个较为复杂的Update语句
- 請問怎樣實現一個簡單的分組查詢? 謝謝
- 为什么我的数据库连不上
- 为什么下面的两段代码结果会不一样呢?
- 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;//****调用上面函数就可以了.