--给时间加索引! select sum(isnull(money,0))sum_money from tb where dataf between .. and ..
--给时间加索引!按员工合计 select id,sum(isnull(money,0))sum_money from tb where dataf between .. and .. group by id
用ID和日期做索引. select ID,sum(isnull(money,0)) as Total where dataf between '2004-02-01' and '2005-06-30' group by ID
declare @table table (ID int,money int,DATET datetime,DATAF datetime) insert into @table select 1,4,'2004-02-09',null union all select 1,7,'2004-04-09',null union all select 2,7,'2005-03-14',null union all select 2,7,'2005-08-14',null union all select 3,14,'2005-03-12',null union all select 4,27,'2005-08-12',null union all select 5,1,'2009-08-08',null union all select 6,5,'2010-12-02',nullselect ID,sum(money) as sumvalue from @table where DATET between '2004-02-01' and '2005-05-31' group by ID /* ID sumvalue ----------- ----------- 1 11 2 7 3 14 */--DATET加上索引 --ID加上索引
--给时间加索引!按员工合计 select id,sum(isnull(money,0))sum_money from tb where dataf between 2004-02-01 and 2006-06-30 group by id
时间那里有点特殊,是不是两个字段?
--给时间加索引!
select sum(isnull(money,0))sum_money
from tb
where dataf between .. and ..
--给时间加索引!按员工合计
select id,sum(isnull(money,0))sum_money
from tb
where dataf between .. and ..
group by id
select ID,sum(isnull(money,0)) as Total
where dataf between '2004-02-01' and '2005-06-30'
group by ID
declare @table table (ID int,money int,DATET datetime,DATAF datetime)
insert into @table
select 1,4,'2004-02-09',null union all
select 1,7,'2004-04-09',null union all
select 2,7,'2005-03-14',null union all
select 2,7,'2005-08-14',null union all
select 3,14,'2005-03-12',null union all
select 4,27,'2005-08-12',null union all
select 5,1,'2009-08-08',null union all
select 6,5,'2010-12-02',nullselect ID,sum(money) as sumvalue from @table
where DATET between '2004-02-01' and '2005-05-31'
group by ID
/*
ID sumvalue
----------- -----------
1 11
2 7
3 14
*/--DATET加上索引
--ID加上索引
select id,sum(isnull(money,0))sum_money
from tb
where dataf between 2004-02-01 and 2006-06-30
group by id