27,580
社区成员
发帖
与我相关
我的任务
分享纵向横向求和！
数据库共有3张表,分别如下:
表1
Materialid Stockid Qty
(物料代码) （仓库代码） （数量）
101 501 100
101 502 120
101 503 130
102 601 160
102 602 200
……
表2
Fid（索引号） stockid(仓库代码) stockname
1 501 材料仓A
2 502 材料仓B
3 503 材料仓c
4 601 成品仓A
5 602 成品仓B
……
表3
Fid （索引号） Materialid (物料代码) Materialname
1 101 电阻
2 102 打印机
……
附注：
表2、表3都会分别随时增加新的物料，仓库
要求最后查询结果如下：
Materialid 材料仓A 材料仓B 材料仓c 成品仓A 成品仓B 数量合计
101 100 120 130 350
102 160 200 360
合计 100 120 130 160 200
--谢谢！
表1
Materialid Stockid Qty
(物料代码) （仓库代码） （数量）
101 501 100
101 502 120
101 503 130
102 601 160
102 602 200
……
表2
Fid（索引号） stockid(仓库代码) stockname
1 501 材料仓A
2 502 材料仓B
3 503 材料仓c
4 601 成品仓A
5 602 成品仓B
……
表3
Fid （索引号） Materialid (物料代码) Materialname
1 101 电阻
2 102 打印机
……
附注：
表2、表3都会分别随时增加新的物料，仓库
要求最后查询结果如下：
Materialid 材料仓A 材料仓B 材料仓c 成品仓A 成品仓B 数量合计
101 100 120 130 350
102 160 200 360
合计 100 120 130 160 200
--谢谢！
...全文
请发表友善的回复…
发表回复
中国风 2007-12-03
- 打赏
- 举报
create table t1 (Materialid int,Stockid int,Qty int)
insert t1 select 101,501 , 100
insert t1 select 101 , 502 , 120
insert t1 select 101 , 503 , 130
insert t1 select 102 , 601 , 160
insert t1 select 102 , 602 , 200
create table t2 (Fid int,stockid int,stockname varchar(10))
insert t2 select 1,501,'材料仓A'
insert t2 select 2,502,'材料仓B'
insert t2 select 3,503,'材料仓c'
insert t2 select 4,601,'成品仓A'
insert t2 select 5,602,'成品仓B'
create table t3 (Fid int,Materialid int,Materialname varchar(10))
insert t3 select 1,101, '电阻'
insert t3 select 2,102,'打印机 '
go
declare @s nvarchar(4000)
set @s='select [Materialname]=isnull(t3.Materialname,''合计'')'
select @s=@s+','+quotename(stockname)+'=sum(case when Stockid='+quotename(stockid,'''')+' then Qty else 0 end)'
from T2
group by stockname,stockid
set @s=@s+',[数量合计]=sum(Qty) from T1 join T3 on t1.Materialid=t3.Materialid group by t3.Materialname with rollup'
exec(@s)
/*
Materialname 材料仓A 材料仓B 材料仓c 成品仓A 成品仓B 数量合计
------------ ----------- ----------- ----------- ----------- ----------- -----------
打印机 0 0 0 160 200 360
电阻 100 120 130 0 0 350
合计 100 120 130 160 200 710
*/
--生成的语句如下:
select
[Materialname]=isnull(t3.Materialname,'合计'),
[材料仓A]=sum(case when Stockid='501' then Qty else 0 end),
[材料仓B]=sum(case when Stockid='502' then Qty else 0 end),
[材料仓c]=sum(case when Stockid='503' then Qty else 0 end),
[成品仓A]=sum(case when Stockid='601' then Qty else 0 end),
[成品仓B]=sum(case when Stockid='602' then Qty else 0 end),
[数量合计]=sum(Qty)
from
T1
join
T3 on t1.Materialid=t3.Materialid
group by t3.Materialname with rollup
-狙击手- 2007-12-03
- 打赏
- 举报
create table t1 (Materialid int,Stockid int,Qty int)
insert t1 select 101,501 , 100
insert t1 select 101 , 502 , 120
insert t1 select 101 , 503 , 130
insert t1 select 102 , 601 , 160
insert t1 select 102 , 602 , 200
create table t2 (Fid int,stockid int,stockname varchar(10))
insert t2 select 1,501,'材料仓A'
insert t2 select 2,502,'材料仓B'
insert t2 select 3,503,'材料仓c'
insert t2 select 4,601,'成品仓A'
insert t2 select 5,602,'成品仓B'
create table t3 (Fid int,Materialid int,Materialname varchar(10))
insert t3 select 1,101, '电阻'
insert t3 select 2,102,'打印机 '
declare @s varchar(8000)
set @s = 'select distinct case when grouping(a.materialid) = 1 then ''合计'' else cast(a.materialid as varchar) end as materialid'
select @s = @s+ ',['+stockname+'] = sum(case when a.stockid = '+ltrim(stockid)+' then a.qty else 0 end)'
from (select distinct top 100 stockid,stockname from t2 order by stockid ) a
exec ( @s+',sum(a.Qty) as total from t1 a,t3 c where a.materialid = c.materialid
group by a.materialid,c.materialname with rollup')
drop table t1,t2,t3
/*
materialid 材料仓A 材料仓B 材料仓c 成品仓A 成品仓B total
------------------------------ ----------- ----------- ----------- ----------- ----------- -----------
101 100 120 130 0 0 350
102 0 0 0 160 200 360
合计 100 120 130 160 200 710
*/-狙击手- 2007-12-03
- 打赏
- 举报
create table t1 (Materialid int,Stockid int,Qty int)
insert t1 select 101,501 , 100
insert t1 select 101 , 502 , 120
insert t1 select 101 , 503 , 130
insert t1 select 102 , 601 , 160
insert t1 select 102 , 602 , 200
create table t2 (Fid int,stockid int,stockname varchar(10))
insert t2 select 1,501,'材料仓A'
insert t2 select 2,502,'材料仓B'
insert t2 select 3,503,'材料仓c'
insert t2 select 4,601,'成品仓A'
insert t2 select 5,602,'成品仓B'
create table t3 (Fid int,Materialid int,Materialname varchar(10))
insert t3 select 1,101, '电阻'
insert t3 select 2,102,'打印机 '
declare @s varchar(8000)
set @s = 'select distinct case when grouping(a.materialid) = 1 then ''all'' else cast(a.materialid as varchar) end as materialid'
select @s = @s+ ',['+stockname+'] = sum(case when a.stockid = '+ltrim(stockid)+' then a.qty else 0 end)'
from (select distinct top 100 stockid,stockname from t2 order by stockid ) a
exec ( @s+',sum(a.Qty) as total from t1 a,t3 c where a.materialid = c.materialid
group by a.materialid,c.materialname with rollup')
drop table t1,t2,t3
/*
materialid 材料仓A 材料仓B 材料仓c 成品仓A 成品仓B total
------------------------------ ----------- ----------- ----------- ----------- ----------- -----------
101 100 120 130 0 0 350
102 0 0 0 160 200 360
all 100 120 130 160 200 710
*/
dawugui 2007-12-03
- 打赏
- 举报
表2、表3都会分别随时增加新的物料，仓库
那就得用动态SQL了.看我整理的行列转换中的相关内容:
那就得用动态SQL了.看我整理的行列转换中的相关内容:
/*
普通行列转换(2007-11-18于海南三亚)
假设有张学生成绩表(tb)如下:
Name Subject Result
张三 语文 74
张三 数学 83
张三 物理 93
李四 语文 74
李四 数学 84
李四 物理 94
*/
-------------------------------------------------------------------------
/*
想变成
姓名 语文 数学 物理
---------- ----------- ----------- -----------
李四 74 84 94
张三 74 83 93
*/
create table tb
(
Name varchar(10) ,
Subject varchar(10) ,
Result int
)
insert into tb(Name , Subject , Result) values('张三' , '语文' , 74)
insert into tb(Name , Subject , Result) values('张三' , '数学' , 83)
insert into tb(Name , Subject , Result) values('张三' , '物理' , 93)
insert into tb(Name , Subject , Result) values('李四' , '语文' , 74)
insert into tb(Name , Subject , Result) values('李四' , '数学' , 84)
insert into tb(Name , Subject , Result) values('李四' , '物理' , 94)
go
--静态SQL,指subject只有语文、数学、物理这三门课程。
select name 姓名,
max(case subject when '语文' then result else 0 end) 语文,
max(case subject when '数学' then result else 0 end) 数学,
max(case subject when '物理' then result else 0 end) 物理
from tb
group by name
/*
姓名 语文 数学 物理
---------- ----------- ----------- -----------
李四 74 84 94
张三 74 83 93
*/
--动态SQL,指subject不止语文、数学、物理这三门课程。
declare @sql varchar(8000)
set @sql = 'select Name as ' + '姓名'
select @sql = @sql + ' , max(case Subject when ''' + Subject + ''' then Result else 0 end) [' + Subject + ']'
from (select distinct Subject from tb) as a
set @sql = @sql + ' from tb group by name'
exec(@sql)
/*
姓名 数学 物理 语文
---------- ----------- ----------- -----------
李四 84 94 74
张三 83 93 74
*/
-------------------------------------------------------------------
/*加个平均分，总分
姓名 语文 数学 物理 平均分 总分
---------- ----------- ----------- ----------- -------------------- -----------
李四 74 84 94 84.00 252
张三 74 83 93 83.33 250
*/
--静态SQL,指subject只有语文、数学、物理这三门课程。
select name 姓名,
max(case subject when '语文' then result else 0 end) 语文,
max(case subject when '数学' then result else 0 end) 数学,
max(case subject when '物理' then result else 0 end) 物理,
cast(avg(result*1.0) as decimal(18,2)) 平均分,
sum(result) 总分
from tb
group by name
/*
姓名 语文 数学 物理 平均分 总分
---------- ----------- ----------- ----------- -------------------- -----------
李四 74 84 94 84.00 252
张三 74 83 93 83.33 250
*/
--动态SQL,指subject不止语文、数学、物理这三门课程。
declare @sql1 varchar(8000)
set @sql1 = 'select Name as ' + '姓名'
select @sql1 = @sql1 + ' , max(case Subject when ''' + Subject + ''' then Result else 0 end) [' + Subject + ']'
from (select distinct Subject from tb) as a
set @sql1 = @sql1 + ' , cast(avg(result*1.0) as decimal(18,2)) 平均分,sum(result) 总分 from tb group by name'
exec(@sql1)
/*
姓名 数学 物理 语文 平均分 总分
---------- ----------- ----------- ----------- -------------------- -----------
李四 84 94 74 84.00 252
张三 83 93 74 83.33 250
*/
drop table tb
---------------------------------------------------------
---------------------------------------------------------
/*
如果上述两表互相换一下：即
姓名 语文 数学 物理
张三 74 83 93
李四 74 84 94
想变成
Name Subject Result
---------- ------- -----------
李四 语文 74
李四 数学 84
李四 物理 94
张三 语文 74
张三 数学 83
张三 物理 93
*/
create table tb1
(
姓名 varchar(10) ,
语文 int ,
数学 int ,
物理 int
)
insert into tb1(姓名 , 语文 , 数学 , 物理) values('张三',74,83,93)
insert into tb1(姓名 , 语文 , 数学 , 物理) values('李四',74,84,94)
select * from
(
select 姓名 as Name , Subject = '语文' , Result = 语文 from tb1
union all
select 姓名 as Name , Subject = '数学' , Result = 数学 from tb1
union all
select 姓名 as Name , Subject = '物理' , Result = 物理 from tb1
) t
order by name , case Subject when '语文' then 1 when '数学' then 2 when '物理' then 3 when '总分' then 4 end
--------------------------------------------------------------------
/*加个平均分,总分
Name Subject Result
---------- ------- --------------------
李四 语文 74.00
李四 数学 84.00
李四 物理 94.00
李四 平均分 84.00
李四 总分 252.00
张三 语文 74.00
张三 数学 83.00
张三 物理 93.00
张三 平均分 83.33
张三 总分 250.00
*/
select * from
(
select 姓名 as Name , Subject = '语文' , Result = 语文 from tb1
union all
select 姓名 as Name , Subject = '数学' , Result = 数学 from tb1
union all
select 姓名 as Name , Subject = '物理' , Result = 物理 from tb1
union all
select 姓名 as Name , Subject = '平均分' , Result = cast((语文 + 数学 + 物理)*1.0/3 as decimal(18,2)) from tb1
union all
select 姓名 as Name , Subject = '总分' , Result = 语文 + 数学 + 物理 from tb1
) t
order by name , case Subject when '语文' then 1 when '数学' then 2 when '物理' then 3 when '平均分' then 4 when '总分' then 5 end
drop table tb1-狙击手- 2007-12-03
- 打赏
- 举报
with rollup
