现有一字段
号码
1234
1232356
1234356
855787
977998
333333
3338888
009988776
我想统计每个号码中不重复的字符个数,如
号码 个数
1234 4
1232356 5
应该怎么写pl/sql语句呢?
号码
1234
1232356
1234356
855787
977998
333333
3338888
009988776
我想统计每个号码中不重复的字符个数,如
号码 个数
1234 4
1232356 5
应该怎么写pl/sql语句呢?
--sql T1是存储数据的表名,num是字段名称
SELECT T1.NUM,COUNT(DISTINCT COLUMN_VALUE)
FROM T1,
TABLE(CAST(MULTISET(
SELECT SUBSTR(T1.NUM,LEVEL,1)
FROM DUAL
CONNECT BY LEVEL <= LENGTH(T1.NUM))AS SYS.ODCIVARCHAR2LIST))
GROUP BY T1.NUM;
--下面是测试结果
[SYS@orcl] SQL>WITH t1 AS(
2 SELECT '1234' AS NUM FROM DUAL UNION ALL
3 SELECT '1232356' AS NUM FROM DUAL UNION ALL
4 SELECT '1234356' AS NUM FROM DUAL UNION ALL
5 SELECT '855787' AS NUM FROM DUAL UNION ALL
6 SELECT '977998' AS NUM FROM DUAL UNION ALL
7 SELECT '333333' AS NUM FROM DUAL UNION ALL
8 SELECT '3338888' AS NUM FROM DUAL UNION ALL
9 SELECT '009988776' AS NUM FROM DUAL
10 )SELECT T1.NUM,COUNT(DISTINCT COLUMN_VALUE)
11 FROM T1,
12 TABLE(CAST(MULTISET(
13 SELECT SUBSTR(T1.NUM,LEVEL,1)
14 FROM DUAL
15 CONNECT BY LEVEL <= LENGTH(T1.NUM))AS SYS.ODCIVARCHAR2LIST))
16 GROUP BY T1.NUM;NUM COUNT(DISTINCTCOLUMN_VALUE)
--------- ---------------------------
009988776 5
1232356 5
1234 4
1234356 6
333333 1
3338888 2
855787 3
977998 3
FROM a1,
TABLE(CAST(MULTISET (SELECT SUBSTR(a1.id, LEVEL, 1)
FROM DUAL
CONNECT BY LEVEL <= LENGTH(a1.id)) AS
SYS.ODCIVARCHAR2LIST))
GROUP BY a1.id;
这个我居然看不懂,研究下
不闲麻烦的话直接用10个instr加起来就行了
case when instr(号码,'0') then 1 else 0 end
+case when instr(号码,'1') then 1 else 0 end
+case when instr(号码,'2') then 1 else 0 end
+case when instr(号码,'3') then 1 else 0 end
+case when instr(号码,'4') then 1 else 0 end
+case when instr(号码,'5') then 1 else 0 end
+case when instr(号码,'6') then 1 else 0 end
+case when instr(号码,'7') then 1 else 0 end
+case when instr(号码,'8') then 1 else 0 end
+case when instr(号码,'9') then 1 else 0 end
(select '1234' a from dual union all
select '1232356' a from dual union all
select '1234356' a from dual
)
select a
,count(1)
from t ,
(select rownum-1 rn from dual connect by rownum<=10) b
where instr(a,b.rn)>0
group by a
;
A COUNT(1)
------- ----------
1234356 6
1234 4
1232356 5
with t as
(select '1234' a from dual union all
select '123321' a from dual union all
select '123123' a from dual
)
select a
,count(1)
from t ,
(select rownum-1 rn from dual connect by rownum<=10) b
where instr(a,b.rn)>0
group by a
;
A COUNT(1)
------ ----------
123321 3
123123 3
1234 4
SELECT T1.NUM,COUNT(DISTINCT COLUMN_VALUE)
FROM T1,
--对象类型转成表类型
TABLE(CAST(MULTISET( --CAST(MULTISET --将拆开的字符转成对象类型
SELECT SUBSTR(T1.NUM,LEVEL,1) --将T1.num 每一个字符拆开
FROM DUAL
CONNECT BY LEVEL <= LENGTH(T1.NUM))AS SYS.ODCIVARCHAR2LIST))
GROUP BY T1.NUM;
,count(1)
from duan_normal ,
(select rownum-1 rn from dual connect by rownum<=10) b
where instr(duan_send,b.rn)>0
group by duan_send
order by 2 desc;
输出结果::(明显有问题,手机号码的长度最多也是11位)
duan_send count(1)
13360820189 12600
13360829985 11816
13360829097 10176
18927279004 8988
18927277921 8405
13376788852 8204
13360828167 7770
18929050382 7658
13380806128 7644
18927279014 6524
,count(1)
from duan_normal ,
(select rownum-1 rn from dual connect by rownum<=10) b
where instr(duan_send,b.rn)>0
group by duan_send
order by 2 desc;
输出结果::(明显有问题,手机号码的长度最多也是11位)
duan_send count(1)
13360820189 12600
13360829985 11816
13360829097 10176
18927279004 8988
18927277921 8405
13376788852 8204
13360828167 7770
18929050382 7658
13380806128 7644
18927279014 6524
这就怪了,子查询(select rownum-1 rn from dual connect by rownum<=10)结果就10行
用instr(duan_send,b.rn)>0关联最多每个号码对应10行
居然能group by出这么多不要用子查询,直接间隔临时表再关联试试
是不是oracle版本的问题
--问题在于他的表里面存在重复的记录,你的写法对不重复记录是没有问题的。
--看看下面的结果 CNT 为不重复的字符个数, CNT2 为实际计算值于CNT的商,都是被整除的,
----说明数据库里面存在很多重复的记录,改一下就没问题了
------(但是你的写法只适用于全是数字的情况,如果换成既有字符串又有数字的话就没办法了)
SELECT DUAN_SEND, COUNT(DISTINCT RN)
FROM DUAN_NORMAL,
(SELECT ROWNUM - 1 RN FROM DUAL CONNECT BY ROWNUM <= 10) B
WHERE INSTR(DUAN_SEND, B.RN) > 0
GROUP BY DUAN_SEND
ORDER BY 2 DESC;
[SYS@orcl] SQL>with t as(
2 select '13360820189' a ,12600 b from dual union all
3 select '13360829985' a ,11816 b from dual union all
4 select '13360829097' a ,10176 b from dual union all
5 select '18927279004' a ,8988 b from dual union all
6 select '18927277921' a ,8405 b from dual union all
7 select '13376788852' a ,8204 b from dual union all
8 select '13360828167' a ,7770 b from dual union all
9 select '18929050382' a ,7658 b from dual union all
10 select '13380806128' a ,7644 b from dual union all
11 select '18927279014' a ,6524 b from dual)
12 select DISTINCT a,b,count(1)over(PARTITION BY a) cnt,b/count(1)over(PARTITION BY a) cnt2
13 from t ,
14 (select rownum-1 rn from dual connect by rownum<=10) b
15 where instr(a,b.rn)>0;A B CNT CNT2
----------- ---------- ---------- ----------
18929050382 7658 7 1094
13360829097 10176 8 1272
13360829985 11816 8 1477
13360828167 7770 7 1110
13376788852 8204 7 1172
18927277921 8405 5 1681
18927279004 8988 7 1284
18927279014 6524 7 932
13360820189 12600 7 1800
13380806128 7644 6 1274[code]
--问题在于他的表里面存在重复的记录,你的写法对不重复记录是没有问题的。
--看看下面的结果 CNT 为不重复的字符个数, CNT2 为实际计算值于CNT的商,都是被整除的,
----说明数据库里面存在很多重复的记录,改一下就没问题了
------(但是你的写法只适用于全是数字的情况,如果换成既有字符串又有数字的话就没办法了)
SELECT DUAN_SEND, COUNT(DISTINCT RN)
FROM DUAN_NORMAL,
(SELECT ROWNUM - 1 RN FROM DUAL CONNECT BY ROWNUM <= 10) B
WHERE INSTR(DUAN_SEND, B.RN) > 0
GROUP BY DUAN_SEND
ORDER BY 2 DESC;
[SYS@orcl] SQL>with t as(
2 select '13360820189' a ,12600 b from dual union all
3 select '13360829985' a ,11816 b from dual union all
4 select '13360829097' a ,10176 b from dual union all
5 select '18927279004' a ,8988 b from dual union all
6 select '18927277921' a ,8405 b from dual union all
7 select '13376788852' a ,8204 b from dual union all
8 select '13360828167' a ,7770 b from dual union all
9 select '18929050382' a ,7658 b from dual union all
10 select '13380806128' a ,7644 b from dual union all
11 select '18927279014' a ,6524 b from dual)
12 select DISTINCT a,b,count(1)over(PARTITION BY a) cnt,b/count(1)over(PARTITION BY a) cnt2
13 from t ,
14 (select rownum-1 rn from dual connect by rownum<=10) b
15 where instr(a,b.rn)>0;A B CNT CNT2
----------- ---------- ---------- ----------
18929050382 7658 7 1094
13360829097 10176 8 1272
13360829985 11816 8 1477
13360828167 7770 7 1110
13376788852 8204 7 1172
18927277921 8405 5 1681
18927279004 8988 7 1284
18927279014 6524 7 932
13360820189 12600 7 1800
13380806128 7644 6 1274