后面的time字段结果是几都不所谓,不是1也行, 张 3 王 2 现在我想要得到的结果是time是几就会查询出几条 name time 张 3 张 3 张 3 王 2 王 2 这样可能会简单点,请教高手来帮帮忙啊
select name, time from (SELECT distinct name, time, level - 1 FROM tb t1 CONNECT BY level <= time)
with temp as( select '张' name,3 times from dual union all select '王' name,2 times from dual ) select name, 1 times from (SELECT distinct name, times, level - 1 FROM temp CONNECT BY level <= times)
刚刚那个SQL的效率的确不好,试下这个 select a.name, a.time from tb a, (SELECT level l from (select max(time) maxtime from tb) CONNECT BY level <= maxtime) b where a.time >= b.l order by a.name
张 3
王 2
现在我想要得到的结果是time是几就会查询出几条
name time
张 3
张 3
张 3
王 2
王 2
这样可能会简单点,请教高手来帮帮忙啊
from (SELECT distinct name, time, level - 1
FROM tb t1
CONNECT BY level <= time)
select '张' name,3 times from dual
union all
select '王' name,2 times from dual
)
select name, 1 times
from (SELECT distinct name, times, level - 1
FROM temp
CONNECT BY level <= times)
select a.name, a.time
from tb a,
(SELECT level l
from (select max(time) maxtime from tb)
CONNECT BY level <= maxtime) b
where a.time >= b.l
order by a.name