17,377
社区成员
发帖
与我相关
我的任务
分享
select 表1.结算编号,
(case
when 表2.付款方式 = '现金' and 表3.消费项目 = '足浴' then
表1.应收金额
else
0
end) 应收金额,
(case
when 表3.消费项目 = '足浴' then
表2.付款方式
when 表3.消费项目 = '按摩' then
表2.付款方式
else
null
end) 付款方式,
(case
when 表2.付款方式 = '现金' then
表2.付款金额
when 表2.付款方式 = '银联' and 表3.消费项目 = '按摩' then
表2.付款金额
else
0
end) 付款金额,
表3.消费项目,
表3.消费金额
from 表3
left join 表1
on 表3.结算编号 = 表1.结算编号
left join 表2
on 表2.结算编号 = 表1.结算编号
with a as
(
select '1000001' as id,250.00 as sumAm from dual
)
, b as
(
select '1000001' as id,'现金' as payty,50.00 as paym from dual
union all
select '1000001','银联',200.00 from dual
)
, c as
(
select '1000001' as id,'足浴' as conpro,100.00 as conm from dual
union all
select '1000001','按摩',100.00 from dual
union all
select '1000001','桑拿',50.00 from dual
)
select t1.id,nvl(t3.sumAm,0.00),t2.payty,nvl(t2.paym,0.00),t1.conpro,t1.conm from (select c.*,rownum as tr from c) t1
left join (select b.*,rownum as tr from b) t2 on t2.id=t1.id and t2.tr=t1.tr
left join (select a.*,rownum as tr from a) t3 on t3.id=t1.id and t3.tr=t1.tr