ITEM COUNT
---- -------
A001 3;
A002 2;
A003 0;上面是表的2个字段,怎样通过查询语句得到下面的结果ITEM XH
---- --
A001 1;
A001 2;
A001 3;
A002 1;
A002 2;
解决方案 »
- Oracle行合并成列
- 求一个oracle索引优化的建立索引的方法
- oraacle 行转列
- ★★★能否获取当前表空间中每一张表的最后更新时间★★★
- 我现在就需要一个数组存放几个数据,在之后的程序中会需要用sql语句来判断某个字段的值是否在几个数据中,怎么实现简单?
- java存储过程问题
- 如何将EXCEL中的某几列导入到数据库中某表中呢?
- 请教大家:我把12机器上数据库的一个大表数据(1000万),怎么快速倒到15机器的数据库里去,不用数据连接(dataconnection)倒!
- 用PL/SQL来读取Datafile,然后生成表。请各位高手提供思路。急!!(100分)
- oracle 哪本书比较好啊
- 怎么成为oracle DBA?
- Oracle导出某个用户的所有对象时 参数owner必须写吗,不写后果严重么
--SQL:
with temp
as
(
select 'A001' a,3 b from dual
union all
select 'A002',2 from dual
union all
select 'A003',0 from dual
)
select a,rn from temp,
(select rownum rn from dual connect by rownum<4) a
where b>0 and rn<=b
order by a--result:
A001 1
A001 2
A001 3
A002 1
A002 2
看了一下,确实可以。
不过COUNT的值是不确定的。能改善一下不
select max(b) into l_b FROM tablename
--传值进来
select rownum from dual connect by rownum<'"& l_b &"'
as
(
select item,count from page
)
select item,rn from temp,
(select rownum rn from page connect by rownum<=count)
where count>0 and rn <= count;
with t as
(select '01' code,'1' id,'name1' name from dual union
select '0101' code,'2' id,'name2' name from dual union
select '0102' code,'3' id,'name3' name from dual union
select '010203' code,'4' id,'name4' name from dual union
select '02' code,'5' id,'name5' name from dual union
select '0201' code,'6' id,'name6' name from dual union
select '0202' code,'7' id,'name7' name from dual)
select *
from t
start with code = '010203'
connect by code = substr(prior code, 1, length(prior code) - 2);