--建立測試環境
create table A1(ID int identity(1,1),CODE varchar(20),NAME nvarchar(50),KIND int)
insert into A1
select '0001','黃龍',1 union all
select '0002','粱祝',1 union all
select '0003','李誒',2 union all
select '0004','肖雨',1 union all
select '0005','龍文',3 create table A2(ID int identity(1,1),NAME nvarchar(50),MONEY decimal(18,3),RTYPE nvarchar(30),ENTERDATE datetime)
insert into A2
select '黃龍',100.00,'普通','2005-9-20' union all
select '粱祝',300.00,'快班','2005-9-22' union all
select '肖雨',95.50, '專線','2005-9-21' union all
select '黃龍',250.00,'普通','2005-9-23' union all
select '梁祝',450.00,'普通','2005-9-25' union all
select '黃龍','200.00','快班','2005-9-20' union all
select '肖雨',150.00,'普通','2005-9-21' create table A3(ID int identity(1,1),NAME nvarchar(50),BACKMONEY decimal(18,2),RTYPE nvarchar(30),ENTERDATE datetime)
insert into A3
select '黃龍',50.00,'普通','2005-9-20' union all
select '梁祝',20.00,'快班','2005-9-22' union all
select '黃龍',30.00,'快班','2005-9-21'--測試
select A.*,isnull(B.TotalBackMoney,0) TotalBackMoney from
(
select Name,sum(MONEY) TotalMoney,
sum(case when RTYPE='普通' then Money else 0 end) GeneralMoney,
sum(case when RTYPE='快班' then Money else 0 end) FastlMoney,
sum(case when RTYPE='專線' then Money else 0 end) SpecialMoney
from A2
where convert(char(10),ENTERDATE,120) between '2005-09-01' and '2005-09-30'
group by Name
) A left join
(
select Name,sum(BACKMONEY) TotalBackMoney
from A3
where convert(char(10),ENTERDATE,120) between '2005-09-01' and '2005-09-30'
group by Name
) B on A.Name=B.Name--刪除測試環境
drop table A1
drop table A2
drop table A3
/*
顯示結果Name TotalMoney GeneralMoney FastMoney SpecialMoney TotalBckMoney
肖雨 245.500 150.000 .000 95.500 .00
梁祝 450.000 450.000 .000 .000 20.00
黃龍 550.000 350.000 200.00 .000 80.00
粱祝 300.000 .000 300.000 .000 .00*/
create table A1(ID int identity(1,1),CODE varchar(20),NAME nvarchar(50),KIND int)
insert into A1
select '0001','黃龍',1 union all
select '0002','粱祝',1 union all
select '0003','李誒',2 union all
select '0004','肖雨',1 union all
select '0005','龍文',3 create table A2(ID int identity(1,1),NAME nvarchar(50),MONEY decimal(18,3),RTYPE nvarchar(30),ENTERDATE datetime)
insert into A2
select '黃龍',100.00,'普通','2005-9-20' union all
select '粱祝',300.00,'快班','2005-9-22' union all
select '肖雨',95.50, '專線','2005-9-21' union all
select '黃龍',250.00,'普通','2005-9-23' union all
select '梁祝',450.00,'普通','2005-9-25' union all
select '黃龍','200.00','快班','2005-9-20' union all
select '肖雨',150.00,'普通','2005-9-21' create table A3(ID int identity(1,1),NAME nvarchar(50),BACKMONEY decimal(18,2),RTYPE nvarchar(30),ENTERDATE datetime)
insert into A3
select '黃龍',50.00,'普通','2005-9-20' union all
select '梁祝',20.00,'快班','2005-9-22' union all
select '黃龍',30.00,'快班','2005-9-21'--測試
select A.*,isnull(B.TotalBackMoney,0) TotalBackMoney from
(
select Name,sum(MONEY) TotalMoney,
sum(case when RTYPE='普通' then Money else 0 end) GeneralMoney,
sum(case when RTYPE='快班' then Money else 0 end) FastlMoney,
sum(case when RTYPE='專線' then Money else 0 end) SpecialMoney
from A2
where convert(char(10),ENTERDATE,120) between '2005-09-01' and '2005-09-30'
group by Name
) A left join
(
select Name,sum(BACKMONEY) TotalBackMoney
from A3
where convert(char(10),ENTERDATE,120) between '2005-09-01' and '2005-09-30'
group by Name
) B on A.Name=B.Name--刪除測試環境
drop table A1
drop table A2
drop table A3
/*
顯示結果Name TotalMoney GeneralMoney FastMoney SpecialMoney TotalBckMoney
肖雨 245.500 150.000 .000 95.500 .00
梁祝 450.000 450.000 .000 .000 20.00
黃龍 550.000 350.000 200.00 .000 80.00
粱祝 300.000 .000 300.000 .000 .00*/
create table A1(ID int identity(1,1),CODE varchar(20),NAME nvarchar(50),KIND int)
insert into A1
select '0001','黃龍',1 union all
select '0002','粱祝',1 union all
select '0003','李誒',2 union all
select '0004','肖雨',1 union all
select '0005','龍文',3 create table A2(ID int identity(1,1),NAME nvarchar(50),MONEY decimal(18,3),RTYPE nvarchar(30),ENTERDATE datetime)
insert into A2
select '黃龍',100.00,'普通','2005-9-20' union all
select '粱祝',300.00,'快班','2005-9-22' union all
select '肖雨',95.50, '專線','2005-9-21' union all
select '黃龍',250.00,'普通','2005-9-23' union all
select '梁祝',450.00,'普通','2005-9-25' union all
select '黃龍','200.00','快班','2005-9-20' union all
select '肖雨',150.00,'普通','2005-9-21' create table A3(ID int identity(1,1),NAME nvarchar(50),BACKMONEY decimal(18,2),RTYPE nvarchar(30),ENTERDATE datetime)
insert into A3
select '黃龍',50.00,'普通','2005-9-20' union all
select '梁祝',20.00,'快班','2005-9-22' union all
select '黃龍',30.00,'快班','2005-9-21'--測試select A.*,isnull(B.TotalBackMoney,0) TotalBackMoney into #tmp from
(
select Name,sum(MONEY) TotalMoney,
sum(case when RTYPE='普通' then Money else 0 end) GeneralMoney,
sum(case when RTYPE='快班' then Money else 0 end) FastlMoney,
sum(case when RTYPE='專線' then Money else 0 end) SpecialMoney
from A2
where convert(char(10),ENTERDATE,120) between '2005-09-01' and '2005-09-30'
group by Name
) A left join
(
select Name,sum(BACKMONEY) TotalBackMoney
from A3
where convert(char(10),ENTERDATE,120) between '2005-09-01' and '2005-09-30'
group by Name
) B on A.Name=B.Name
select Rtrim(ltrim(name)),sum(TotalMoney) TotalMoney ,sum(GeneralMoney) GeneralMoney ,sum(FastlMoney) FastlMoney ,Sum(SpecialMoney) SpecialMoney ,sum(TotalBackMoney) TotalBackMoney from #tmp group by Rtrim(ltrim(name))
--刪除測試環境
drop table A1
drop table A2
drop table A3
drop table #tmp
/*
顯示結果Name TotalMoney GeneralMoney FastMoney SpecialMoney TotalBckMoney
肖雨 245.500 150.000 .000 95.500 .00
粱祝1 450.000 450.000 .000 .000 20.00
黃龍 550.000 350.000 200.00 .000 80.00
粱祝1 300.000 .000 300.000 .000 .00*/