select name,(select sum(hours) from t where date>='2004-8-26' and date<Date and name=a.name) as Hours1,
(select sum(hours) from t where date>='2004-8-29' and date<Date and name=a.name) as Hours2
from t a
group by name
(select sum(hours) from t where date>='2004-8-29' and date<Date and name=a.name) as Hours2
from t a
group by name
sum1=sum([hours]),
sum2=sum(case when [date]>='2004-8-29' then [hours] else 0 end)
from tablename
where [date]>='2004-8-26'
group by [name]
select [name],
sum1=sum([hours]),
sum2=sum(case when [date]>='2004-8-29' then [hours] else 0 end)
from tablename
where [date]>='2004-8-26'
group by [name]
如何建立临时表????
因为上述只是举例,
实际上要复杂的多
有另外一个JOIN过来的表
--示例代码:
--考勤表T,结构如下:
--Name|Hours|Date
--有不同人的记录若干
--现想分类统计每人从2004-8-26至今的Hours合计和从(2004-8-29)至今的Hours合计。
--难点:两个合计必须在一个查询中返回,
--是在一起的。。
--请高手指点
declare @tb1 table ([Name] varchar(20),Hours decimal(28,2),[Date] datetime)
insert into @tb1
select '张三', 8, '2004-8-27' union all
select '李四', 8.5, '2004-8-28' union all
select '王二', 8.75, '2004-8-29' union all
select '王五', 8, '2004-8-30' union all
select '张三', 8.25, '2004-8-28' union all
select '李四', 8, '2004-8-27' union all
select '王五', 8, '2004-8-29'
select a.[Name],Count_Hours=sum(a.Hours) from (select * from @tb1 where datediff(day,cast('2004-8-26'as datetime),[Date])>=0)a group by a.[Name]
union all
select a.[Name],sum(a.Hours) from (select * from @tb1 where datediff(day,cast('2004-8-29'as datetime),[Date])>=0)a group by a.[Name]/*
Name Count_Hours
-------------------- ----------------------------------------
李四 16.50
王二 8.75
王五 16.00
张三 16.25
王二 8.75
王五 16.00(所影响的行数为 6 行)
*/
sum1=sum([hours]),
sum2=sum(case when [date]>='2004-8-29' then [hours] else 0 end)
into 中间表
from tablename
where [date]>='2004-8-26'
group by [name]或者如果你要不想用的话,也可以这样:
select *from
(
select [name],
sum1=sum([hours]),
sum2=sum(case when [date]>='2004-8-29' then [hours] else 0 end)
from tablename
where [date]>='2004-8-26'
group by [name]
) a join 其他表 b on .....--没测试,自己测试一下