表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请大家帮忙看看!
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请大家帮忙看看!
解决方案 »
- 关于存储过程中Insert问题~~急急(百分送)
- 計算周別的具體時間段?
- 存储过程动态创建表的问题
- 已成功与服务器建立连接,但是在登录过程中发生错误。 (provider: 命名管道提供程序, error: 0 - 管道的另一端上无任何进程。)
- 请教 insert into table () 的问题
- 【疑问】我在SQL SERVER2005建立连接服务器去查询DB2的数据,但是查询出来的数据,中文居然不显示,大家能否给个解决的思路?
- 本地计算机上的sql server agent 服务启动后又停止了。一些服务自动停止,如果它们没有什么可做的,例如“性能日志和警报“服务"
- 无法找到 sp_executesql
- 如何在两个不同的数据库间导数据(如sql server和access),有分!!!
- 怎样使查询结果中某一条记录总在第一条!请教!!!!!
- 分组统计数量问题
- 请教想用循环建表字段,但不知道为什么加字段语句不能用变量?附代码
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', 60declare @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
declare @sql varchar(8000)
set @sql='select F_bookdate=convert(char(10), F_bookdate, 120), '
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 convert(char(10), F_bookdate, 120)'
exec(@sql)--result
F_bookdate A区 B区 C区 D区
---------- ----------- ----------- ----------- -----------
2004-10-20 10 20 0 0
2004-10-21 0 0 40 60
(
DateClass varchar(10),
Class varchar(20),
count 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 DateClass 'select @sql = @sql + ',sum(case Class when '''+a.Class+''' then COUNT else 0 end )as ['+b.F_area+']'
from (select distinct Class from tab) a , tab1 b where a.Class = b.F_codeset @sql = @sql + ' from tab , tab1 b group by DateClass'exec(@sql)
(
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_codeset @sql = @sql + ' from tab group by F_bookdate'exec(@sql)
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', 60declare @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
(
F_code varchar(3),
F_area varchar(5)
)insert into #A values('001','A区')
insert into #A values('002','B区')
insert into #A values('003','C区')
insert into #A values('004','D区')Create table #B
(
F_bookdate datetime,
F_areacode varchar(3),
F_nums int
)insert into #B values('2004-10-20 12:50:00','001',10)
insert into #B values('2004-10-20 12:50:00','002',20)
insert into #B values('2004-10-21 12:50:00','003',40)
insert into #B values('2004-10-21 12:50:00','004',60)
select aa.F_bookdate 日期,aa.A区,bb.B区,cc.C区,dd.D区
from
(
select a.F_bookdate,isnull(b.nums,0) 'A区'
from
(
select distinct F_bookdate from #B
) a,
(
select F_bookdate,sum(F_nums) nums
from #a a,#b b
where a.F_code=b.F_areacode
and a.F_area='A区'
group by a.F_area,b.F_bookdate
) b
where a.F_bookdate*=b.F_bookdate
) aa,
(
select a.F_bookdate,isnull(b.nums,0) 'B区'
from
(
select distinct F_bookdate from #B
) a,
(
select F_bookdate,sum(F_nums) nums
from #a a,#b b
where a.F_code=b.F_areacode
and a.F_area='B区'
group by a.F_area,b.F_bookdate
) b
where a.F_bookdate*=b.F_bookdate
) bb,
(
select a.F_bookdate,isnull(b.nums,0) 'C区'
from
(
select distinct F_bookdate from #B
) a,
(
select F_bookdate,sum(F_nums) nums
from #a a,#b b
where a.F_code=b.F_areacode
and a.F_area='C区'
group by a.F_area,b.F_bookdate
) b
where a.F_bookdate*=b.F_bookdate
) cc,
(
select a.F_bookdate,isnull(b.nums,0) 'D区'
from
(
select distinct F_bookdate from #B
) a,
(
select F_bookdate,sum(F_nums) nums
from #a a,#b b
where a.F_code=b.F_areacode
and a.F_area='D区'
group by a.F_area,b.F_bookdate
) b
where a.F_bookdate*=b.F_bookdate
) dd
where aa.F_bookdate=bb.F_bookdate
and bb.F_bookdate=cc.F_bookdate
and cc.F_bookdate=dd.F_bookdate
如
F_bookdate A区 B区 C区 D区 合计
---------- ----------- ----------- ----------- -----------
2004-10-20 10 20 0 0 30
2004-10-21 0 0 40 60 100
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', 60declare @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
+',合计=sum(F_Nums)'--加一段合计
+ '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 30
2004-10-21 0 0 40 60 100
(
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)
declare @sql1 varchar(2000)
set @sql = 'select F_bookdate '
set @sql1 = ''
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_codeselect @sql1 = @sql1 + '+ sum(case F_Areacode when '''+a.F_Areacode+''' then F_Nums else 0 end )'
from (select distinct F_Areacode from tab) a , tab1 b where a.F_Areacode = b.F_codeset @sql = @sql +','+ STUFF(@sql1,1,1,'')+' 合计 from tab group by F_bookdate'exec(@sql)
(
F_code varchar(3),
F_area varchar(5)
)insert into #A values('001','A区')
insert into #A values('002','B区')
insert into #A values('003','C区')
insert into #A values('004','D区')Create table #B
(
F_bookdate datetime,
F_areacode varchar(3),
F_nums int
)insert into #B values('2004-10-20 12:50:00','001',10)
insert into #B values('2004-10-20 12:50:00','002',20)
insert into #B values('2004-10-21 12:50:00','003',40)
insert into #B values('2004-10-21 12:50:00','004',60)select * from #A
select * from #Bselect F_bookdate,
isnull((select sum(F_nums) from #B where F_areacode=(select F_code from #A where F_area='A区') and F_bookdate=b.F_bookdate),0) 'A区',
isnull((select sum(F_nums) from #B where F_areacode=(select F_code from #A where F_area='B区') and F_bookdate=b.F_bookdate),0) 'B区',
isnull((select sum(F_nums) from #B where F_areacode=(select F_code from #A where F_area='C区') and F_bookdate=b.F_bookdate),0) 'C区',
isnull((select sum(F_nums) from #B where F_areacode=(select F_code from #A where F_area='D区') and F_bookdate=b.F_bookdate),0) 'D区'
from #B b
group by F_bookdate