17,377
社区成员
发帖
与我相关
我的任务
分享
create table table_order_mst (订单编号 varchar2(20),订单类型 varchar2(20),订单总金额 number(10),成本价 number(10),数量 number(10));
insert into table_order_mst values ('D1','折扣订单',10000,0,0);
insert into table_order_mst values ('D2','成本订单',20000,1000,10);
create table table_order_det (订单编号 varchar2(5),数量 number(10),单价 number(10),金额 number(10));
insert into table_order_det values ('D1',4,1000,4000);
insert into table_order_det values ('D1',4,1000,6000);
insert into table_order_det values ('D2',6,0,0);
insert into table_order_det values ('D2',7,0,0);
select tb2.订单编号,tb2.数量,tb2.单价,
case when row_number() over(partition by tb2.订单编号 order by tb2.订单编号)=1 and tb2.金额=0 then tb1.订单总金额 else tb2.金额 end 金额
from table_order_mst tb1,table_order_det tb2
where tb1.订单编号=tb2.订单编号;
订单编号 数量 单价 金额
---------------------------------------------
1 D1 4 1000 4000
2 D1 6 1000 6000
3 D2 6 0 20000
4 D2 7 0 0
select tb2.fld1,tb2.fld2,case when row_number() over(partition by tb2.fld1 order by tb2.fld2)=1 then tb1.fld2 else 0 end fld2
from tb1,tb2
where tb1.fld1=tb2.fld1;
fld1 fld2 fld2
-------------------------
1 D1 1.32 100
2 D1 1.33 0
3 D1 1.45 0
4 D2 1.22 200
5 D2 1.33 0
6 D2 1.55 0
create table table1 (
FLD1 VARCHAR2(111),
FLD2 NUMBER(20,2)
)
create table table2(
FLD1 VARCHAR2(111),
FLD2 NUMBER(20,2)
)
insert into table1 (FLD1, FLD2) values ('D1', 100);
insert into table2 (FLD1, FLD2) values ('D1', 1);
insert into table2 (FLD1, FLD2) values ('D1', 2);
insert into table2 (FLD1, FLD2) values ('D1', 3);
insert into table1 (FLD1, FLD2) values ('D2', 200);
insert into table2 (FLD1, FLD2) values ('D2', 1);
insert into table2 (FLD1, FLD2) values ('D2', 2);
insert into table2 (FLD1, FLD2) values ('D2', 3);
select t1.fld1,
t2.fld2,
decode(row_number() over(partition by t1.fld1 order by t2.fld2),
1,
t1.fld2,
0)
from table1 t1, table2 t2
where t1.fld1 = t2.fld1;
FLD1 FLD2 DECODE(ROW_NUMBER()OVER(PARTIT
-------------------------------------------------------------------------------- ---------------------- ------------------------------
D1 1.00 100
D1 2.00 0
D1 3.00 0
D2 1.00 200
D2 2.00 0
D2 3.00 0
6 rows selected
create table tb1 (Fld1 varchar2(5),Fld2 number(10));
insert into tb1 values ('D1',100);
insert into tb1 values ('D2',200);
create table tb2 (Fld1 varchar2(5),Fld2 number(10));
insert into tb2 values ('D1',1);
insert into tb2 values ('D1',2);
insert into tb2 values ('D1',3);
insert into tb2 values ('D2',1);
insert into tb2 values ('D2',2);
insert into tb2 values ('D2',3);
select tb2.fld1,case when tb2.fld2=1 then tb1.fld2 else 0 end fld2
from tb1,tb2
where tb1.fld1=tb2.fld1
fld1 fld2
-------------------------
1 D1 100
2 D1 0
3 D1 0
4 D2 200
5 D2 0
6 D2 0