--仓库表，bh是仓库编号，qm是仓库名称
CREATE TABLE [dbo].[chck] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[parentID] [int] NULL ,
[qm] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[bh] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
--销售表，fhck是仓库编号，bh是商品编号，sl是销售数量
CREATE TABLE [dbo].[dj_head] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[type] [nvarchar] (2) COLLATE Chinese_PRC_CI_AS NULL ,
[bh] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[fhck] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[sl] [int] NULL
) ON [PRIMARY]
GO
--商品表，bh是商品编号，sl4是总销售数量，qm是商品名称
CREATE TABLE [dbo].[kcsp] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[parentID] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[qm] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[bh] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[sl4] [int] NULL
) ON [PRIMARY]
GO
insert into [dbo].[chck](qm,bh)
values
('1号仓库',001),
('2号仓库',002),
('3号仓库',003),
('4号仓库',004),
('5号仓库',005);
insert into [dbo].[dj_head]([type],bh,fhck,sl)
values
('广东水果','0000000001',001,100),
('北方水果','0000000002',001,200),
('进口水果','0000000058',002,300),
('普通水果','0000000067',003,400);
insert into [dbo].[kcsp](qm,bh,sl4)
values
('广东水果','0000000001',100),
('北方水果','0000000002',200),
('进口水果','0000000058',300),
('普通水果','0000000067',400);
现在要的结果就是得到每件商品在每个仓库里的销售数量，仓库要横着显示？
------解决方案--------------------
CREATE procedure spxsfbb(
@beginDate varchar(20),
@endDate varchar(20),
@searchKcsp varchar(1000),
@sql varchar(2000) output
)
as
select @sql='select a.bh as 编号,a.qm as 全名,a.sl4 as 总销售数量, '
--将所有的仓库装配到sql中
select @[email protected] +'sum(case b.fhck when ''' + bh +''' then b.sl else 0 end) as '''+qm +''','
from chck where id not in (select parentID from chck)
--将From部分装配到sql
select @sql=left(@sql,len(@sql)-1)
select @[email protected]+' from kcsp a '+
' left join (select d.fhck,b.sl,b.bh from dj b left join dj_head d on d.id=b.djbh '+
' and d.ldrq between [email protected]+''' and [email protected]+''') b on a.bh=b.bh'+
' left join chck c on b.fhck=c.bh'+
' where [email protected]+''+
' group by a.bh,a.qm,a.sl4'
--print @sql
GO