select jydbh,jinanshu,
case when jsfs ='现金 ' then zyf else 0 end xianjin,
case when jsfs ='上付 ' then zyf else 0 end shangfu, jsfs
from jydxx,hwxx
where jydxx.jydbh=hwxx.jydbh
case when jsfs ='现金 ' then zyf else 0 end xianjin,
case when jsfs ='上付 ' then zyf else 0 end shangfu, jsfs
from jydxx,hwxx
where jydxx.jydbh=hwxx.jydbh
SELECT jydbh,jianshu,
[xianjin]=case when xianjin!=0 then zyf else 0 end,
[shangfu]=case when shangfu!=0 then zyf else 0 end,
jsfs
FROM jydxx,hwxx
WHERE jydxx.jydbh=hwxx.jydbh
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([hw_id] int,[jydbh] varchar(11),[jianshu] int,[fhrxm] varchar(1),[yingjje] int,[xianjin] int,[shangfu] int,[zyf] int,[jsfs] varchar(4))
insert [tb]
select 238,'MF090412001',2,'D',100,100,0,40,'现金' union all
select 239,'MF090412001',1,'D',100,100,0,60,'现金' union all
select 240,'MF090412002',2,'c',50,0,50,20,'上付' union all
select 241,'MF090412002',1,'D',50,0,50,30,'上付'
---查询---
select
jydbh,
jianshu,
xianjin=case jsfs when '现金' then zyf else 0 end,
shangfu=case jsfs when '上付' then zyf else 0 end,
jsfs
from [tb]
---结果---
jydbh jianshu xianjin shangfu jsfs
----------- ----------- ----------- ----------- ----
MF090412001 2 40 0 现金
MF090412001 1 60 0 现金
MF090412002 2 0 20 上付
MF090412002 1 0 30 上付(所影响的行数为 4 行)
把tb替换成(select * from jydxx,hwxx where jydxx.jydbh=hwxx.jydbh) t 即可