declare @tb table(name varchar(10),money int,addDate datetime)
insert into @tb select '张三',1000,'2008-05-02'
union all select '李四',2000,'2008-05-03'
union all select '张三',1000,'2008-06-02'
union all select '李四',2000,'2008-06-03'
union all select '张三',1000,'2008-07-02'
union all select '李四',2000,'2008-07-03'
union all select '张三',1000,'2008-08-02'
union all select '李四',2000,'2008-08-03'
select a.*,b.money as totalmoney from @tb a
inner join(
select name,sum(money) as money from @tb
where addDate >='2008-06-01' and addDate <='2008-07-31'
group by name) b
on a.name=b.name
where a.addDate >='2008-06-01' and a.addDate <='2008-07-31'
/*
name money addDate totalmoney
---------- ----------- ------------------------------------------------------ -----------
李四 2000 2008-06-03 00:00:00.000 4000
李四 2000 2008-07-03 00:00:00.000 4000
张三 1000 2008-06-02 00:00:00.000 2000
张三 1000 2008-07-02 00:00:00.000 2000(所影响的行数为 4 行)
*/
insert into @tb select '张三',1000,'2008-05-02'
union all select '李四',2000,'2008-05-03'
union all select '张三',1000,'2008-06-02'
union all select '李四',2000,'2008-06-03'
union all select '张三',1000,'2008-07-02'
union all select '李四',2000,'2008-07-03'
union all select '张三',1000,'2008-08-02'
union all select '李四',2000,'2008-08-03'
select a.*,b.money as totalmoney from @tb a
inner join(
select name,sum(money) as money from @tb
where addDate >='2008-06-01' and addDate <='2008-07-31'
group by name) b
on a.name=b.name
where a.addDate >='2008-06-01' and a.addDate <='2008-07-31'
/*
name money addDate totalmoney
---------- ----------- ------------------------------------------------------ -----------
李四 2000 2008-06-03 00:00:00.000 4000
李四 2000 2008-07-03 00:00:00.000 4000
张三 1000 2008-06-02 00:00:00.000 2000
张三 1000 2008-07-02 00:00:00.000 2000(所影响的行数为 4 行)
*/
insert into @tb select '张三',1000,'2008-05-02'
insert into @tb select '李四',2000,'2008-05-03'
insert into @tb select '张三',1000,'2008-06-02'
insert into @tb select '李四',2000,'2008-06-03'
insert into @tb select '张三',1000,'2008-07-02'
insert into @tb select '李四',2000,'2008-07-03'
insert into @tb select '张三',1000,'2008-08-02'
insert into @tb select '李四',2000,'2008-08-03';with tp as
(
select * from @tb
where adddate between '2008-06-01' and '2008-07-31'
)select *,money2=(select sum(money) from tp where name=t.name)
from tp tname money adddate money2
张三 1000 2008-06-02 00:00:00.000 2000
李四 2000 2008-06-03 00:00:00.000 4000
张三 1000 2008-07-02 00:00:00.000 2000
李四 2000 2008-07-03 00:00:00.000 4000
drop table tb
go
create table tb(name varchar(10),[money] int,adddate datetime)
insert into tb select '张三',1000,'2008-05-02'
insert into tb select '李四',2000,'2008-05-03'
insert into tb select '张三',1000,'2008-06-02'
insert into tb select '李四',2000,'2008-06-03'
insert into tb select '张三',1000,'2008-07-02'
insert into tb select '李四',2000,'2008-07-03'
insert into tb select '张三',1000,'2008-08-02'
insert into tb select '李四',2000,'2008-08-03'
goalter proc proc_tt
@startdate datetime,
@enddate datetime
as
beginselect *,
money2=(select sum(money) from tb where adddate between @startdate and @enddate and name=t.name)
from tb t
where adddate between @startdate and @enddateendexec proc_tt '2008-06-01','2008-07-31'
name money adddate money2
张三 1000 2008-06-02 00:00:00.000 2000
李四 2000 2008-06-03 00:00:00.000 4000
张三 1000 2008-07-02 00:00:00.000 2000
李四 2000 2008-07-03 00:00:00.000 4000
as
create table #Temp(TName varchar(50),Total money)
insert into #Temp select TName,sum(Aount) from t where BuildDate between @BuildDate and @EndDate
group by TName
select a.*,b.Total from t a right outer join #Temp b on a.Tname=b.tname where BuildDate between @BuildDate and @EndDate
exec p '2008-06-01','2008-07-31'