数据库是从服务器上(AIX+Oracle 10G)备份下来的,然后恢复到我本机(XP Oracle10G)上,
我建了个视图,视图原来没有,代码如下:
create or replace view cashflow as
select nov,
periodv,
detailindex,
subjname,
explanation,
localcreditamount,
localdebitamount,
pk_cashflow,
money,
prepareddatev,
unitname,
ltrim(max(sys_connect_by_path(valuename, ';')), ';') as valuename
from ( select nov,
pk_cashflow,
periodv,
detailindex,
subjname,
explanation,
localcreditamount,
localdebitamount,
money,
prepareddatev,
unitname,
valuename,
rnFirst,
lead(rnFirst) over(partition by pk_cashflow, nov, periodv, detailindex, subjname, explanation, localcreditamount, localdebitamount, money, prepareddatev, unitname order by rnFirst) rnNext
from (select bd_cashflow.cfitemname as pk_cashflow,
bd_corp.unitname as unitname,
gl_detail.nov as nov,
gl_detail.yearv || gl_detail.periodv as periodv,
gl_detail.detailindex as detailindex,
bd_accsubj.subjname as subjname,
gl_detail.explanation as explanation,
gl_detail.localcreditamount as localcreditamount,
gl_detail.localdebitamount as localdebitamount,
gl_detail.prepareddatev as prepareddatev,
gl_cashflowcase.money as money,
gl_freevalue.valuename as valuename,
gl_detail.excrate2 as excrate2,
row_number() over(order by bd_cashflow.cfitemname, nov, periodv, detailindex, subjname, explanation, localcreditamount, localdebitamount, money, prepareddatev, unitname ) rnFirst
from gl_cashflowcase
inner join bd_cashflow
on gl_cashflowcase.pk_cashflow = bd_cashflow.pk_cashflow
inner join gl_detail
on gl_cashflowcase.pk_detail = gl_detail.pk_detail
inner join bd_accsubj
on gl_detail.pk_accsubj = bd_accsubj.pk_accsubj
inner join bd_corp
on gl_detail.pk_corp = bd_corp.pk_corp
inner join gl_freevalue
on gl_detail.assid = gl_freevalue.freevalueid) temp1) temp2
start with rnNext is null
connect by rnNext = prior rnFirst
group by nov,
periodv,
detailindex,
subjname,
pk_cashflow,
explanation,
money,
localcreditamount,
localdebitamount,
prepareddatev,
unitname
union
select nov,
periodv,
detailindex,
subjname,
explanation,
localcreditamount,
localdebitamount,
pk_cashflow,
money,
prepareddatev,
unitname,
valuename
from (select bd_cashflow.cfitemname as pk_cashflow,
bd_corp.unitname as unitname,
gl_detail.nov as nov,
gl_detail.yearv || gl_detail.periodv as periodv,
gl_detail.detailindex as detailindex,
bd_accsubj.subjname as subjname,
gl_detail.explanation as explanation,
gl_detail.localcreditamount as localcreditamount,
gl_detail.localdebitamount as localdebitamount,
gl_detail.prepareddatev as prepareddatev,
gl_cashflowcase.money as money,
gl_detail.assid as assid
from gl_cashflowcase
inner join bd_cashflow
on gl_cashflowcase.pk_cashflow = bd_cashflow.pk_cashflow
inner join gl_detail
on gl_cashflowcase.pk_detail = gl_detail.pk_detail
inner join bd_accsubj
on gl_detail.pk_accsubj = bd_accsubj.pk_accsubj
inner join bd_corp
on gl_detail.pk_corp = bd_corp.pk_corp) x
left join gl_freevalue y
on x.assid = y.freevalueid
where y.valuename is null;测试查询结果没有问题和预期相同,但是把视图放到服务器上出现了问题,查询结果竟然和我本机不一样,最后一列(Valuename)合并有错误,恢复到同事的机器上也没有问题,这是为什么?
周末自己把操作系统换成了Win7,安装了oracle 10G,恢复数据库,创建视图,结果查询结果竟然也出现了和服务器上相同的情况,每次查询结果还不一样,请大侠们多多指教!
我建了个视图,视图原来没有,代码如下:
create or replace view cashflow as
select nov,
periodv,
detailindex,
subjname,
explanation,
localcreditamount,
localdebitamount,
pk_cashflow,
money,
prepareddatev,
unitname,
ltrim(max(sys_connect_by_path(valuename, ';')), ';') as valuename
from ( select nov,
pk_cashflow,
periodv,
detailindex,
subjname,
explanation,
localcreditamount,
localdebitamount,
money,
prepareddatev,
unitname,
valuename,
rnFirst,
lead(rnFirst) over(partition by pk_cashflow, nov, periodv, detailindex, subjname, explanation, localcreditamount, localdebitamount, money, prepareddatev, unitname order by rnFirst) rnNext
from (select bd_cashflow.cfitemname as pk_cashflow,
bd_corp.unitname as unitname,
gl_detail.nov as nov,
gl_detail.yearv || gl_detail.periodv as periodv,
gl_detail.detailindex as detailindex,
bd_accsubj.subjname as subjname,
gl_detail.explanation as explanation,
gl_detail.localcreditamount as localcreditamount,
gl_detail.localdebitamount as localdebitamount,
gl_detail.prepareddatev as prepareddatev,
gl_cashflowcase.money as money,
gl_freevalue.valuename as valuename,
gl_detail.excrate2 as excrate2,
row_number() over(order by bd_cashflow.cfitemname, nov, periodv, detailindex, subjname, explanation, localcreditamount, localdebitamount, money, prepareddatev, unitname ) rnFirst
from gl_cashflowcase
inner join bd_cashflow
on gl_cashflowcase.pk_cashflow = bd_cashflow.pk_cashflow
inner join gl_detail
on gl_cashflowcase.pk_detail = gl_detail.pk_detail
inner join bd_accsubj
on gl_detail.pk_accsubj = bd_accsubj.pk_accsubj
inner join bd_corp
on gl_detail.pk_corp = bd_corp.pk_corp
inner join gl_freevalue
on gl_detail.assid = gl_freevalue.freevalueid) temp1) temp2
start with rnNext is null
connect by rnNext = prior rnFirst
group by nov,
periodv,
detailindex,
subjname,
pk_cashflow,
explanation,
money,
localcreditamount,
localdebitamount,
prepareddatev,
unitname
union
select nov,
periodv,
detailindex,
subjname,
explanation,
localcreditamount,
localdebitamount,
pk_cashflow,
money,
prepareddatev,
unitname,
valuename
from (select bd_cashflow.cfitemname as pk_cashflow,
bd_corp.unitname as unitname,
gl_detail.nov as nov,
gl_detail.yearv || gl_detail.periodv as periodv,
gl_detail.detailindex as detailindex,
bd_accsubj.subjname as subjname,
gl_detail.explanation as explanation,
gl_detail.localcreditamount as localcreditamount,
gl_detail.localdebitamount as localdebitamount,
gl_detail.prepareddatev as prepareddatev,
gl_cashflowcase.money as money,
gl_detail.assid as assid
from gl_cashflowcase
inner join bd_cashflow
on gl_cashflowcase.pk_cashflow = bd_cashflow.pk_cashflow
inner join gl_detail
on gl_cashflowcase.pk_detail = gl_detail.pk_detail
inner join bd_accsubj
on gl_detail.pk_accsubj = bd_accsubj.pk_accsubj
inner join bd_corp
on gl_detail.pk_corp = bd_corp.pk_corp) x
left join gl_freevalue y
on x.assid = y.freevalueid
where y.valuename is null;测试查询结果没有问题和预期相同,但是把视图放到服务器上出现了问题,查询结果竟然和我本机不一样,最后一列(Valuename)合并有错误,恢复到同事的机器上也没有问题,这是为什么?
周末自己把操作系统换成了Win7,安装了oracle 10G,恢复数据库,创建视图,结果查询结果竟然也出现了和服务器上相同的情况,每次查询结果还不一样,请大侠们多多指教!
create table Test(
Name varchar2(100),
age number(2,0),
sex varchar2(2),
Num number(2,0)
);delete from Test ;insert into Test Values ('aa',12,'男',1);
insert into Test Values ('bb',13,'女',2);
insert into Test Values ('cc',14,'男',2);
insert into Test Values ('dd',15,'女',3);
insert into Test Values ('ee',16,'男',4);
insert into Test Values ('ff',17,'女',4);
insert into Test Values ('gg',18,'男',5);
insert into Test Values ('hh',19,'女',7);
insert into Test Values ('ii',20,'男',7);
insert into Test Values ('jj',21,'女',9);
insert into Test Values ('k',22,'男',10);select NUM,
ltrim(max(sys_connect_by_path(age, ';')), ';') as age,
ltrim(max(sys_connect_by_path(Name, ';')), ';') as Name,
ltrim(max(sys_connect_by_path(sex, ';')), ';') as sex
from (select NUM,
sex,
Name,
age,
rnFirst,
lead(rnFirst) over(partition by num order by rnFirst) rnNext
from (select a.*,row_number() over(order by a.num) rnFirst
from Test a ) test1) test2
start with rnNext is null
connect by prior rnFirst =rnNext
group by NUM;最后果不其然在10.2.0.1上和10.2.0.3上结果不一样,10.2.0.3是错误的如下:
1 12 aa 男
2 14;13 cc;bb 男
3 15 dd 女
4 17;16 ff;ee 女;男
5 18 gg 男
7 20;19 ii;hh 男
9 21 jj 女
10 22 k 男
10.2.0.1结果正确如下:
1 12 aa 男
2 14;13 cc;bb 男;女
3 15 dd 女
4 17;16 ff;ee 女;男
5 18 gg 男
7 20;19 ii;hh 男;女
9 21 jj 女
10 22 k 男谁能解释一下这个结果