select a.商品名, sum(case a.仓库名 when 's1' then 销售额 else 0 end) as S1销售, sum(case a.仓库名 when 's1' then 库存额 else 0 end) as S1库存, .... from 主表 a join 仓库表 b on a.仓库id =b.仓库id
-->TravyLee生成测试数据:[主表] if object_id('[主表]') is not null drop table [主表] create table [主表]( [商品名] int, [仓库名] varchar(2), [仓库id] int, [销售额] int, [库存额] int ) insert [主表] select 1,'S1',111,2,5 union all select 2,'S1',111,3,1 union all select 1,'S2',222,2,2 union all select 3,'S3',333,1,1 --> 测试数据:[仓库表] if object_id('[仓库表]') is not null drop table [仓库表] create table [仓库表]( [仓库名] varchar(2), [仓库id] int ) insert [仓库表] select 'S1',111 union all select 'S1',111 union all select 'S2',222 union all select 'S3',333 union all select 'S4',444select a.商品名,b.仓库名,a.销售额,a.库存额 into #test from [主表] a left join [仓库表] b on a.仓库id=b.仓库iddeclare @str varchar(max) set @str='' select @str=@str+','+仓库名+'销售'+'=sum(case when 仓库名=' +QUOTENAME(仓库名,'''')+' then 销售额 else 0 end),' +仓库名+'库存'+'=sum(case when 仓库名='+QUOTENAME(仓库名,'''') +' then 库存额 else 0 end)' from #test group by 仓库名 exec('select 商品名'+@str+' from #test group by 商品名')/* 商品名 S1销售 S1库存 S2销售 S2库存 S3销售 S3库存 ---------------------------------------------------------------- 1 4 10 2 2 0 0 2 6 2 0 0 0 0 3 0 0 0 0 1 1 */
declare @sql varchar(8000) set @sql = 'select isnull(a.商品名,''合计'') ' select @sql = @sql + ' , sum(case a.仓库名 when ''' + 仓库名 + ''' then 销售额 else 0 end) [' + 仓库名 + '仓库], sum(case a.仓库名 when ''' + 仓库名 + ''' then 库存额 else 0 end) [' + 仓库名 + '库存]' from (select distinct 仓库名 from 主表) as a set @sql = @sql + ' from 主表 a inner join 仓库表 b on a.仓库id =b.仓库id group by a.商品名 with rollup' exec(@sql)
-->TravyLee生成测试数据:[主表] if object_id('[主表]') is not null drop table [主表] create table [主表]( [商品名] int, [仓库名] varchar(2), [仓库id] int, [销售额] int, [库存额] int ) insert [主表] select 1,'S1',111,2,5 union all select 2,'S1',111,3,1 union all select 1,'S2',222,2,2 union all select 3,'S3',333,1,1 --> 测试数据:[仓库表] if object_id('[仓库表]') is not null drop table [仓库表] create table [仓库表]( [仓库名] varchar(2), [仓库id] int ) insert [仓库表] select 'S1',111 union all select 'S1',111 union all select 'S2',222 union all select 'S3',333 union all select 'S4',444select a.商品名,b.仓库名,a.销售额,a.库存额 into #test from [主表] a left join [仓库表] b on a.仓库id=b.仓库iddeclare @str varchar(max) set @str='' select @str=@str+','+仓库名+'销售'+'=sum(case when 仓库名=' +QUOTENAME(仓库名,'''')+' then 销售额 else 0 end),' +仓库名+'库存'+'=sum(case when 仓库名='+QUOTENAME(仓库名,'''') +' then 库存额 else 0 end)' from #test group by 仓库名 exec('select 商品名'+@str+' from #test group by 商品名 with rollup')/* 商品名 S1销售 S1库存 S2销售 S2库存 S3销售 S3库存 1 4 10 2 2 0 0 2 6 2 0 0 0 0 3 0 0 0 0 1 1 NULL 10 12 2 2 1 1 */
---------------------------------------------------------------- -- Author :fredrickhu(小F,向高手学习) -- Date :2012-05-30 11:56:27 -- Version: -- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86) -- Apr 22 2011 11:57:00 -- Copyright (c) Microsoft Corporation -- Enterprise Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64) -- ---------------------------------------------------------------- --> 测试数据:[主表] if object_id('[主表]') is not null drop table [主表] go create table [主表]([商品名] int,[仓库名] varchar(2),[仓库id] int,[销售额] int,[库存额] int) insert [主表] select 1,'S1',111,2,5 union all select 2,'S1',111,3,1 union all select 1,'S2',222,2,2 union all select 3,'S3',333,1,1 --> 测试数据:[仓库表] if object_id('[仓库表]') is not null drop table [仓库表] go create table [仓库表]([仓库名] varchar(2),[仓库id] int) insert [仓库表] select 'S1',111 union all select 'S1',111 union all select 'S2',222 union all select 'S3',333 union all select 'S4',444 --------------开始查询-------------------------- declare @sql varchar(8000) set @sql = 'select isnull(ltrim(a.商品名),''合计'') as 商品名 ' select @sql = @sql + ' , sum(case a.仓库名 when ''' + 仓库名 + ''' then 销售额 else 0 end) [' + 仓库名 + '仓库], sum(case a.仓库名 when ''' + 仓库名 + ''' then 库存额 else 0 end) [' + 仓库名 + '库存]' from (select distinct 仓库名 from 主表) as a set @sql = @sql + ' from 主表 a inner join 仓库表 b on a.仓库id =b.仓库id group by a.商品名 with rollup' exec(@sql) ----------------结果---------------------------- /* (4 行受影响)(5 行受影响) 商品名 S1仓库 S1库存 S2仓库 S2库存 S3仓库 S3库存 ------------ ----------- ----------- ----------- ----------- ----------- ----------- 1 4 10 2 2 0 0 2 6 2 0 0 0 0 3 0 0 0 0 1 1 合计 10 12 2 2 1 1(4 行受影响) */
a.商品名,
sum(case a.仓库名 when 's1' then 销售额 else 0 end) as S1销售,
sum(case a.仓库名 when 's1' then 库存额 else 0 end) as S1库存,
....
from
主表 a join 仓库表 b
on
a.仓库id =b.仓库id
商品名 S1销售 S1库存 S2销售 S2库存 S3销售 S3库存 S4销售 S4库存
1 2 5 2 2 0 0 0 0
2 3 1 0 0 0 0 0 0
3 0 0 0 0 1 1 0 0
合计 5 6 2 2 1 1 0 0
-------------------------------------------------------------------
declare cur cursor for
Select FItemID,FName from t_stock where Fnumber Like '00.%'
Open Cur
Fetch cur into @Fstockid,@fstockName
While @@fetch_status=0
BEGINSet @sql= 'Alter table #tmp200 add ['+@Fstockname +'库存] INT'
exec(@sql)
Set @sql= 'Alter table #tmp2001 add ['+@Fstockname +'库存] INT'
exec(@sql)
Set @sqlOrd = @sqlord +', case when s2.FstockID=' + @Fstockid+' then SUM(s2.FInSecQty) End'
Set @sqlbegin = @sqlbegin +', ['+@Fstockname +'库存]'
Set @Sale= @Sale + 'isnull([' +@Fstockname+'库存],0)+'
Set @sqlOut = @sqlbegin + ')'+@sqlOrd +' from #Happen1 S2 Inner Join t_ICITEM t
ON s2.FItemID=t.FItemID group by s2.FstockID,t.Fname'Fetch cur into @Fstockid,@fstockName
END
close cur
deallocate curexec(@sqlout)
-------------------------------------------
这是我用游标写出来的,但是在最后的输出结果里是类似这么一种情况
商品名 S1销售 S1库存 S2销售 S2库存 S3销售 S3库存 S4销售 S4库存
1 2 5 2 2 0 0 0 0
1 null null 2 2 0 0 0 0
2 3 1 0 0 0 0 0 0
3 0 0 0 0 1 1 0 0
合计 5 6 2 2 1 1 0 0
------------------------------------
为什么商品一不是以一行显示. 我用的是sql2005.
商品名 S1销售 S1库存 S2销售 S2库存 S3销售 S3库存 S4销售 S4库存
1 2 5 0 0 0 0 0 0
1 0 0 2 2 0 0 0 0
2 3 1 0 0 0 0 0 0
3 0 0 0 0 1 1 0 0
合计 5 6 2 2 1 1 0 0
------------------------------------
-->TravyLee生成测试数据:[主表]
if object_id('[主表]') is not null drop table [主表]
create table [主表](
[商品名] int,
[仓库名] varchar(2),
[仓库id] int,
[销售额] int,
[库存额] int
)
insert [主表]
select 1,'S1',111,2,5 union all
select 2,'S1',111,3,1 union all
select 1,'S2',222,2,2 union all
select 3,'S3',333,1,1
--> 测试数据:[仓库表]
if object_id('[仓库表]') is not null
drop table [仓库表]
create table [仓库表](
[仓库名] varchar(2),
[仓库id] int
)
insert [仓库表]
select 'S1',111 union all
select 'S1',111 union all
select 'S2',222 union all
select 'S3',333 union all
select 'S4',444select a.商品名,b.仓库名,a.销售额,a.库存额 into #test
from [主表] a
left join [仓库表] b
on a.仓库id=b.仓库iddeclare @str varchar(max)
set @str=''
select
@str=@str+','+仓库名+'销售'+'=sum(case when 仓库名='
+QUOTENAME(仓库名,'''')+' then 销售额 else 0 end),'
+仓库名+'库存'+'=sum(case when 仓库名='+QUOTENAME(仓库名,'''')
+' then 库存额 else 0 end)'
from
#test
group by
仓库名
exec('select 商品名'+@str+' from #test group by 商品名')/*
商品名 S1销售 S1库存 S2销售 S2库存 S3销售 S3库存
----------------------------------------------------------------
1 4 10 2 2 0 0
2 6 2 0 0 0 0
3 0 0 0 0 1 1
*/
set @sql = 'select isnull(a.商品名,''合计'') '
select @sql = @sql + ' , sum(case a.仓库名 when ''' + 仓库名 + ''' then 销售额 else 0 end) [' + 仓库名 + '仓库],
sum(case a.仓库名 when ''' + 仓库名 + ''' then 库存额 else 0 end) [' + 仓库名 + '库存]'
from (select distinct 仓库名 from 主表) as a
set @sql = @sql + ' from 主表 a inner join 仓库表 b on a.仓库id =b.仓库id group by a.商品名 with rollup'
exec(@sql)
-->TravyLee生成测试数据:[主表]
if object_id('[主表]') is not null drop table [主表]
create table [主表](
[商品名] int,
[仓库名] varchar(2),
[仓库id] int,
[销售额] int,
[库存额] int
)
insert [主表]
select 1,'S1',111,2,5 union all
select 2,'S1',111,3,1 union all
select 1,'S2',222,2,2 union all
select 3,'S3',333,1,1
--> 测试数据:[仓库表]
if object_id('[仓库表]') is not null
drop table [仓库表]
create table [仓库表](
[仓库名] varchar(2),
[仓库id] int
)
insert [仓库表]
select 'S1',111 union all
select 'S1',111 union all
select 'S2',222 union all
select 'S3',333 union all
select 'S4',444select a.商品名,b.仓库名,a.销售额,a.库存额 into #test
from [主表] a
left join [仓库表] b
on a.仓库id=b.仓库iddeclare @str varchar(max)
set @str=''
select
@str=@str+','+仓库名+'销售'+'=sum(case when 仓库名='
+QUOTENAME(仓库名,'''')+' then 销售额 else 0 end),'
+仓库名+'库存'+'=sum(case when 仓库名='+QUOTENAME(仓库名,'''')
+' then 库存额 else 0 end)'
from
#test
group by
仓库名
exec('select 商品名'+@str+' from #test group by 商品名 with rollup')/*
商品名 S1销售 S1库存 S2销售 S2库存 S3销售 S3库存
1 4 10 2 2 0 0
2 6 2 0 0 0 0
3 0 0 0 0 1 1
NULL 10 12 2 2 1 1
*/
-- Author :fredrickhu(小F,向高手学习)
-- Date :2012-05-30 11:56:27
-- Version:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86)
-- Apr 22 2011 11:57:00
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)
--
----------------------------------------------------------------
--> 测试数据:[主表]
if object_id('[主表]') is not null drop table [主表]
go
create table [主表]([商品名] int,[仓库名] varchar(2),[仓库id] int,[销售额] int,[库存额] int)
insert [主表]
select 1,'S1',111,2,5 union all
select 2,'S1',111,3,1 union all
select 1,'S2',222,2,2 union all
select 3,'S3',333,1,1
--> 测试数据:[仓库表]
if object_id('[仓库表]') is not null drop table [仓库表]
go
create table [仓库表]([仓库名] varchar(2),[仓库id] int)
insert [仓库表]
select 'S1',111 union all
select 'S1',111 union all
select 'S2',222 union all
select 'S3',333 union all
select 'S4',444
--------------开始查询--------------------------
declare @sql varchar(8000)
set @sql = 'select isnull(ltrim(a.商品名),''合计'') as 商品名 '
select @sql = @sql + ' , sum(case a.仓库名 when ''' + 仓库名 + ''' then 销售额 else 0 end) [' + 仓库名 + '仓库],
sum(case a.仓库名 when ''' + 仓库名 + ''' then 库存额 else 0 end) [' + 仓库名 + '库存]'
from (select distinct 仓库名 from 主表) as a
set @sql = @sql + ' from 主表 a inner join 仓库表 b on a.仓库id =b.仓库id group by a.商品名 with rollup'
exec(@sql) ----------------结果----------------------------
/*
(4 行受影响)(5 行受影响)
商品名 S1仓库 S1库存 S2仓库 S2库存 S3仓库 S3库存
------------ ----------- ----------- ----------- ----------- ----------- -----------
1 4 10 2 2 0 0
2 6 2 0 0 0 0
3 0 0 0 0 1 1
合计 10 12 2 2 1 1(4 行受影响)
*/
顺便问一句,能推荐几本数据库类的书籍吗?买了一本看了看感觉对我现在的这些表操作没什么帮助.
谢了!
你的第一个仓库的销售额和库存额貌似有问题,都是乘以了2的结果.......