表一：
编号 名称 类别 金额
D1 A X 100
D2 B Y 200
D3 C X 150
D3 C Y 300
D4 D X 500
表二：
编号 类别 结算金额 结算方式
D1 X 890 电汇
D2 Y 751 承兑
D3 X 521 电汇
D4 X 654 电汇
结果
编号 名称 类别 金额 结算金额 结算方式
D1 A X 100 890 电汇
D2 B Y 200 751 承兑
D3 C X 150 521 电汇
D3 C Y 300 0 电汇
D4 D X 500 654 电汇
根据编号、类别左连接查询，查询结果表二中没有记录的，结算方式使用有记录的结算方式补充，结算金额用0补充
------解决方案--------------------
with
t as (select 'D1' "编号", 'A' "名称",'X' "类别",100 "金额" from dual
union all
select 'D2' "编号", 'B' "名称",'Y' "类别",200 "金额" from dual
union all
select 'D3' "编号", 'C' "名称",'X' "类别",150 "金额" from dual
union all
select 'D3' "编号", 'C' "名称",'Y' "类别",300 "金额" from dual
union all
select 'D4' "编号", 'D' "名称",'X' "类别",500 "金额" from dual),
t1 as (select 'D1' "编号",'X' "类别",890 "结算金额",'电汇' "结算方式" from dual
union all
select 'D2' "编号",'Y' "类别",751 "结算金额",'承兑' "结算方式" from dual
union all
select 'D3' "编号",'X' "类别",521 "结算金额",'电汇' "结算方式" from dual
union all
select 'D4' "编号",'X' "类别",654 "结算金额",'电汇' "结算方式" from dual
)
select t."编号",t."名称",t."类别",t."金额",decode(t1."结算金额",null,0,t1."结算金额"),t1."结算方式"
from t left join t1 on
t."编号"=t1."编号"
and t."类别"=t1."类别"
order by t."编号";
------解决方案--------------------
上面的忘了加查询结果表二中没有记录的结算方式使用有记录的结算方式补充
with
t as (select 'D1' "编号", 'A' "名称",'X' "类别",100 "金额" from dual
union all
select 'D2' "编号", 'B' "名称",'Y' "类别",200 "金额" from dual
union all
select 'D3' "编号", 'C' "名称",'X' "类别",150 "金额" from dual
union all
select 'D3' "编号", 'C' "名称",'Y' "类别",300 "金额" from dual
union all
select 'D4' "编号", 'D' "名称",'X' "类别",500 "金额" from dual),
t1 as (select 'D1' "编号",'X' "类别",890 "结算金额",'电汇' "结算方式" from dual
union all
select 'D2' "编号",'Y' "类别",751 "结算金额",'承兑' "结算方式" from dual
union all
select 'D3' "编号",'X' "类别",521 "结算金额",'电汇' "结算方式" from dual
union all
select 'D4' "编号",'X' "类别",654 "结算金额",'电汇' "结算方式" from dual
)
select t."编号",t."名称",t."类别",t."金额",decode(t1."结算金额",null,0,t1."结算金额"),
decode(t1."结算方式",null,(select t1."结算方式" from t1 where t."编号"=t1."编号"),t1."结算方式") "结算方式"
from t left join t1 on
t."编号"=t1."编号"
and t."类别"=t1."类别"
order by t."编号",t."金额";
------解决方案--------------------
楼上,(select t1."结算方式" from t1 where t."编号"=t1."编号"),这句请加个top 1，不然容易出错
------解决方案--------------------
...楼上穿越到sqlserver了吧
------解决方案--------------------
我去，还真是，oracle是rownumber = 1吧
------解决方案--------------------