求一SQL语句！
表A
F_code F_Area
001 A区
002 B区
003 C区
004 D区
表B
F_bookdate F_Areacode F_Nums
2004-10-20 12:50:00 001 10
2004-10-20 12:50:00 002 20
2004-10-21 12:50:00 003 40
2004-10-21 12:50:00 004 60
统计后的结果为：
日期 A区 B区 C区 D区
2004-10-20 10 20 0 0
2004-10-21 0 0 40 60
请大家帮忙看看！
[解决办法]
create table A(F_code char(3), F_Area nvarchar(20))
insert A select '001 ', 'A区 '
union all select '002 ', 'B区 '
union all select '003 ', 'C区 '
union all select '004 ', 'D区 '
create table B(F_bookdate datetime, F_Areacode char(3), F_Nums int)
insert B select '2004-10-20 12:50:00 ', '001 ', 10
union all select '2004-10-20 12:50:00 ', '002 ', 20
union all select '2004-10-21 12:50:00 ', '003 ', 40
union all select '2004-10-21 12:50:00 ', '004 ', 60
declare @sql varchar(8000)
set @sql= 'select F_bookdate, '
select @sql=@sql+quotename(F_Area)+ '=sum(case when F_Areacode= '+quotename(F_code, ' ' ' ')+ ' then F_Nums else 0 end), '
from A
select @sql=left(@sql, len(@sql)-1), @sql=@sql+ ' from B group by F_bookdate '
exec(@sql)
--result
F_bookdate A区 B区 C区 D区
------------------------------------------------------ ----------- ----------- ----------- -----------
2004-10-20 12:50:00.000 10 20 0 0
2004-10-21 12:50:00.000 0 0 40 60
[解决办法]
create table tab
(
F_bookdate varchar(10),
F_Areacode varchar(20),
F_Nums varchar(20)
)
insert into tab select '2004-10-20 ', '001 ', '10 '
insert into tab select '2004-10-20 ', '002 ', '20 '
insert into tab select '2004-10-21 ', '003 ', '40 '
insert into tab select '2004-10-21 ', '004 ', '60 '
create table tab1
(
F_code varchar(10),
F_area varchar(20)
)
insert into tab1 select '001 ', 'A区 '
insert into tab1 select '002 ', 'B区 '
insert into tab1 select '003 ', 'C区 '
insert into tab1 select '004 ', 'D区 '
--语句
declare @sql varchar(2000)
set @sql = 'select F_bookdate '
select @sql = @sql + ',sum(case F_Areacode when ' ' '+a.F_Areacode+ ' ' ' then F_Nums else 0 end )as [ '+b.F_area+ '] '
from (select distinct F_Areacode from tab) a , tab1 b where a.F_Areacode = b.F_code
set @sql = @sql + ' from tab group by F_bookdate '
exec(@sql)
[解决办法]
create table test_t(F_code char(3), F_Area nvarchar(20))
insert test_t select '001 ', 'A区 '
union all select '002 ', 'B区 '
union all select '003 ', 'C区 '
union all select '004 ', 'D区 '
create table test_t1(F_bookdate datetime, F_Areacode char(3), F_Nums int)
insert test_t1 select '2004-10-20 12:50:00 ', '001 ', 10
union all select '2004-10-20 12:50:00 ', '002 ', 20
union all select '2004-10-21 12:50:00 ', '003 ', 40
union all select '2004-10-21 12:50:00 ', '004 ', 60
declare @sql varchar(1000)
select @sql=isnull(@sql, ' ')+ ',[ '+F_Area+ ']=sum(case F_Area when ' ' '+F_Area+ ' ' 'then F_Nums else 0 end) '
from test_t group by F_Area
set @sql= 'select F_bookdate=convert(varchar(10),F_bookdate,120) '+@sql
+ ' from test_t a inner join test_t1 b on F_Areacode=F_code group by convert(varchar(10),F_bookdate,120) '
--print @sql
exec(@sql)
F_bookdate A区 B区 C区 D区
---------- ----------- ----------- ----------- -----------
2004-10-20 10 20 0 0
2004-10-21 0 0 40 60