假设数据库中表A中“卡号”字段,普通查询:select 卡号 from A 得出结果如下:
8986010860510012102
8986010860510012106
8986010860510012107
8986010860510012108
8986010860510027968
8986010860510027969
8986010860510027970
8986010860511027971
8986010860510027969
8986010860510027970
8986010860511027972
8986010860510027973
希望得出如下结果
起始卡号 结尾卡号 数量
8986010860510012102 8986010860510012102 1
8986010860510012106 8986010860510012108 3
8986010860510027968 8986010860510027973 8
请教各位高手,该如何写啊?
8986010860510012102
8986010860510012106
8986010860510012107
8986010860510012108
8986010860510027968
8986010860510027969
8986010860510027970
8986010860511027971
8986010860510027969
8986010860510027970
8986010860511027972
8986010860510027973
希望得出如下结果
起始卡号 结尾卡号 数量
8986010860510012102 8986010860510012102 1
8986010860510012106 8986010860510012108 3
8986010860510027968 8986010860510027973 8
请教各位高手,该如何写啊?
解决方案 »
- 如何计算某值在表中出现几次?
- 菜鸟急问ORACLE里FUNCTION返回ref cursor的问题
- 日志问题~
- 如何一次提取前三名和最后三名
- ODP.NET for oracle 参数化 修改数据
- 求教一条sql语句的写法
- win2003下装9I,装好后,自己建库前要做那些工作?那里有这方面的资料啊
- 为了在asp中调用我们局的oracle数据库,我在机器上装了oracle8,建了个服务名,将数据库字符集改成 amarica... ,一切正常,我又建立了一
- oracle连接出现问题,请各位帮忙
- powerdesigner用reort导出文档,列显示为true/false,如何设置true的显示为“X”
- 请问这个插入语句的“标识符”无效哪里有问题。。。
- oracle9.2恢复出错
select '106' as card from dual union
select '107' as card from dual union
select '108' as card from dual union
select '968' as card from dual union
select '969' as card from dual union
select '970' as card from dual union
select '971' as card from dual )
select t2.c1, t1.c1, (t1.c1 - t2.c1 + 1) as 数量
from (select c1, c2, rownum xh
from (select distinct t.c1, t.c2
from (select distinct card c1, card + 1 as c2 from tab) t
start with 1 = 1
connect by prior t.c2 = t.c1
order by t.c1)
where c1 in (select card from tab)
and c2 not in (select card from tab)) t1,
(select c1, c2, rownum xh
from (select distinct t.c1, t.c2
from (select distinct card c1, card + 1 as c2 from tab) t
start with 1 = 1
connect by prior t.c2 = t.c1
order by t.c1)
where c1 in (select card from tab)
and c1 - 1 not in (select card from tab)) t2
where t1.xh = t2.xh
说实话,我也有点绕晕了,但总算出来了,楼主先试试效果
可以的话,我再解释