ORACLE中类似的视图如何来建?

Question

表1放的是具体的版本号,表2放的是各版本的具体记录及修订后的记录,它们之间通过单据号进行关联,现在想通过视图来查询出各版本的数量来,有没有办法实现呢?具体表结构及记录如下:
表1 tab1
   单据号  版本号   是否最新版本 
   DJH01   01           Y             
   DJH02   01           N        
   DJH02   02           N   
   DJH02   03           Y             
表2 tab2
   单据号 系统号 行号  存货号  数量   备注  修订行号
   DJH2    01    1     0101   20               04
   DJH2    02    2     0202   30               05    
   DJH2    03    3     0201   50
   DJH2    04    1     0101   50               07     
   DJH2    05    2     0202   85               08
   DJH2    06    4     0208   40         
   DJH2    07    1     0101   99
   DJH2    08    2     0202   105  需显示的视图格式
  单据号   行号   存货号   数量(版本1)  数量(版本2)  数量(版本3)  备注(版本1) 备注(版本2) 备注(版本3)

Answer 1

单据号 行号 存货号 数量(版本1) 数量(版本2) 数量(版本3) 备注(1) 备注(2) 备注(3)
DJH2    1   0101     20           50          99           
DJH2    2   0202     30           85          105
DJH2    3   0201     50
DJH2    4   0208                  50

Answer 2

create table tab1(BillID varchar2(10), NUM varchar2(10),   IsNew varchar2(1));  insert into tab1(BillID , NUM ,   IsNew ) values('DJH1','01','Y');             
  insert into tab1(BillID , NUM ,   IsNew ) values('DJH2','01','N');       
  insert into tab1(BillID , NUM ,   IsNew ) values('DJH2','02','N');   
  insert into tab1(BillID , NUM ,   IsNew ) values('DJH2','03','Y');             create table  tab2(BillID  varchar2(10),
                   sysID   varchar2(10),
                   goodsID varchar2(10),
                   LineID  varchar2(10),
                   numbers number,
                   re  varchar(10),
                   editlineid varchar2(10));   insert into tab2(BillID,SysID,LineID,GoodsID,Numbers,Re,EditLineID) values('DJH2','01','1','0101',20,'测试1','04');
   insert into tab2(BillID,SysID,LineID,GoodsID,Numbers,Re,EditLineID) values('DJH2','02','2','0202',30,'测试2','05') ;  
   insert into tab2(BillID,SysID,LineID,GoodsID,Numbers,Re,EditLineID) values('DJH2','03','3','0201',50,'测试3','');
   insert into tab2(BillID,SysID,LineID,GoodsID,Numbers,Re,EditLineID) values('DJH2','04','1','0101',50,'测试4','07') ;
   insert into tab2(BillID,SysID,LineID,GoodsID,Numbers,Re,EditLineID) values('DJH2','05','2','0202',85,'测试5','08');
   insert into tab2(BillID,SysID,LineID,GoodsID,Numbers,Re,EditLineID) values('DJH2','06','4','0208',40,'测试6','')  ;      
   insert into tab2(BillID,SysID,LineID,GoodsID,Numbers,Re,EditLineID) values('DJH2','07','1','0101',99,'测试7','');
   insert into tab2(BillID,SysID,LineID,GoodsID,Numbers,Re,EditLineID) values('DJH2','08','2','0202',105,'测试8','') ; 
select y.BillID,y.LineID,y.GoodsID,sum(y.Numbers1),sum(y.Numbers2),sum(y.Numbers3),y.Re1,y.Re2,y.Re3
           from (
                     select x.BillID  BillID ,x.LineID  LineID ,x.GoodsID GoodsID,
                            case when x.NUM='01' then x.Numbers else 0 end   Numbers1,
                            case when x.NUM='02' then x.Numbers else 0 end   Numbers2,
                            case when x.NUM='03' then x.Numbers else 0 end   Numbers3,
                            case when x.NUM='01' then x.Re else NULL end  Re1,
                            case when x.NUM='02' then x.Re else NULL end  Re2,
                            case when x.NUM='03' then x.Re else NULL end  Re3
                            from
                     (   select a.BillID ,a.NUM ,a.IsNew ,
                                b.SysID,b.GoodsID,b.LineID,b.Numbers,b.Re,b.EditLineID
                          from tab1 a,tab2 b
                         where b.BillID=a.BillID) x
                         
                 ) y
           group by  y.BillID,y.LineID,y.GoodsID,y.Re1,y.Re2,y.Re3   1 DJH2 1 0101 0 0 20 测试1
2 DJH2 1 0101 0 0 50 测试4
3 DJH2 1 0101 0 0 99 测试7
4 DJH2 1 0101 0 20 0 测试1
5 DJH2 1 0101 0 50 0 测试4
6 DJH2 1 0101 0 99 0 测试7
7 DJH2 1 0101 20 0 0 测试1
8 DJH2 1 0101 50 0 0 测试4
9 DJH2 1 0101 99 0 0 测试7
10 DJH2 2 0202 0 0 30 测试2
11 DJH2 2 0202 0 0 85 测试5
12 DJH2 2 0202 0 0 105 测试8
13 DJH2 2 0202 0 30 0 测试2
14 DJH2 2 0202 0 85 0 测试5
15 DJH2 2 0202 0 105 0 测试8
16 DJH2 2 0202 30 0 0 测试2
17 DJH2 2 0202 85 0 0 测试5
18 DJH2 2 0202 105 0 0 测试8
19 DJH2 3 0201 0 0 50 测试3
20 DJH2 3 0201 0 50 0 测试3
21 DJH2 3 0201 50 0 0 测试3

Answer 3

Big_Gun,谢谢你的回复,可是还存在的问题是,这个版本号是变动的,有可能变动5 6 7 8次

Answer 4

表1 tab1
   单据号  版本号   是否最新版本 
   DJH01   01           Y             
   DJH02   01           N        
   DJH02   02           N   
   DJH02   03           Y             
表2 tab2
   单据号 系统号 行号  存货号  数量   备注  修订行号
   DJH2    01    1     0101   20               04
   DJH2    02    2     0202   30               05    
   DJH2    03    3     0201   50
   DJH2    04    1     0101   50               07     
   DJH2    05    2     0202   85               08
   DJH2    06    4     0208   40         
   DJH2    07    1     0101   99
   DJH2    08    2     0202   105  这两个表的单据号怎么不一致？

Answer 5

实现步骤:
1.建立分别建立如下视图:
view1:单据号   行号   存货号   数量(版本1) 备注(版本1)
view2:单据号   行号   存货号   数量(版本2) 备注(版本2)
view3:单据号   行号   存货号   数量(版本3) 备注(版本3)
...
然后用LAG/LEAD函数拼成你要的视图,如果版本总数是变动的,就需要构建动态语句来实现

Answer 6

你这个不太好搞，说到底tab1是纵表，生成的数据其实对应它的是横表，要么把tab1弄成横表，再加个表来表示最新的版本，或者默认数值较大的为最新版本。呵呵，但是你说了，可能变动5 6 7 8 次，就是说这个次数是不固定的，要变成横表不太可能不限制列数吧。呵呵，所以说还是用程序吧，在程序中转置矩阵还是很容易的。

Answer 7

再有就是考虑用游表来建立临时表，每当以上基础表有变动了就从出，然后根据这个再出视图，再有就是不要重新出所有的数据，最好是判断哪条是新增的，再update或者怎么弄，配合触发器吧，呵呵，但是......要实现这些够你喝一壶的了，以上疯言疯语仅供参考。

Answer 8

是呀,这个真不好调,首先建了个视图,将这两个表连接成一个表baobiao,再对这两表进行操作
具体过程如下:
declare col1 baobiao.nversion%type;        
sqlstr varchar2(8000);
cursor c_try is select distinct nversion from baobiao;  --版本信息
begin
    sqlstr:='';
    open c_try;
    loop
    fetch c_try into col1;
    exit when c_try%notfound;
    sqlstr:=sqlstr||', sum(decode(nversion,'''||col1||''',nordernum,0.0)) as '||col1;
    exit when c_try%notfound;
    end loop;
    close c_try;
    sqlstr:='create table proje as select cmangid,sum(nordernum) as 数量'||sqlstr||' from a group by cmangid';---显示相应存货的各版本的数量
    execute immediate sqlstr;
end;
   /
  
--再查询
select * from proje;错误信息:
未找到预期的from关键字;
可表里确实有相应的数据;

Answer 9

對於你這個問題，就是一種縱表轉橫表的問題，我在實際工作中碰到過，並在SQL Server中用存儲過程實現了，其根本思想就是執行動態SQL語句來實現。不過由於在oracle中無法通過存儲過程直接執行SQL語句（無論是靜態SQL語句還是動態SQL語句）來返回記錄集，而隻能通過遊標（或集合類型變量）來返回記錄集，所以在oracle中要實現你想要的這個報表確實有點麻煩，但好在oracle提供了一種能執行動態SQL來返回遊標的功能，所以我的建議是建一個自定義函數或存過來返回一個遊標，我給出的參考代碼如下：
create or replace procude testProc(cur out sys_refcursor)
is
   cursor cur1 is select distinct 單據號,行號,存貨號 from tab2;
   str varchar2(8000):='select 單據號,行號,存貨號';
   i number:=1;
begin
   for c in cur1 loop
       str:=str||','||'sum(decode(行號||版本號,'''||c.行號||c.版本號||''',c.數量,null)) as 數量(版本'||i||')';
       i:=i+1;
   end loop;
   i:=1;
   for c in cur1 loop
       str:=str||','||'min(decode(行號||存貨號,'''||c.行號|||c.存貨號||''',c.備注,null)) as 備注(版本'||i||')';
       i:=i+1;
   end loop;
   open cur for str||' from tab2 group by 單據號,行號,存貨號';
end;
以上即是我把SQL Server中實現的思想轉到Oracle中的代碼，不過我未經過驗証，不保証代碼沒有語法錯誤或其它錯誤，至於如何調用上述存過並在前端程序中(如asp.net中)顯示報表數據我就不多說了。
另外，將我上述代碼稍改一下，每次調用時動態創建一個表或視圖也可以(這樣好象不太好，因為每次調用存過時就創建一個表或視圖，下次調用時就有可能發生表或視圖已存在的錯誤，並且DDL語句要消耗不少系統資源，而且還要申請數據空間),就象樓主在上面做的那樣。再次聲明：我給的代碼是根據樓主給的數據與要求憑感覺寫出來的，未經過測試，可能存在一些錯誤！！！！！