Create table trans
(
EmpID char(6),
TransDate DateTime,
TransType char(1),
TransMoney numeric(7,2)
)
go-------------------------------------测试数据
insert into trans
select '001','2001-09-01','0',1500.25
union all
select '001','2001-09-01','0',250.00
union all
select '002','2001-09-01','1',350.75
union all
select '003','2001-09-02','0',123.11go/*
Gh 员工工号 char(6)
Xm 姓名 Varchar(10)
例如:
Gh xm
001 张先生
002 李会计
003 王小姐
*/Create Table Emp
(
EmpID char(6),
EmpName varchar(10)
)
goinsert into Emp
select '001','张先生'
union all
select '002','李会计'
union
select '003','王小姐'
go
select
convert(varchar(10),tt1.transdate,120) as 日期,
case tt1.transtype
when 0 then '收'
when 1 then '付'
end as 方向,
isnull(tt2.金额,0) as 金额
from
(
select *
from
(select TransDate from trans group by transDate)t1,
(select TransType from trans group by transtype)t2
)tt1
left join
(
select Transdate,transtype,sum(transmoney)as 金额
from trans
group by transdate,transtype
)tt2
on
tt1.transdate=tt2.transdate
and
tt1.transtype=tt2.transtype
order by tt1.transdate,tt1.transtypedrop table Emp,trans/*
日期 方向 金额
---------- ---- ----------------------------------------
2001-09-01 收 1750.25
2001-09-01 付 350.75
2001-09-02 收 123.11
2001-09-02 付 .00*/
发过贴子之后,我想到了一个方法,大家帮忙看一下:
select 日期=a.TRansDate,方向=case
when b.TransType = '0' then '收款'
else '付款'
end
,金额=isnull((select sum(c.TransMoney) from Trans c where a.TransDate = c.TRansDate and b.TransType = c.TransType),0)
from Trans a,(select Distinct TransType from Trans) b
group by a.TransDate,b.TransType
go