/*
有4张表:合同表、货物表、加工表、加工项目表(基础资料)
意思是:一个合同有多个货物,每一个货物又有多个加工项,
第4张表,加工项目表(基础资料),数据是固定的,FID、FName是不会变的,01焊接,02打捆,我要将这个表的数据行当成是列,最终想要的结果是:[合同] [物品] [焊接数] [打捆数] [刷油数] [运输数]
合同1 铁皮 10 5 0 0
合同1 钢板 0 0 100 0
合同2 汽车 0 0 0 10
合同2 自行车 0 0 0 8
合同2 电动车 0 0 0 20如果不好实现,可以考虑将第4张表看成是个常量,就4条数据,(01焊接,02打捆,03刷油,04运输)
*/--1、合同表,主表
CREATE TABLE [dbo].[Contract] (
[FNo] [varchar] (4) NOT NULL , --合同号
[FContractName] [varchar] (50) NOT NULL --合同名称
) ON [PRIMARY]
insert into Contract values ('HT01','合同1')
insert into Contract values ('HT02','合同2')
--2、货物表,从表
CREATE TABLE [dbo].[Goods] (
[FID] [varchar] (4) NOT NULL , --编号
[FNO] [varchar] (4) NOT NULL , --外键,与合同表的 FNo 关联
[FGoods] [varchar] (50) NOT NULL --货物名称
) ON [PRIMARY]
insert into Goods values ('0001','HT01','铁皮')
insert into Goods values ('0002','HT01','钢板')
insert into Goods values ('0003','HT02','汽车')
insert into Goods values ('0004','HT02','自行车')
insert into Goods values ('0005','HT02','电动车')
--3、加工表,从表的从表
CREATE TABLE [dbo].[Process] (
[FID] [int] IDENTITY (1, 1) NOT NULL ,
[FParentID] [varchar] (4) NOT NULL , --外键,与货物表的 FID 关联
[FProcessItem] [varchar] (50) NOT NULL , --加工项,存的是加工项目表(基础资料)的 FID
[FQuantity] [int] NOT NULL --数量
) ON [PRIMARY]
insert into Process values ('0001','01',10)
insert into Process values ('0001','02',5)
insert into Process values ('0002','03',100)
insert into Process values ('0003','04',10)
insert into Process values ('0004','04',8)
insert into Process values ('0005','04',20)
--4、加工项目表(基础资料)
CREATE TABLE [dbo].[ProcessItems] (
[FID] [varchar] (4) NOT NULL ,
[FName] [varchar] (50) NOT NULL
) ON [PRIMARY]
insert into ProcessItems values ('01','焊接')
insert into ProcessItems values ('02','打捆')
insert into ProcessItems values ('03','刷油')
insert into ProcessItems values ('04','运输')
有4张表:合同表、货物表、加工表、加工项目表(基础资料)
意思是:一个合同有多个货物,每一个货物又有多个加工项,
第4张表,加工项目表(基础资料),数据是固定的,FID、FName是不会变的,01焊接,02打捆,我要将这个表的数据行当成是列,最终想要的结果是:[合同] [物品] [焊接数] [打捆数] [刷油数] [运输数]
合同1 铁皮 10 5 0 0
合同1 钢板 0 0 100 0
合同2 汽车 0 0 0 10
合同2 自行车 0 0 0 8
合同2 电动车 0 0 0 20如果不好实现,可以考虑将第4张表看成是个常量,就4条数据,(01焊接,02打捆,03刷油,04运输)
*/--1、合同表,主表
CREATE TABLE [dbo].[Contract] (
[FNo] [varchar] (4) NOT NULL , --合同号
[FContractName] [varchar] (50) NOT NULL --合同名称
) ON [PRIMARY]
insert into Contract values ('HT01','合同1')
insert into Contract values ('HT02','合同2')
--2、货物表,从表
CREATE TABLE [dbo].[Goods] (
[FID] [varchar] (4) NOT NULL , --编号
[FNO] [varchar] (4) NOT NULL , --外键,与合同表的 FNo 关联
[FGoods] [varchar] (50) NOT NULL --货物名称
) ON [PRIMARY]
insert into Goods values ('0001','HT01','铁皮')
insert into Goods values ('0002','HT01','钢板')
insert into Goods values ('0003','HT02','汽车')
insert into Goods values ('0004','HT02','自行车')
insert into Goods values ('0005','HT02','电动车')
--3、加工表,从表的从表
CREATE TABLE [dbo].[Process] (
[FID] [int] IDENTITY (1, 1) NOT NULL ,
[FParentID] [varchar] (4) NOT NULL , --外键,与货物表的 FID 关联
[FProcessItem] [varchar] (50) NOT NULL , --加工项,存的是加工项目表(基础资料)的 FID
[FQuantity] [int] NOT NULL --数量
) ON [PRIMARY]
insert into Process values ('0001','01',10)
insert into Process values ('0001','02',5)
insert into Process values ('0002','03',100)
insert into Process values ('0003','04',10)
insert into Process values ('0004','04',8)
insert into Process values ('0005','04',20)
--4、加工项目表(基础资料)
CREATE TABLE [dbo].[ProcessItems] (
[FID] [varchar] (4) NOT NULL ,
[FName] [varchar] (50) NOT NULL
) ON [PRIMARY]
insert into ProcessItems values ('01','焊接')
insert into ProcessItems values ('02','打捆')
insert into ProcessItems values ('03','刷油')
insert into ProcessItems values ('04','运输')
解决方案 »
- 怎么查sql server的locale?
- 如何用变量代替需查询的表名?
- 如何彻底删除表
- SQL server数据库不断增大的长久维护办法
- 关于几点优化经验的问题,我想不痛为什么,请高手举例论证此观点,谢谢~
- sql server management studio 在"关系图"窗格和"条件"窗格中无法用图形方法表示查询
- 解决立刻给分(在线等):多步 OLE DB 操作产生错误。请检查每个 OLE DB 状态值。没有工作被完成。这个错误是怎么回事?
- 为什么不能创建关系
- 求SQL统计查询加速方法
- sql server7中UPDATE触发器怎么写?
- 存储过程不知道出错在哪?
- 求一句存储过程中的时间判断
--1、合同表,主表
CREATE TABLE [dbo].[Contract] (
[FNo] [varchar] (4) NOT NULL , --合同号
[FContractName] [varchar] (50) NOT NULL --合同名称
) ON [PRIMARY]
insert into Contract values ('HT01','合同1')
insert into Contract values ('HT02','合同2')
--2、货物表,从表
CREATE TABLE [dbo].[Goods] (
[FID] [varchar] (4) NOT NULL , --编号
[FNO] [varchar] (4) NOT NULL , --外键,与合同表的 FNo 关联
[FGoods] [varchar] (50) NOT NULL --货物名称
) ON [PRIMARY]
insert into Goods values ('0001','HT01','铁皮')
insert into Goods values ('0002','HT01','钢板')
insert into Goods values ('0003','HT02','汽车')
insert into Goods values ('0004','HT02','自行车')
insert into Goods values ('0005','HT02','电动车')
--3、加工表,从表的从表
CREATE TABLE [dbo].[Process] (
[FID] [int] IDENTITY (1, 1) NOT NULL ,
[FParentID] [varchar] (4) NOT NULL , --外键,与货物表的 FID 关联
[FProcessItem] [varchar] (50) NOT NULL , --加工项,存的是加工项目表(基础资料)的 FID
[FQuantity] [int] NOT NULL --数量
) ON [PRIMARY]
insert into Process values ('0001','01',10)
insert into Process values ('0001','02',5)
insert into Process values ('0002','03',100)
insert into Process values ('0003','04',10)
insert into Process values ('0004','04',8)
insert into Process values ('0005','04',20)
--4、加工项目表(基础资料)
CREATE TABLE [dbo].[ProcessItems] (
[FID] [varchar] (4) NOT NULL ,
[FName] [varchar] (50) NOT NULL
) ON [PRIMARY]
insert into ProcessItems values ('01','焊接')
insert into ProcessItems values ('02','打捆')
insert into ProcessItems values ('03','刷油')
insert into ProcessItems values ('04','运输')
goselect a.[FContractName],b.[FGoods],
sum(case when c.[FProcessItem] = '01' then c.[FQuantity] else 0 end) as [焊接],
sum(case when c.[FProcessItem] = '02' then c.[FQuantity] else 0 end) as [打捆],
sum(case when c.[FProcessItem] = '03' then c.[FQuantity] else 0 end) as [刷油],
sum(case when c.[FProcessItem] = '04' then c.[FQuantity] else 0 end) as [运输]
from [Contract] a inner join [Goods] b on a.[FNo] = b.[FNO]
inner join [Process] c on b.[FID] = c.[FParentID]
group by a.[FContractName],b.[FGoods]drop table [Contract],[Goods],[Process],[ProcessItems]/*FContractName FGoods 焊接 打捆 刷油 运输
-------------------------------------------------- -------------------------------------------------- ----------- ----------- ----------- -----------
合同1 钢板 0 0 100 0
合同1 铁皮 10 5 0 0
合同2 电动车 0 0 0 20
合同2 汽车 0 0 0 10
合同2 自行车 0 0 0 8(5 行受影响)
--动态拼接set nocount on
--1、合同表,主表
CREATE TABLE [dbo].[Contract] (
[FNo] [varchar] (4) NOT NULL , --合同号
[FContractName] [varchar] (50) NOT NULL --合同名称
) ON [PRIMARY]
insert into Contract values ('HT01','合同1')
insert into Contract values ('HT02','合同2')
--2、货物表,从表
CREATE TABLE [dbo].[Goods] (
[FID] [varchar] (4) NOT NULL , --编号
[FNO] [varchar] (4) NOT NULL , --外键,与合同表的 FNo 关联
[FGoods] [varchar] (50) NOT NULL --货物名称
) ON [PRIMARY]
insert into Goods values ('0001','HT01','铁皮')
insert into Goods values ('0002','HT01','钢板')
insert into Goods values ('0003','HT02','汽车')
insert into Goods values ('0004','HT02','自行车')
insert into Goods values ('0005','HT02','电动车')
--3、加工表,从表的从表
CREATE TABLE [dbo].[Process] (
[FID] [int] IDENTITY (1, 1) NOT NULL ,
[FParentID] [varchar] (4) NOT NULL , --外键,与货物表的 FID 关联
[FProcessItem] [varchar] (50) NOT NULL , --加工项,存的是加工项目表(基础资料)的 FID
[FQuantity] [int] NOT NULL --数量
) ON [PRIMARY]
insert into Process values ('0001','01',10)
insert into Process values ('0001','02',5)
insert into Process values ('0002','03',100)
insert into Process values ('0003','04',10)
insert into Process values ('0004','04',8)
insert into Process values ('0005','04',20)
--4、加工项目表(基础资料)
CREATE TABLE [dbo].[ProcessItems] (
[FID] [varchar] (4) NOT NULL ,
[FName] [varchar] (50) NOT NULL
) ON [PRIMARY]
insert into ProcessItems values ('01','焊接')
insert into ProcessItems values ('02','打捆')
insert into ProcessItems values ('03','刷油')
insert into ProcessItems values ('04','运输')
go
set nocount offdeclare @sql varchar(max) -- 2000可以用varchar(4000)
set @sql = 'select a.[FContractName],b.[FGoods]'
select @sql = @sql + ',sum(case c.[FProcessItem] when ''' + [FID] + ''' then c.[FQuantity] else 0 end)[' + [FName] + '数]'
from [ProcessItems]
select @sql = @sql + ' from [Contract] a inner join [Goods] b on a.[FNo] = b.[FNO]
inner join [Process] c on b.[FID] = c.[FParentID] group by a.[FContractName],b.[FGoods] '
exec(@sql)drop table [Contract],[Goods],[Process],[ProcessItems]
/*FContractName FGoods 焊接数 打捆数 刷油数 运输数
-------------------------------------------------- -------------------------------------------------- ----------- ----------- ----------- -----------
合同1 钢板 0 0 100 0
合同1 铁皮 10 5 0 0
合同2 电动车 0 0 0 20
合同2 汽车 0 0 0 10
合同2 自行车 0 0 0 8(5 行受影响)
谢谢啊,好像问题不大,不过有个问题:HT03,合同3,货物是奶茶,但是没有加工项,就不显示了
你可以将里面的两个inner join改为left join试试,或者改第二个为left join就可以了。
好了,我光注意 sum case 那部分了,忘记了 inner join 和 left join 了,呵呵,谢谢啊,分都给你了,应该没有什么问题