--仓库表,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现在要的结果就是得到每件商品在每个仓库里的销售数量?
解决方案 »
- delphi中如何设置serversocket的接收字节上限
- 我用IdAntiFreeze来防止程序没有响应,为什么不行?
- C/S控制并发问题
- 如何检测系统中是否有一类字体和确定的动态连接库??
- Dll中如何传递超过255长度的字符串?
- 大家帮忙看看,这个SQL应如何写才能运行
- 关于Delphi 的ActiveX Document的问题!
- 用DELPHI5,测试一个字符串是否为数字的函数如何写?
- DBgrid 的OnDrawDataCell 是什么时候发生的
- select count(*),sum(round(field*0.0456)) from tb1,tb2 如何实现?
- delphi做CS结构的系统时,如何实现客户端数据库和服务器端数据库,双向互传。
- 兄弟们有问题了高手请看。。。。。。。。。。。。。。。。。。。
union
select 商品编号,0,总销售数量 from B
可能还需要和第一张表根据 编号做一个inner join
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);
isNull((select sum(销售单.数量) from 销售单 where 仓库=sumX.仓库 and 产品=sumX.产品ID),0)
from
(select 库存.仓库ID, 库存.产品ID, sum(库存.数量) as 数量 from 仓库 inner join 产品 on 产品.ID=库存.产品ID group by 库存.仓库,库存.产品) as sumX
先贴上据我理解的代码
select kcsp.qm as 商品名称,chck.qm as 仓库名称,sum(dj_head.sl) as 销售数量 from kcsp left join dj_head on kcsp.bh = dj_head.bh left join chck on chck.bh = dj_head.fhck group by chck.qm,kcsp.qm
SQL如下:select distinct dj_head.bh as 商品编号,kcsp.qm as 商品名称,kcsp.sl4 as 总销售数量,
max(case fhck when '1' then dj_head.sl else 0 end) '1号仓库',
max(case fhck when '2' then dj_head.sl else 0 end) '2号仓库',
max(case fhck when '3' then dj_head.sl else 0 end) '3号仓库',
max(case fhck when '4' then dj_head.sl else 0 end) '4号仓库',
max(case fhck when '5' then dj_head.sl else 0 end) '5号仓库'
from dj_head
inner join chck on chck.bh=dj_head.fhck
inner join kcsp on dj_head.bh=kcsp.bh
group by dj_head.bh,kcsp.qm,kcsp.sl4
var
ASql :string;
begin
ASql := ' select distinct qm, bh from chck ';
ADOQuery1.Close;
ADOQuery1.SQL.Text := ASql;
ADOQuery1.Open; ASql := 'SELECT dbo.kcsp.bh, dbo.kcsp.qm, kcsp.sl4';
ADOQuery1.First;
while not ADOQuery1.Eof do
begin
if (ADOQuery1.FieldByName('qm').AsString <> '') and (ADOQuery1.FieldByName('bh').AsString <> '') then
ASql := ASql + ',' + ADOQuery1.FieldByName('qm').AsString +
'= (SELECT isnull(sum(sl),0) FROM dj_head WHERE fhck =' + QuotedStr(ADOQuery1.FieldByName('bh').AsString) + ')';
ADOQuery1.Next;
end;
ASql := ASql + ' FROM dbo.dj_head LEFT OUTER JOIN dbo.kcsp ON dbo.dj_head.bh = dbo.kcsp.bh';
ADOQuery2.Close;
ADOQuery2.SQL.Text := ASql;
ADOQuery2.Open;
end;
var
ASql :string;
begin
ASql := ' select distinct qm, bh from chck ';
ADOQuery1.Close;
ADOQuery1.SQL.Text := ASql;
ADOQuery1.Open; ASql := 'SELECT distinct dbo.kcsp.bh, dbo.kcsp.qm, kcsp.sl4';
ADOQuery1.First;
while not ADOQuery1.Eof do
begin
if (ADOQuery1.FieldByName('qm').AsString <> '') and (ADOQuery1.FieldByName('bh').AsString <> '') then
ASql := ASql + ',' + ADOQuery1.FieldByName('qm').AsString +
'= (SELECT isnull(sum(sl),0) FROM dj_head WHERE fhck =' + QuotedStr(ADOQuery1.FieldByName('bh').AsString) + ' and bh=kcsp.bh)';
ADOQuery1.Next;
end;
ASql := ASql + ' FROM dbo.dj_head LEFT OUTER JOIN dbo.kcsp ON dbo.dj_head.bh = dbo.kcsp.bh';
ADOQuery2.Close;
ADOQuery2.SQL.Text := ASql;
ADOQuery2.Open;
end;额,测试了下,刚才发的查询数据不对
declare @sql varchar(8000)
set @sql = 'select distinct dj_head.bh as 商品编号,kcsp.qm as 商品名称,kcsp.sl4 as 总销售数量 '
select @sql = @sql + ' , max(case fhck when ''' + bh + ''' then sl else 0 end) [' + bh + '号仓库]'
from (select distinct bh from chck) as a
set @sql = @sql + ' from dj_head
right join chck on chck.bh=dj_head.fhck
right join kcsp on dj_head.bh=kcsp.bh
group by dj_head.bh,kcsp.qm,kcsp.sl4'
exec(@sql)