select wzbh, "收" as pzz(凭证字), rksl as rksl, rkje as rkje,null as cksl ,null as ckje ,null as kcsl ,null as kcje
from 表1 where rkbh is not null/*- 你的库结构是这样设计的吧--------*/
union
select wzbh, "支" as pzz(凭证字), null as rksl ,null as rkje ,cksl as cksl, ckje as ckje,null as kcsl ,null as kcje
from 表1 where ckbh is not null/*- 你的库结构是这样设计的吧--------*/
union
select wzbh, "存" as pzz(凭证字), null as rksl ,null as rkje ,null as cksl, null as ckje,kcsl as kcsl ,kcje as kcje
from 表3 /*- 你的库结构是这样设计的吧--------*//*- 这里你会得到所有的这3个表的联合,然后你排序,就可以得到0001、0002、0003。。的记录了--------*/
from 表1 where rkbh is not null/*- 你的库结构是这样设计的吧--------*/
union
select wzbh, "支" as pzz(凭证字), null as rksl ,null as rkje ,cksl as cksl, ckje as ckje,null as kcsl ,null as kcje
from 表1 where ckbh is not null/*- 你的库结构是这样设计的吧--------*/
union
select wzbh, "存" as pzz(凭证字), null as rksl ,null as rkje ,null as cksl, null as ckje,kcsl as kcsl ,kcje as kcje
from 表3 /*- 你的库结构是这样设计的吧--------*//*- 这里你会得到所有的这3个表的联合,然后你排序,就可以得到0001、0002、0003。。的记录了--------*/
解决方案 »
- oracle账户被锁定都有什么原因导致
- 通过函数select到的列,如何做为where语句的条件?
- job等待
- 新问题,大家帮看看这个查询语句怎么写好呢,
- 如何合并数据???
- 这个查询怎么写呢?
- ORACLE的安装问题,对于大家老说应该是小问题,大家指教我呀
- 请问oracle9i中service名和SID的区别
- 怎样在Oracle 8i 中实现一个字段值的自动累加
- 实在是莫名其妙,有请各位指教。
- 高手指教:我用pb的数据窗口将oracle8里的数据倒进oracle9里,没有加条件,但就是少了很多。高手指教
- 向大家求教:我在开发一个人才招聘网站,不知下面的库表结构定义是否合理?哪位高手指点一下!谢谢!!!
(
select wzbh,'收' as type,rksl,rksl,null as cksl,null as ckje,null as kcsl,null as kcje
from table1
union all
select wzbh,'支 as type,null as rksl, null as rksl,cksl,ckje,null as kcsl,null as kcje
from table2
union all
select wzbh,'存'as type,null as rksl, null as rksl,null as cksl,null as ckje,kcsl, kcje
from table3
)
group by wzbh;
group by不行,使用order by
---------- ---------- ---------- ----------
0001 R001 9 54
0002 R002 9 52SQL> select * from tb12;WZBH CKBH CKSL CKJE
---------- ---------- ---------- ----------
0001 c001 4 24
0002 c002 22 222SQL> select * from tb13;WZBH KCSL KCJE
---------- ---------- ----------
0001 5 30
0002 2 30
SQL> select a.wzbh,a.tbb,a.rkbh,a.rksl,a.rkje,a.ckbh,a.cksl,a.ckje,a.kcsl,a.kcje
2 from(
3 select wzbh,'收' tbb,rkbh,rksl,rkje,'' CKBH,'' CKSL,'' CKJE,'' KCSL,'' KCJE from tb11
4 union
5 select wzbh,'支' tbb,'' RKBH,'' RKSL,'' RKJE,ckbh,cksl,ckje,'' KCSL,'' KCJE from tb12
6 union
7 select wzbh,'支' tbb,'' RKBH,'' RKSL,'' RKJE,'' CKBH,'' CKSL,'' CKJE,KCSL,KCJE from tb13
8 ) a order by a.wzbh;
WZBH TB RKBH RKSL RKJE CKBH CKSL CKJE KCSL
---------- -- ---------- ---------- ---------- ---------- ---------- ---------- ----------
KCJE
----------
0001 收 R001 9 54
0001 支 c001 4 24
0001 支 5
300002 收 R002 9 52
0002 支 c002 22 222
0002 支 2
30
已选择6行。
同意楼上的做法!
from table1
union all
select wzbh,'支 as type,null as rksl, null as rksl,cksl,ckje,null as kcsl,null as kcje
from table2
union all
select wzbh,'存'as type,null as rksl, null as rksl,null as cksl,null as ckje,kcsl, kcje
from table3以上在SQL server中通过,结果如下:1 收 54 54 NULL NULL NULL NULL
1 支 NULL NULL 4 24.0000 NULL NULL
1 存 NULL NULL NULL NULL 5 30