是这样的,从A,B两张表里取数据,表的大概结构是:
table A:id(主键) YUXIANG(预想次数)
1 3
table B:id(主键1) SHIJI(主键2)--实际次数
1 1
1 2
现在我要按id取两表的数据,要求显示结果如下:
id YUXIANG SHIJI
1 1/3 1
1 2/3 2
1 3/3
我想只用sql文来实现这个结果,然后输出,这个问题困扰小弟许久,求高手支招!!!
table A:id(主键) YUXIANG(预想次数)
1 3
table B:id(主键1) SHIJI(主键2)--实际次数
1 1
1 2
现在我要按id取两表的数据,要求显示结果如下:
id YUXIANG SHIJI
1 1/3 1
1 2/3 2
1 3/3
我想只用sql文来实现这个结果,然后输出,这个问题困扰小弟许久,求高手支招!!!
union
select a.id , to_char(b.yuxiang)||'/'||to_char(a.YUXIANG) , nvl from a , b where a.id = b.shiji
table A:id(主键) YUXIANG(预想次数)
1 3
2 3
3 0
4 5
table B:id(主键1) SHIJI(主键2)--实际次数
1 1
1 2
4 1
id YUXIANG SHIJI
1 1/3 1
1 2/3 2
1 3/3
2 1/3
2 2/3
2 3/3
4 1/4 1
4 2/4
... ...
id YUXIANG SHIJI
1 1/3 1
1 2/3 2
1 3/3
最后一条不能显示出来啊
select 1 id, 3 YUXIANG from dual
), table_b as (
select 1 id, 1 shiji from dual union all
select 1, 2 from dual
)
SELECT a.id, a.lv||'/'||a.YUXIANG, b.shiji
FROM ( SELECT a.id, a.YUXIANG, LEVEL lv FROM table_a a CONNECT BY LEVEL <= a.YUXIANG ) a
left JOIN table_b b on a.id = b.id AND a.lv = b.shiji
order by b.id
union all
select b.id,a.yuxiang||'/'||a.yuxiang yuxiang,null shiji from a,b where a.id=b.id
with a as
(select 1 id,3 yuxiang from dual
union all
select 2,3 from dual
union all
select 4,5 from dual
)
,b as
(select 1 id,1 shiji from dual
union all
select 1,2 from dual
union all
select 4,1 from dual
)
select tb.id,tb.rn||'/'||tb.yuxiang,tb.yuxiang,b.shiji
from b
,(select a.id,a.yuxiang,ta.rn
from a
,(select rownum rn from dual connect by rownum <= (select max(yuxiang) from a)) ta
where a.yuxiang >= ta.rn
) tb
where b.id(+) = tb.id and b.shiji(+) = tb.rn
order by 1,2