tableA(物资编码表)
id name price
101 计算机配件
1011111 U盘 100.00
1012222 移动硬盘 200.00
102 办公用品
1021111 水笔 1.00
1022222 记事本 2.00
1023333 剪刀 5.00
......tableB(领用单位表)
id 单位名称
201 电工班
202 钳工班
203 物料班
......tableC(领料明细表)
领料单号 tableAid 领料数量 单价 tableBid
1 1011111 2 100.00 201
2 1012222 2 200.00 201
3 1021111 2 1.00 201
4 1021111 2 1.00 202
5 1021111 2 1.00 203
6 1023333 2 5.00 203想要的结果
单位/类别 计算机配件 办公用品 .... 管理费 合计
电工班 600.00 2 30.1 632.1
钳工班 2 0.1 2.1
物料班 12 0.6 12.6
.....说明:
1、结果表中“计算机配件”、“办公用品”等类别来自A表中name，只要是101开头的都属于计算机配件；102开头的属于办公用品...
2、管理费是所有类别的金额小计*5%。
3、类别与单位都是动态的，根据领料情况自动增减。
4、关于领料明细中的单价问题，这里记录的是实际发料的单价，可能涉及折扣等问题，这里暂不作考虑，就以原价为单价。
id name price
101 计算机配件
1011111 U盘 100.00
1012222 移动硬盘 200.00
102 办公用品
1021111 水笔 1.00
1022222 记事本 2.00
1023333 剪刀 5.00
......tableB(领用单位表)
id 单位名称
201 电工班
202 钳工班
203 物料班
......tableC(领料明细表)
领料单号 tableAid 领料数量 单价 tableBid
1 1011111 2 100.00 201
2 1012222 2 200.00 201
3 1021111 2 1.00 201
4 1021111 2 1.00 202
5 1021111 2 1.00 203
6 1023333 2 5.00 203想要的结果
单位/类别 计算机配件 办公用品 .... 管理费 合计
电工班 600.00 2 30.1 632.1
钳工班 2 0.1 2.1
物料班 12 0.6 12.6
.....说明:
1、结果表中“计算机配件”、“办公用品”等类别来自A表中name，只要是101开头的都属于计算机配件；102开头的属于办公用品...
2、管理费是所有类别的金额小计*5%。
3、类别与单位都是动态的，根据领料情况自动增减。
4、关于领料明细中的单价问题，这里记录的是实际发料的单价，可能涉及折扣等问题，这里暂不作考虑，就以原价为单价。
select *,费用=（select sum(price) from tba where id like '%'+t.id)
from tba t
2、进行行列转换
if object_id('tableA') is not null drop table tableA
create table tableA (id int,name varchar(10),price numeric(5,2))
insert into tableA
select 101,'计算机配件',null union all
select 1011111,'U盘',100.00 union all
select 1012222,'移动硬盘',200.00 union all
select 102,'办公用品',null union all
select 1021111,'水笔',1.00 union all
select 1022222,'记事本',2.00 union all
select 1023333,'剪刀',5.00
--> 测试数据: tableB
if object_id('tableB') is not null drop table tableB
create table tableB (id int,单位名称 varchar(6))
insert into tableB
select 201,'电工班' union all
select 202,'钳工班' union all
select 203,'物料班'
--> 测试数据: tableC
if object_id('tableC') is not null drop table tableC
create table tableC (领料单号 int,tableAid int,领料数量 int,单价 numeric(5,2),tableBid int)
insert into tableC
select 1,1011111,2,100.00,201 union all
select 2,1012222,2,200.00,201 union all
select 3,1021111,2,1.00,201 union all
select 4,1021111,2,1.00,202 union all
select 5,1021111,2,1.00,203 union all
select 6,1023333,2,5.00,203
godeclare @sql varchar(max)
set @sql='select [单位/类别]=单位名称'
select @sql=@sql+',['+name+']=sum(case when tableaid like '''+ltrim(id)+'%'' then 领料数量*单价 else 0 end)'
from tablea where isnull(price,0)=0
set @sql=@sql+',管理费=sum(领料数量*单价)*0.05,合计=sum(领料数量*单价)+sum(领料数量*单价)*0.05 from tablec c,tableb b where b.id=c.tablebid group by b.单位名称'
exec(@sql)
--结果：
单位/类别 计算机配件 办公用品 管理费 合计
------ --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
电工班 600.00 2.00 30.1000 632.10
钳工班 0.00 2.00 0.1000 2.10
物料班 0.00 12.00 0.6000 12.60
if object_id('[tableA]') is not null drop table [tableA]
go
create table [tableA]([id] int,[name] varchar(10),[price] numeric(5,2))
insert [tableA]
select 101,'计算机配件',null union all
select 1011111,'U盘',100.00 union all
select 1012222,'移动硬盘',200.00 union all
select 102,'办公用品',null union all
select 1021111,'水笔',1.00 union all
select 1022222,'记事本',2.00 union all
select 1023333,'剪刀',5.00
if object_id('[tableB]') is not null drop table [tableB]
go
create table [tableB]([id] int,[单位名称] varchar(6))
insert [tableB]
select 201,'电工班' union all
select 202,'钳工班' union all
select 203,'物料班'
if object_id('[tableC]') is not null drop table [tableC]
go
create table [tableC]([领料单号] int,[tableAid] int,[领料数量] int,[单价] numeric(5,2),[tableBid] int)
insert [tableC]
select 1,1011111,2,100.00,201 union all
select 2,1012222,2,200.00,201 union all
select 3,1021111,2,1.00,201 union all
select 4,1021111,2,1.00,202 union all
select 5,1021111,2,1.00,203 union all
select 6,1023333,2,5.00,203
---查询---
declare @sql varchar(8000)
select
@sql=isnull(@sql+',','')
+'sum(case when a.name='''+name+''' then c.领料数量*c.单价 else 0 end) as ['+name+']'
from
(select distinct name from tablea where len(id)=3) tset @sql='select b.单位名称 as [单位/类别],'
+@sql
+',sum(c.领料数量*c.单价)*0.05 as 管理费,sum(c.领料数量*c.单价) as 合计 '
+'from tablea a,tableb b,tablec c '
+'where len(a.id)=3 and left(c.tableAid,3)=a.id and c.tableBid=b.id '
+'group by b.单位名称'
--print @sqlexec (@sql)
---结果---
单位/类别 办公用品 计算机配件 管理费 合计
------ ---------------------------------------- ---------------------------------------- ---------------------------------------- ----------------------------------------
电工班 2.00 600.00 30.1000 602.00
钳工班 2.00 .00 .1000 2.00
物料班 12.00 .00 .6000 12.00
declare @sql varchar(8000)
select
@sql=isnull(@sql+',','')
+'sum(case when a.name='''+name+''' then c.领料数量*c.单价 else 0 end) as ['+name+']'
from
(select distinct name from tablea where len(id)=3) tset @sql='select b.单位名称 as [单位/类别],'
+@sql
+',sum(c.领料数量*c.单价)*0.05 as 管理费,sum(c.领料数量*c.单价)*1.05 as 合计 '
+'from tablea a,tableb b,tablec c '
+'where len(a.id)=3 and left(c.tableAid,3)=a.id and c.tableBid=b.id '
+'group by b.单位名称'
--print @sqlexec (@sql)/**
单位/类别 办公用品 计算机配件 管理费 合计
------ ---------------------------------------- ---------------------------------------- ---------------------------------------- ----------------------------------------
电工班 2.00 600.00 30.1000 632.1000
钳工班 2.00 .00 .1000 2.1000
物料班 12.00 .00 .6000 12.6000
**/
select
@sql=isnull(@sql+',','')
+'sum(case when a.name='''+name+''' then c.领料数量*c.单价 else 0 end) as ['+name+']'
from
(select distinct name from tablea where len(id)=3) tset @sql='select b.单位名称 as [单位/类别],'
+@sql
+',sum(c.领料数量*c.单价)*0.05 as 管理费,sum(c.领料数量*c.单价)*1.05 as 合计 '
+'from tablea a,tableb b,tablec c '
+'where len(a.id)=3 and left(c.tableAid,3)=a.id and c.tableBid=b.id '
+'group by b.单位名称'
--print @sqlexec (@sql)以上这部分算是sql语句还是存储过程,一般的SQL语句不是 select ...开头的吗?
--创建存储过程
create proc sp_wsp
as
declare @sql varchar(max)
set @sql='select [单位/类别]=单位名称'
select @sql=@sql+',['+name+']=sum(case when tableaid like '''+ltrim(id)+'%'' then 领料数量*单价 else 0 end)'
from tablea where isnull(price,0)=0
set @sql=@sql+',管理费=sum(领料数量*单价)*0.05,合计=sum(领料数量*单价)+sum(领料数量*单价)*0.05 from tablec c,tableb b where b.id=c.tablebid group by b.单位名称'
exec(@sql)
go
--调用
exec sp_wsp
因为你的类别是不固定的，所以要用动态SQL，你print出来的sql语句也是以select开头的
from tablea where isnull(price,0)=0
这句话中like '''+ltrim(id)+'%'' 是什么意思,是怎么体现出按前三位分类的.
from tablea where isnull(price,0)=0
是獲取凡是price 為null則認爲當前行是大類行例如
101 计算机配件 而根據你的資料
所有小類都是在大類後面加數比如
1011111 U盘 100.00
1012222 移动硬盘 200.00 則要找出當前大類所有小就只需要
like '101%''
就可以找出
上面2行