create table V_Table(RecID int primary key, KindName varchar(20),Amount money)
GO
-- 插入数据
insert into V_Table(RecID,KindName,Amount) values(1,'挂号费', 1.5)
insert into V_Table(RecID,KindName,Amount) values(2,'诊查费', 5)
insert into V_Table(RecID,KindName,Amount) values(3,'化验费', 38)
insert into V_Table(RecID,KindName,Amount) values(4,'挂号费', 3)
insert into V_Table(RecID,KindName,Amount) values(5,'诊查费', 22)
insert into V_Table(RecID,KindName,Amount) values(6,'化验费', 65)
insert into V_Table(RecID,KindName,Amount) values(7,'挂号费', -1)
insert into V_Table(RecID,KindName,Amount) values(8,'诊查费', -10)
insert into V_Table(RecID,KindName,Amount) values(9,'化验费', 108)
insert into V_Table(RecID,KindName,Amount) values(10,'挂号费', 1.5)
insert into V_Table(RecID,KindName,Amount) values(11,'诊查费', -5)
insert into V_Table(RecID,KindName,Amount) values(12,'化验费', -38)
GO
-- 显示数据
select * from V_Table
GO
正数为收费,负数为退费,要求编写sql语句,以显示如下所示的结果集:
------------------------------------------------
-- 类别 收费 退费 合计
------------------------------------------------
-- 挂号费 6.0000 -1.0000 5.00
-- 化验费 211.0000 -38.0000 173.00
-- 诊查费 27.0000 -15.0000 12.00
------------------------------------------------
GO
-- 插入数据
insert into V_Table(RecID,KindName,Amount) values(1,'挂号费', 1.5)
insert into V_Table(RecID,KindName,Amount) values(2,'诊查费', 5)
insert into V_Table(RecID,KindName,Amount) values(3,'化验费', 38)
insert into V_Table(RecID,KindName,Amount) values(4,'挂号费', 3)
insert into V_Table(RecID,KindName,Amount) values(5,'诊查费', 22)
insert into V_Table(RecID,KindName,Amount) values(6,'化验费', 65)
insert into V_Table(RecID,KindName,Amount) values(7,'挂号费', -1)
insert into V_Table(RecID,KindName,Amount) values(8,'诊查费', -10)
insert into V_Table(RecID,KindName,Amount) values(9,'化验费', 108)
insert into V_Table(RecID,KindName,Amount) values(10,'挂号费', 1.5)
insert into V_Table(RecID,KindName,Amount) values(11,'诊查费', -5)
insert into V_Table(RecID,KindName,Amount) values(12,'化验费', -38)
GO
-- 显示数据
select * from V_Table
GO
正数为收费,负数为退费,要求编写sql语句,以显示如下所示的结果集:
------------------------------------------------
-- 类别 收费 退费 合计
------------------------------------------------
-- 挂号费 6.0000 -1.0000 5.00
-- 化验费 211.0000 -38.0000 173.00
-- 诊查费 27.0000 -15.0000 12.00
------------------------------------------------
GO
-- 插入数据
insert into V_Table(RecID,KindName,Amount) values(1,'挂号费', 1.5)
insert into V_Table(RecID,KindName,Amount) values(2,'诊查费', 5)
insert into V_Table(RecID,KindName,Amount) values(3,'化验费', 38)
insert into V_Table(RecID,KindName,Amount) values(4,'挂号费', 3)
insert into V_Table(RecID,KindName,Amount) values(5,'诊查费', 22)
insert into V_Table(RecID,KindName,Amount) values(6,'化验费', 65)
insert into V_Table(RecID,KindName,Amount) values(7,'挂号费', -1)
insert into V_Table(RecID,KindName,Amount) values(8,'诊查费', -10)
insert into V_Table(RecID,KindName,Amount) values(9,'化验费', 108)
insert into V_Table(RecID,KindName,Amount) values(10,'挂号费', 1.5)
insert into V_Table(RecID,KindName,Amount) values(11,'诊查费', -5)
insert into V_Table(RecID,KindName,Amount) values(12,'化验费', -38)
GOselect KindName,SUM(case when Amount>0 then Amount else 0 end) 收费,
SUM(case when Amount<0 then Amount else 0 end) 退费,
SUM(Amount)
from V_Table group by KindName /*
KindName 收费 退费
-------------------- --------------------- --------------------- ---------------------
挂号费 6.00 -1.00 5.00
化验费 211.00 -38.00 173.00
诊查费 27.00 -15.00 12.00(3 行受影响)
SUM(case when Amount>0 then Amount else 0 end) 收费,
convert(decimal(10,4),SUM(case when Amount<0 then Amount else 0 end)) 退费,
SUM(Amount) 合计
from V_Table group by KindName