请教个复杂的SQL语句
表XS
dq je sz
a 100 2007.1.1
a 10 2007.1.1
b 100 2007.1.1
a 50 2007.1.2
a 10 2007.1.31
b 60 2007.2.1
怎么查出这样的形式
dq je总和 1 2 3 4 一直到31(每天)
[解决办法]
create table tb(dq varchar(10),je int,sz datetime)
insert into tb values( 'a ', 100 , '2007.1.1 ')
insert into tb values( 'a ', 10 , '2007.1.1 ')
insert into tb values( 'b ', 100 , '2007.1.1 ')
insert into tb values( 'a ', 50 , '2007.1.2 ')
insert into tb values( 'a ', 10 , '2007.1.31 ')
insert into tb values( 'b ', 60 , '2007.2.1 ')
go
declare @sql varchar(8000)
set @sql = 'select dq '
select @sql = @sql + ' , sum(case datepart(day,sz) when ' ' ' + cast(rq as varchar) + ' ' ' then je else 0 end) [ ' + cast(rq as varchar) + '] '
from (select distinct datepart(day,sz) rq from tb) as a
set @sql = @sql + ' from tb group by dq '
exec(@sql)
drop table tb
/*
dq 1 2 31
---------- ----------- ----------- -----------
a 110 50 10
b 160 0 0
*/
[解决办法]
--不知道加个月份是不是要好点?
create table tb(dq varchar(10),je int,sz datetime)
insert into tb values( 'a ', 100 , '2007.1.1 ')
insert into tb values( 'a ', 10 , '2007.1.1 ')
insert into tb values( 'b ', 100 , '2007.1.1 ')
insert into tb values( 'a ', 50 , '2007.1.2 ')
insert into tb values( 'a ', 10 , '2007.1.31 ')
insert into tb values( 'b ', 60 , '2007.2.1 ')
go
declare @sql varchar(8000)
set @sql = 'select dq,convert(varchar(7),sz,120) 月份 '
select @sql = @sql + ' , sum(case datepart(day,sz) when ' ' ' + cast(rq as varchar) + ' ' ' then je else 0 end) [ ' + cast(rq as varchar) + '] '
from (select distinct datepart(day,sz) rq from tb) as a
set @sql = @sql + ' from tb group by dq,convert(varchar(7),sz,120) '
exec(@sql)
drop table tb
/*
dq 月份 1 2 31
---------- ------- ----------- ----------- -----------
a 2007-01 110 50 10
b 2007-01 100 0 0
b 2007-02 60 0 0
*/
[解决办法]
create table tb(dq varchar(10),je int,sz datetime)
insert into tb values( 'a ', 100 , '2007.1.1 ')
insert into tb values( 'a ', 10 , '2007.1.1 ')
insert into tb values( 'b ', 100 , '2007.1.1 ')
insert into tb values( 'a ', 50 , '2007.1.2 ')
insert into tb values( 'a ', 10 , '2007.1.31 ')
insert into tb values( 'b ', 60 , '2007.2.1 ')
go
declare @sql varchar(8000)
set @sql = 'select dq,sum(je)je总和 '
select @sql = @sql + ' , sum(case datepart(day,sz) when ' ' ' + cast(rq as varchar) + ' ' ' then je else 0 end) [ ' + cast(rq as varchar) + '] '
from (select distinct datepart(day,sz) rq from tb) as a
set @sql = @sql + ' from tb group by dq '
exec(@sql)
drop table tb
/*
dq je总和 1 2 31
---------- ----------- ----------- ----------- -----------
a 170 110 50 10
b 160 160 0 0
*/
[解决办法]
1.对dq建立索引.
2.使用我的这个动态语句.
3.如果日期固定,可以写成静态的.
select dq,sum(je)je总和,
sum(case datepart(day,sz) when 1 then je else 0 end) '1号 ',
sum(case datepart(day,sz) when 2 then je else 0 end) '2号 ',
...
sum(case datepart(day,sz) when 31 then je else 0 end) '31号 '
from tb
group by dq
