--仓库表,bh是仓库编号,qm是仓库名称
CREATE TABLE [dbo].[chck] (
[id] [int] IDENTITY (1, 1) NOT 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 ,
[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 ,
[qm] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[bh] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[sl4] [int] NULL
) ON [PRIMARY]
GO现在要的结果就是得到每件商品在每个仓库里的销售数量,仓库要横着显示?
CREATE TABLE [dbo].[chck] (
[id] [int] IDENTITY (1, 1) NOT 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 ,
[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 ,
[qm] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[bh] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[sl4] [int] NULL
) ON [PRIMARY]
GO现在要的结果就是得到每件商品在每个仓库里的销售数量,仓库要横着显示?
use tempdb;
/*
--仓库表,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]
insert into [dbo].[chck](parentID,qm,bh)
values
(1,'1号仓库',1),
(1,'2号仓库',2),
(1,'3号仓库',3),
(2,'4号仓库',4),
(2,'5号仓库',5);--销售表,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
insert into [dbo].[dj_head]([type],bh,fhck,sl)
values
('水果','0000000001',1,100),
('水果','0000000002',1,200),
('水果','0000000058',2,300),
('水果','0000000067',3,400);--商品表,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].[kcsp](parentID,qm,bh,sl4)
values
(1,'广东水果','0000000001',100),
(1,'北方水果','0000000002',200),
(1,'进口水果','0000000058',300),
(1,'0000000067','0000000067',400);
*/
declare @sql varchar(8000)
set @sql = 'select [商品编号],[商品全名],[总销售数量] '
select @sql = @sql + ' , SUM(case qm when ''' + qm + ''' then [总销售数量] else 0 end) [' + qm + ']'
from (select distinct qm from (
select t2.bh as [商品编号],t3.qm as [商品全名],t3.sl4 as [总销售数量],t1.qm
from [dbo].[chck] as t1
join [dbo].[dj_head] as t2 on t1.id = t2.id
join [dbo].[kcsp] as t3 on t1.id = t3.id
) as t) as a
set @sql = @sql + ' from ('
+' select t2.bh as [商品编号],t3.qm as [商品全名],t3.sl4 as [总销售数量],t1.qm'
+' from [dbo].[chck] as t1'
+' join [dbo].[dj_head] as t2 on t1.id = t2.id'
+' join [dbo].[kcsp] as t3 on t1.id = t3.id'
+') as t group by [商品编号],[商品全名],[总销售数量]'
exec(@sql);
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);
b.bh,b.type,
sum(c.sl4) as 总销售数量
sum(case a.qm when '1号仓库' then b.sl else 0 end) as '1号仓库',
sum(case a.qm when '2号仓库' then b.sl else 0 end) as '2号仓库',
sum(case a.qm when '3号仓库' then b.sl else 0 end) as '3号仓库',
sum(case a.qm when '4号仓库' then b.sl else 0 end) as '4号仓库',
sum(case a.qm when '5号仓库' then b.sl else 0 end) as '5号仓库'
from
chck a,dj_head b,kcsp c
where
a.bh=b.fhck
and
b.bh=c.bh
and
b.type=c.qm
group by
b.bh,b.type
b.bh,c.qm,
sum(c.sl4) as 总销售数量,
sum(case a.qm when '1号仓库' then b.sl else 0 end) as '1号仓库',
sum(case a.qm when '2号仓库' then b.sl else 0 end) as '2号仓库',
sum(case a.qm when '3号仓库' then b.sl else 0 end) as '3号仓库',
sum(case a.qm when '4号仓库' then b.sl else 0 end) as '4号仓库',
sum(case a.qm when '5号仓库' then b.sl else 0 end) as '5号仓库'
from
chck a,dj_head b,kcsp c
where
a.bh=b.fhck
and
b.bh=c.bh
group by
b.bh,c.qm这样只能显示有销售数量的商品,而没有销售数量的不能显示,其实没有销售数量的我也要显示
use tempdb;
/*
CREATE TABLE [dbo].[chck] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[qm] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[bh] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
insert into [dbo].[chck](qm,bh)
values
('1号仓库','001'),
('2号仓库','002'),
('3号仓库','003'),
('4号仓库','004'),
('5号仓库','005');CREATE TABLE [dbo].[dj_head] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[type] [nvarchar] (20) 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
insert into [dbo].[dj_head]([type],bh,fhck,sl)
values
('广东水果','0000000001','001',100),
('北方水果','0000000002','001',200),
('进口水果','0000000058','002',300),
('普通水果','0000000067','003',400);CREATE TABLE [dbo].[kcsp] (
[id] [int] IDENTITY (1, 1) NOT 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].[kcsp](qm,bh,sl4)
values
('广东水果','0000000001',100),
('北方水果','0000000002',200),
('进口水果','0000000058',300),
('普通水果','0000000067',400);
*/
select
b.bh,c.qm,
sum(c.sl4) as 总销售数量,
sum(case a.qm when '1号仓库' then b.sl else 0 end) as '1号仓库',
sum(case a.qm when '2号仓库' then b.sl else 0 end) as '2号仓库',
sum(case a.qm when '3号仓库' then b.sl else 0 end) as '3号仓库',
sum(case a.qm when '4号仓库' then b.sl else 0 end) as '4号仓库',
sum(case a.qm when '5号仓库' then b.sl else 0 end) as '5号仓库'
from
chck a,dj_head b,kcsp c
where
a.bh=b.fhck
and
b.bh=c.bh
group by
b.bh,c.qm
declare @sql varchar(8000)
set @sql = 'select [商品编号],[商品全名],[总销售数量] '
select @sql = @sql + ' , SUM(case qm when ''' + qm + ''' then [总销售数量] else 0 end) [' + qm + ']'
from (select distinct qm from (
select t2.bh as [商品编号],t3.qm as [商品全名],t3.sl4 as [总销售数量],t1.qm
from [dbo].[chck] as t1
join [dbo].[dj_head] as t2 on t1.id = t2.id
join [dbo].[kcsp] as t3 on t1.id = t3.id
) as t) as a
set @sql = @sql + ' from ('
+' select t2.bh as [商品编号],t3.qm as [商品全名],t3.sl4 as [总销售数量],t1.qm'
+' from [dbo].[chck] as t1'
+' join [dbo].[dj_head] as t2 on t1.id = t2.id'
+' join [dbo].[kcsp] as t3 on t1.id = t3.id'
+') as t group by [商品编号],[商品全名],[总销售数量]'
exec(@sql);
为什么在查询分析器里查询不出数据啊?
还错误:
服务器: 消息 156,级别 15,状态 1,行 3
在关键字 'declare' 附近有语法错误。
怎么都以ID来关联啊,谁跟你说了ID是关联字段啊?
@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 @sql=@sql +'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 @sql=@sql+' 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 '''+@beginDate+''' and '''+@endDate+''') b on a.bh=b.bh'+
' left join chck c on b.fhck=c.bh'+
' where '+@searchKcsp+''+
' group by a.bh,a.qm,a.sl4'
--print @sql
GO