--仓库表,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现在要的结果就是得到每件商品在每个仓库里的销售数量?
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现在要的结果就是得到每件商品在每个仓库里的销售数量?
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);
sum(if(C.Bh='001',A.sl,0)) as 1a,
sum(if(C.Bh='002',A.sl,0)) as 2a,
sum(if(C.Bh='003',A.sl,0)) as 3a,
sum(if(C.Bh='004',A.sl,0)) as 4a,
sum(if(C.Bh='005',A.sl,0)) as 5a
From head A left join kcsp B on A.bh=B.bh
left Join chck C on A.fhck=C.Bh
Group by A.bh
from 仓库表 a
left join 销售表 b on a.仓库编号=b.仓库编号
left join 商品表 c on c.商品编号=b.商品编号
group by a.仓库编号,a.仓库名称,c.商品编号,c.商品名称
-> sum(if(C.Bh='001',A.sl,0)) as 1a,
-> sum(if(C.Bh='002',A.sl,0)) as 2a,
-> sum(if(C.Bh='003',A.sl,0)) as 3a,
-> sum(if(C.Bh='004',A.sl,0)) as 4a,
-> sum(if(C.Bh='005',A.sl,0)) as 5a
-> From head A left join kcsp B on A.bh=B.bh
-> left Join chck C on A.fhck=C.Bh
-> Group by A.bh;
+------------+------+----------+------+------+------+------+------+
| Bh | Type | allsales | 1a | 2a | 3a | 4a | 5a |
+------------+------+----------+------+------+------+------+------+
| 0000000001 | ???? | 100 | 100 | 0 | 0 | 0 | 0 |
| 0000000002 | ???? | 200 | 200 | 0 | 0 | 0 | 0 |
| 0000000058 | ???? | 300 | 0 | 300 | 0 | 0 | 0 |
| 0000000067 | ???? | 400 | 0 | 0 | 400 | 0 | 0 |
+------------+------+----------+------+------+------+------+------+
4 rows in set (0.00 sec)
ACCESS的话把IF改为IIF,SQL SERVER的话用CASE WHEN....
ORACLE的话..不懂.
上面语句只适合静态仓库数,一旦仓库多的话,要用动态SQL连接.
select @sql='select 商品编号,商品名称 '
--将所有的仓库装配到sql中
select @sql=@sql +'sum(case 仓库名称 when ' + 仓库名称 +' then 销量 else 0 end) as '+仓库名称 +','
from 仓库表
--将From部分装配到sql
select @sql=left(@sql,len(@sql)-1)
select @sql=@sql+' from 仓库表 a '+
' left join 销售表 b on a.仓库编号=b.仓库编号'+
' left join 商品表 c on c.商品编号=b.商品编号'+
' group by c.商品编号,c.商品名称'
用exec或sp_executesql执行该@sql就是你想要的
@sql varchar(2000)
)
as
select @sql='select bh,qm '
--将所有的仓库装配到sql中
select @sql=@sql +'sum(case qm when ' + qm +' then 销量 else 0 end) as '+qm +','
from chck
--将From部分装配到sql
select @sql=left(@sql,len(@sql)-1)
select @sql=@sql+' from chck a '+
' left join dj_head b on a.bh=b.fhck'+
' left join kcsp c on c.bh=b.bh'+
' group by c.bh,c.qm'
GOexec kkk2 ''这样没有显示任何数据啊,可是符合条件的有很多数据啊
set @a1='Select A.Bh,A.Type,sum(A.sl) as allsales,';
set @a3=' From head A left join kcsp B on A.bh=B.bh left Join chck C on A.fhck=C.Bh
Group by A.bh';
set @a2='';
select @a2:=CONCAT(@a2,'sum(IF(A.bh=',bh,',A.sl,0)) AS ',bh,'a',',') From chck;
select @a2:=left(@a2,length(@a2)-1);
select @a4:=concat(@a1,@a2,@a3);
prepare dd from @a4;
execute dd;
--有中文的话,此处改成 nvarchar
declare @sql nvarchar(max)
select @sql='select c.商品编号,c.商品名称 '
--将所有的仓库装配到sql中
select @sql=@sql +'sum(case when a.仓库编号 =''' + 仓库编号+''' then c.销量 else 0 end) as '''+仓库名称 +''','+char(10)
from 仓库表
--去除末尾的回车和逗号
select @sql=left(@sql,len(@sql)-2)+char(10)
--将From部分装配到sql
select @sql=@sql+' from 仓库表 a '+ char(10)+
' left join 销售表 b on a.仓库编号=b.仓库编号'+ char(10)+
' left join 商品表 c on c.商品编号=b.商品编号'+ char(10)+
' group by c.商品编号,c.商品名称'
--执行语句
exec (@sql)
--翻译到我们系统以后是有结果的.你把@SQL输出来看看.如果检查不出问题,那把@SQL帖出来
@sql nvarchar(2000)
)
as
select @sql='select c.bh,c.qm '
--将所有的仓库装配到sql中
select @sql=@sql +'sum(case when a.bh =''' + bh+''' then c.sl else 0 end) as '''+qm +''','+char(10)
from chck
--去除末尾的回车和逗号
select @sql=left(@sql,len(@sql)-2)+char(10)
--将From部分装配到sql
select @sql=@sql+' from chck a '+ char(10)+
' left join dj_head b on a.bh=b.fhck'+ char(10)+
' left join kcsp c on c.bh=b.bh'+ char(10)+
' group by c.bh,c.qm'
GO然后在查询分析器里:exec kkk3 ''
这样没有显示任何数据,只提示:命令已成功完成。
select c.bh,c.qm
sum(case when a.bh ='001' then c.sl else 0 end) as '1号仓库',
sum(case when a.bh ='002' then c.sl else 0 end) as '2号仓库',
sum(case when a.bh ='003' then c.sl else 0 end) as '3号仓库',
sum(case when a.bh ='004' then c.sl else 0 end) as '4号仓库',
sum(case when a.bh ='005' then c.sl else 0 end) as '5号仓库',
sum(case when a.bh ='006' then c.sl else 0 end) as '6号仓库'
from chck a
left join dj_head b on a.bh=b.fhck
left join kcsp c on c.bh=b.bh
group by c.bh,c.qm