--随机取(参数id可变) with test as( select '01' id, 2 layer from dual union all select '02' id, 2 layer from dual union all select '03' id, 3 layer from dual) SELECT * FROM test WHERE rownum < (SELECT layer FROM test WHERE id = '01') + 1;--按升序取(ID参数可变) with test as( select '01' id, 2 layer from dual union all select '02' id, 2 layer from dual union all select '03' id, 3 layer from dual) SELECT * FROM (SELECT * FROM test ORDER BY id) WHERE rownum < (SELECT layer FROM test WHERE id = '01') + 1;
看看这个是不是你要的 with id_layer as( select '01' id, 2 layer from dual union all select '02' id, 2 layer from dual union all select '03' id, 3 layer from dual) select t1.id||t3.rn from id_layer t1, (select rownum rn from dual connect by rownum<=(select max(t2.layer) from id_layer t2))t3 where t3.rn<=t1.layer
select id,layer from (select id,layer,rownum rn from table) a where a.rn<=(select layer from table where id='01')
那就是 with id_layer as( select '01' id, 2 layer from dual union all select '02' id, 2 layer from dual union all select '03' id, 3 layer from dual) select t1.id||t3.rn from id_layer t1, (select rownum rn from dual connect by rownum<=(select max(t2.layer) from id_layer t2))t3 where t3.rn<=t1.layer 你试的时候 with id_layer as( select '01' id, 2 layer from dual union all select '02' id, 2 layer from dual union all select '03' id, 3 layer from dual) 这段不要,然后把id_layer换成你的表名就可以了
011
012 这样的
with test as(
select '01' id, 2 layer from dual union all
select '02' id, 2 layer from dual union all
select '03' id, 3 layer from dual)
SELECT * FROM test WHERE rownum < (SELECT layer FROM test WHERE id = '01') + 1;--按升序取(ID参数可变)
with test as(
select '01' id, 2 layer from dual union all
select '02' id, 2 layer from dual union all
select '03' id, 3 layer from dual)
SELECT *
FROM (SELECT * FROM test ORDER BY id)
WHERE rownum < (SELECT layer FROM test WHERE id = '01') + 1;
select '01' id, 2 layer from dual union all
select '02' id, 2 layer from dual union all
select '03' id, 3 layer from dual)
select t1.id||t3.rn
from id_layer t1,
(select rownum rn from dual connect by rownum<=(select max(t2.layer) from id_layer t2))t3
where t3.rn<=t1.layer
select id,layer from (select id,layer,rownum rn from table) a
where a.rn<=(select layer from table where id='01')
返回结果中
是 对应记录的ID + 数值 这个数值时1到layer值之间的值
select '01' id, 2 layer from dual union all
select '02' id, 2 layer from dual union all
select '03' id, 3 layer from dual)
select t1.id||t3.rn
from id_layer t1,
(select rownum rn from dual connect by rownum<=(select max(t2.layer) from id_layer t2))t3
where t3.rn<=t1.layer 你试的时候
with id_layer as(
select '01' id, 2 layer from dual union all
select '02' id, 2 layer from dual union all
select '03' id, 3 layer from dual)
这段不要,然后把id_layer换成你的表名就可以了