22,210
社区成员
发帖
与我相关
我的任务
分享
create table a (bh varchar(2),mc varchar(10))
insert into a values ('01','A账户')
insert into a values ('02','B账户')
create table b(lx varchar(2),shkh varchar(2),fkh varchar(2),je decimal(13,2))
insert into b values ('21','01',null,100.00)
insert into b values ('23','02','01',200.00)
SELECT t1.*,t2.mc AS shmc,t3.mc AS fmc
FROM (
SELECT *,0 AS se FROM b UNION ALL
SELECT lx,fkh,shkh,0,je FROM b WHERE shkh IS NOT NULL AND fkh IS NOT null
) t1
JOIN a t2 ON t1.shkh=t2.bh
JOIN a t3 ON t1.fkh=t3.bh
/*
lx shkh fkh je se shmc fmc
---- ---- ---- --------------------------------------- --------------------------------------- ---------- ----------
23 02 01 200.00 0.00 B账户 A账户
23 01 02 0.00 200.00 A账户 B账户
*/
create table #a (bh varchar(2),mc varchar(10))
insert into #a values ('01','A账户')
insert into #a values ('02','B账户')
create table #b(lx varchar(2),shkh varchar(2),fkh varchar(2),je decimal(13,2))
insert into #b values ('21','01',null,100.00)
insert into #b values ('23','02','01',200.00)
select 类型,账号名称,对方账号名称,convert(varchar(50),收入金额)收入金额,convert(varchar(50),支出金额)支出金额
from
(
select lx '类型',
case when shkh='02' then (select mc from #a where bh='02') end '账号名称',
case when fkh='01' then (select mc from #a where bh='01') end '对方账号名称',
case when shkh='02' then je end '收入金额',
case when fkh='01' then '' end '支出金额'
from #b
where lx=23
)cte
union all
select 类型,对方账号名称,账号名称,convert(varchar(50),支出金额),convert(varchar(50),收入金额)
from
(
select lx '类型',
case when shkh='02' then (select mc from #a where bh='02') end '账号名称',
case when fkh='01' then (select mc from #a where bh='01') end '对方账号名称',
case when shkh='02' then je end '收入金额',
case when fkh='01' then '' end '支出金额'
from #b
where lx=23
)cte
/*
类型 账号名称 对方账号名称 收入金额 支出金额
23 B账户 A账户 200.00
23 A账户 B账户 200.00
*/
select c.lx,a.mc as account,d.mc as to_account,c.get_money,c.pay_money from
(
select lx,shkh as account,fkh as to_account,je as get_money,null as pay_money
from (
select * from b where b.shkh is not null and b.fkh is not null) t
union all
select lx,fkh as account,shkh as to_account,null as get_money,je as pay_money
from (select * from b where b.shkh is not null and b.fkh is not null) t
) c left join a on c.account=a.bh
left join a d on c.to_account=d.bh
create table #a (bh varchar(2),mc varchar(10))
insert into #a values ('01','A账户')
insert into #a values ('02','B账户')
create table #b(lx varchar(2),shkh varchar(2),fkh varchar(2),je decimal(13,2))
insert into #b values ('21','01',null,100.00)
insert into #b values ('23','02','01',200.00)
select * from #b
with cte as
(
select lx '类型',
case when shkh='02' then (select mc from #a where bh='02') end '账号名称',
case when fkh='01' then (select mc from #a where bh='01') end '对方账号名称',
case when shkh='02' then je end '收入金额',
case when fkh='01' then '' end '支出金额'
from #b
where lx=23
)
select 类型,账号名称,对方账号名称,convert(varchar(50),收入金额)收入金额,convert(varchar(50),支出金额)支出金额 from cte
union all
select 类型,对方账号名称,账号名称,convert(varchar(50),支出金额),convert(varchar(50),收入金额) from cte
类型 账号名称 对方账号名称 收入金额 支出金额
---- ---------- ---------- -------------------------------------------------- --------------------------------------------------
23 B账户 A账户 200.00
23 A账户 B账户 200.00
(2 row(s) affected)