SELECT 缴款人账号
FROM zh331
GROUP BY 缴款人账号
HAVING COUNT(SUBSTRING(缴款人账号, 2, 5)) >= 2求缴款人账号从第二位开始到第六位的数字重复数大于等于2的,上面的是错的,只是为了跟大家表述清楚,达人帮忙了,在线等,急!
FROM zh331
GROUP BY 缴款人账号
HAVING COUNT(SUBSTRING(缴款人账号, 2, 5)) >= 2求缴款人账号从第二位开始到第六位的数字重复数大于等于2的,上面的是错的,只是为了跟大家表述清楚,达人帮忙了,在线等,急!
解决方案 »
- oracle脚本文件如何执行(不要在SQLPLUS及PLSQL中)
- 我想向一张表循环插入一个数组,比如有字段A/B,A是序列NEXTVAL插入,B是数组的每个元素,序列、数组已知,如何整理这个SQL?
- oracle索引
- pl/sql 游标的问题
- linux下的oracle如何查看错误参考手册
- 能 在数据库中如何随机取数据吗??简单一点的
- powerdesign 连接 oracle数据库失败
- 我下面那些代码,为何编译时出错:“警告: 创建的过程带有编译错误。”
- 好像很简单,但是做起来很烦!
- 有谁装过oracle9i?帮我一下吧!!!
- 利用OCI作一个数据迁移的小工具
- 请问如何写SQL语句查询任何连续7天内工作时间不能超过40小时?
FROM zh331
GROUP BY SUBSTRING(缴款人账号, 2, 5)
HAVING COUNT(*)>= 2有问题再说!
FROM (
SELECT 缴款人账号,
DECODE(TRANSLATE(SUBSTRING(缴款人账号, 2, 5), '0' || SUBSTRING(缴款人账号, 2, 5), '0'), NULL, 0, 0, 0, 1, 0, 1) AS ZERO,
DECODE(TRANSLATE(SUBSTRING(缴款人账号, 2, 5), '1' || SUBSTRING(缴款人账号, 2, 5), '1'), NULL, 0, 0, 0, 1, 0, 1) AS ONE,
DECODE(TRANSLATE(SUBSTRING(缴款人账号, 2, 5), '2' || SUBSTRING(缴款人账号, 2, 5), '2'), NULL, 0, 0, 0, 1, 0, 1) AS TWO,
DECODE(TRANSLATE(SUBSTRING(缴款人账号, 2, 5), '3' || SUBSTRING(缴款人账号, 2, 5), '3'), NULL, 0, 0, 0, 1, 0, 1) AS THREE,
DECODE(TRANSLATE(SUBSTRING(缴款人账号, 2, 5), '4' || SUBSTRING(缴款人账号, 2, 5), '4'), NULL, 0, 0, 0, 1, 0, 1) AS FOUR,
DECODE(TRANSLATE(SUBSTRING(缴款人账号, 2, 5), '5' || SUBSTRING(缴款人账号, 2, 5), '5'), NULL, 0, 0, 0, 1, 0, 1) AS FIVE,
DECODE(TRANSLATE(SUBSTRING(缴款人账号, 2, 5), '6' || SUBSTRING(缴款人账号, 2, 5), '6'), NULL, 0, 0, 0, 1, 0, 1) AS SIX,
DECODE(TRANSLATE(SUBSTRING(缴款人账号, 2, 5), '7' || SUBSTRING(缴款人账号, 2, 5), '7'), NULL, 0, 0, 0, 1, 0, 1) AS SEVEN,
DECODE(TRANSLATE(SUBSTRING(缴款人账号, 2, 5), '8' || SUBSTRING(缴款人账号, 2, 5), '8'), NULL, 0, 0, 0, 1, 0, 1) AS EIGHT,
DECODE(TRANSLATE(SUBSTRING(缴款人账号, 2, 5), '9' || SUBSTRING(缴款人账号, 2, 5), '9'), NULL, 0, 0, 0, 1, 0, 1) AS NINE
FROM zh331
)
WHERE ZERO + ONE + TWO + THREE + FOUR + FIVE + SIX + SEVEN + EIGHT + NINE > 0不知道我猜到你想要的结果了没有
from zh331
where substr(缴款人账号,2,5) =
(select m
from (select a,substr(缴款人账号,2,5) m
from zh331)
group by m
having count(m) >= 2)
已经试过了,应该可以满足楼主的需求
from zh331
where substr(缴款人账号,2,5) in
(select m
from (select a,substr(缴款人账号,2,5) m
from zh331)
group by m
having count(m) >= 2) 又考虑了一下,'=' 应该该作'in', 如上面红色部分
select 缴款人账号 from
(
select 缴款人账号, substr(缴款人账号,2,1) s2,substr(缴款人账号,3,1) s3,substr(缴款人账号,4,1) s4,substr(缴款人账号,5,1) s5,substr(缴款人账号,6,1) s6
from temp1
)
where s2 in (s3,s4,s5,s6) or s3 in (s2,s4,s5,s6) or s4 in (s2,s3,s5,s6) or s5 in (s2,s3,s4,s6)
or s6 in (s2,s3,s4,s5)
缴款人账号从第二位开始到第六位的数字重复数大于等于2的
就是如where 子句中的條件了!
FROM zh331
GROUP BY SUBSTR(缴款人账号, 2, 5)
HAVING COUNT(*)> = 2
from zh331
where substr(缴款人账号,2,5) in
(select m
from (select a,substr(缴款人账号,2,5) m
from zh331)
group by m
having count(m) >= 2)
这个应该是最满足于需求的 1楼的没有回答楼主的意思,他最后想要查出的结果是帐号,而不是SUBSTR后的结果
or instr( substr( id, 4,1), substr( id, 4,3) ) >0
or instr( substr( id, 5,1), substr( id, 6,2) ) >0
or instr( substr( id, 6,1), substr( id, 7,1) ) >0
or instr( substr( id, 4,1), substr( id, 4,3) ) >0
or instr( substr( id, 5,1), substr( id, 6,2) ) >0
or instr( substr( id, 6,1), substr( id, 7,1) ) >0
or instr( substr( id, 4,1), substr( id, 4,3) ) >0
or instr( substr( id, 5,1), substr( id, 6,2) ) >0
or instr( substr( id, 6,1), substr( id, 7,1) ) >0
from zh331
where substr(缴款人账号,2,5) in
(select m
from (select a,substr(缴款人账号,2,5) m
from zh331)
group by m
having count(m) >= 2)
or instr( substr( id, 4,1), substr( id, 4,3) ) >0
or instr( substr( id, 5,1), substr( id, 6,2) ) >0
or instr( substr( id, 6,1), substr( id, 7,1) ) >0