如下表:
字段:d001 d002 d003
记录:张三 100 2011-01-01 11:20:33
张三 100 2011-01-08 11:20:33
李四 200 2011-01-01 11:20:33
李四 200 2011-01-08 11:20:33按一段时间内统计查询结果显示:
张三 200 100 100
李四 400 200 200
字段:d001 d002 d003
记录:张三 100 2011-01-01 11:20:33
张三 100 2011-01-08 11:20:33
李四 200 2011-01-01 11:20:33
李四 200 2011-01-08 11:20:33按一段时间内统计查询结果显示:
张三 200 100 100
李四 400 200 200
declare @table table (d001 varchar(4),d002 int,d003 datetime)
insert into @table
select '张三',100,'2011-01-01 11:20:33' union all
select '张三',100,'2011-01-08 11:20:33' union all
select '李四',200,'2011-01-01 11:20:33' union all
select '李四',200,'2011-01-08 11:20:33'select distinct a.*,b.d002 as col2,b.d002 as col3 from (
select d001,sum(d002) as d002 from @table group by d001)
a
left join @table b on a.d001=b.d001
order by d001 desc
/*
d001 d002 col2 col3
---- ----------- ----------- -----------
张三 200 100 100
李四 400 200 200
*/
insert into tb select '张三',100,'2011-01-01 11:20:33'
insert into tb select '张三',100,'2011-01-08 11:20:33'
insert into tb select '李四',200,'2011-01-01 11:20:33'
insert into tb select '李四',200,'2011-01-08 11:20:33'
go
select a.d001,前日+后日 as 总,前日,后日 from
(select distinct d001 from tb)a left join
(select d001,d002 as 前日 from tb where d003='2011-01-01 11:20:33')b on a.d001=b.d001 left join
(select d001,d002 as 后日 from tb where d003='2011-01-08 11:20:33')c on a.d001=c.d001
go
drop table tb
/*
d001 总 前日 后日
---------- ----------- ----------- -----------
李四 400 200 200
张三 200 100 100(2 行受影响)
*/
insert into tb select '张三',100,'2011-01-01 11:20:33'
insert into tb select '张三',100,'2011-01-08 11:20:33'
insert into tb select '李四',200,'2011-01-01 11:20:33'
insert into tb select '李四',200,'2011-01-08 11:20:33'--如果你确定为'2011-01-01','2011-01-08'
select d001,
sum(d002) [合计],
max(case convert(varchar(10),d003,120) when '2011-01-01' then d002 else 0 end) [2011-01-01],
max(case convert(varchar(10),d003,120) when '2011-01-08' then d002 else 0 end) [2011-01-08]
from tb
group by d001
/*
d001 合计 2011-01-01 2011-01-08
---------- ----------- ----------- -----------
李四 400 200 200
张三 200 100 100(所影响的行数为 2 行)
*/--如果你不确定为'2011-01-01','2011-01-08'
declare @sql varchar(8000)
set @sql = 'select d001 , sum(d002) [合计] '
select @sql = @sql + ' , max(case convert(varchar(10),d003,120) when ''' + d003 + ''' then d002 else 0 end) [' + d003 + ']'
from (select distinct convert(varchar(10),d003,120) d003 from tb) as a
set @sql = @sql + ' from tb group by d001'
exec(@sql)
/*
d001 合计 2011-01-01 2011-01-08
---------- ----------- ----------- -----------
李四 400 200 200
张三 200 100 100*/drop table tb