求一个SQL一共是5个表
A表是入库明细
B表是出库明细
C表是返厂明细
d表是返库明细
A B C D 表字段都相同 为:
产品编码 产品名称 数量 时间
E表是库存表 字段为:产品编码 产品名称 库存数量(程序每次入、出等操作自动算出来,然后插入的 ,其实就是个记录)
现在的问题是:我要查询出来的结果是:产品编码 产品名称 入数量 出数量 返厂数量 返库数量 库存数量
现在我已经实现了,但我想查询一个时间段的库存,如何和A B C D E的时间字段关联呢? 因为设计的时候 E表没有时间字段(数据库不能修改了) 。
A表是入库明细
B表是出库明细
C表是返厂明细
d表是返库明细
A B C D 表字段都相同 为:
产品编码 产品名称 数量 时间
E表是库存表 字段为:产品编码 产品名称 库存数量(程序每次入、出等操作自动算出来,然后插入的 ,其实就是个记录)
现在的问题是:我要查询出来的结果是:产品编码 产品名称 入数量 出数量 返厂数量 返库数量 库存数量
现在我已经实现了,但我想查询一个时间段的库存,如何和A B C D E的时间字段关联呢? 因为设计的时候 E表没有时间字段(数据库不能修改了) 。
inner join B on A.id = B.id
inner join C on A.id = C.id
inner join D on A.id = D.id
inner join E on A.id = E.id
where A.时间 between '' and ''
建议你列出你的表结构,并提供测试数据以及基于这些测试数据的所对应正确结果。
参考一下这个贴子的提问方式http://topic.csdn.net/u/20091130/20/8343ee6a-417c-4c2d-9415-fa46604a00cf.html
1. 你的 create table xxx .. 语句
2. 你的 insert into xxx ... 语句
3. 结果是什么样,(并给以简单的算法描述)
4. 你用的数据库名称和版本(经常有人在MS SQL server版问 MySQL)
这样想帮你的人可以直接搭建和你相同的环境,并在给出方案前进行测试,避免文字描述理解上的误差。
/* 按个人理解,你们是那是物流中介公司
返厂是是出库,返库是入库
库存数量=原始库存+入库数量-出库数量+返库数量-返厂数量
=>原始库存=库存数量-入库数量+出库数量-返库数量+返厂数量
我不知道时间段的库存如何查,给你写了段在你指定时间时候的历史库存,你可以查2次得出库存时间段?
*/select a.产品编码,a.产品名称 ,a.数量 as 入库数量,b.数量 as 出库数量,c.数量 as 返厂数量,d.数量 as 返库数量 ,
(e.数量-
coalesce((select sum(数量) from a where 时间>'输入你要查询的时间' ),0)+
coalesce((select sum(数量) from b where 时间>'输入你要查询的时间'),0)-
coalesce((select sum(数量) from d where 时间>'输入你要查询的时间'),0)+
coalesce((select sum(数量) from c where 时间>'输入你要查询的时间'),0)
)as 库存数量
from a,b,c,d,e
where a.产品编码=b.产品编码 and
a.产品编码=c.产品编码 and
a.产品编码=d.产品编码 and
a.产品编码=e.产品编码
一共是5个表
A表是入库明细
B表是出库明细
C表是返厂明细
d表是返库明细
A B C D 表字段都相同 为: 产品编码 产品名称 数量 时间 E表是库存表 字段为: 产品编码 产品名称 库存数量(程序每次入、出等操作自动算出来,然后插入的 ,其实就是个记录)
现在的问题是:我要查询出来的结果是: 产品编码 产品名称 入数量 出数量 返厂数量 返库数量 库存数量
现在我已经实现了,但我想查询一个时间段的库存(比如2010-1-1 到 2010-2-1,也就是要查询出这个时间段的入 出 返 等数量),如何和A B C D 的时间字段联系上呢? 其中a b c d 表里的产品编码和e表的产品编码 是有关系的 。
E表(库存表)产品编码 产品名称 库存数量
cp001 手机 18
cp002 笔记本 8
------------------------------------A表 (入库表)产品编码 产品名称 数量 时间
cp001 手机 8 2009-7-1
cp001 手机 13 2010-1-1
cp002 笔记本 8 2010-1-1
------------------------------------B表 (出库表)产品编码 产品名称 数量 时间
cp001 手机 4 2010-1-1------------------------------------c表 (返厂表)产品编码 产品名称 数量 时间
cp001 手机 1 2010-1-1
------------------------------------d表 (返库表)产品编码 产品名称 数量 时间
cp001 手机 2 2010-1-1
------------------------------------库存=入库-出库-返厂+返库+转存
结果为我要查询2010-1-1 到 现在时间的数据产品编码 产品名称 入数量 出数量 返厂数量 返库数量 转存数量 库存
cp001 手机 13 4 1 2 8 18
cp002 笔记本 8 0 0 0 0 8
/*------------------------------------------------------------------
-- Author : htl258(Tony)
-- Date : 2010-04-10 21:44:47
-- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
Jul 9 2008 14:43:34
Copyright (c) 1988-2008 Microsoft Corporation
Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)------------------------------------------------------------------*/
--> 生成测试数据表:EIF OBJECT_ID('[E]') IS NOT NULL
DROP TABLE [E]
GO
CREATE TABLE [E]([产品编码] NVARCHAR(10),[产品名称] NVARCHAR(10),[库存数量] INT)
INSERT [E]
SELECT 'cp001',N'手机',18 UNION ALL
SELECT 'cp002',N'笔记本',8
GO
--SELECT * FROM [E]--> 生成测试数据表:AIF OBJECT_ID('[A]') IS NOT NULL
DROP TABLE [A]
GO
CREATE TABLE [A]([产品编码] NVARCHAR(10),[产品名称] NVARCHAR(10),[数量] INT,[时间] DATETIME)
INSERT [A]
SELECT 'cp001',N'手机',8,N'2009-7-1' UNION ALL
SELECT 'cp001',N'手机',13,N'2010-1-1' UNION ALL
SELECT 'cp002',N'笔记本',8,N'2010-1-1'
GO
--SELECT * FROM [A]--> 生成测试数据表:BIF OBJECT_ID('[B]') IS NOT NULL
DROP TABLE [B]
GO
CREATE TABLE [B]([产品编码] NVARCHAR(10),[产品名称] NVARCHAR(10),[数量] INT,[时间] DATETIME)
INSERT [B]
SELECT 'cp001',N'手机',4,N'2010-1-1'
GO
--SELECT * FROM [B]--> 生成测试数据表:cIF OBJECT_ID('[c]') IS NOT NULL
DROP TABLE [c]
GO
CREATE TABLE [c]([产品编码] NVARCHAR(10),[产品名称] NVARCHAR(10),[数量] INT,[时间] DATETIME)
INSERT [c]
SELECT 'cp001',N'手机',1,N'2010-1-1'
GO
--SELECT * FROM [c]--> 生成测试数据表:dIF OBJECT_ID('[d]') IS NOT NULL
DROP TABLE [d]
GO
CREATE TABLE [d]([产品编码] NVARCHAR(10),[产品名称] NVARCHAR(10),[数量] INT,[时间] DATETIME)
INSERT [d]
SELECT 'cp001',N'手机',2,N'2010-1-1'
GO
--SELECT * FROM [d]-->SQL查询如下:
IF OBJECT_ID('[SP_STOCKQTY]') IS NOT NULL
DROP PROC [SP_STOCKQTY]
GO
CREATE PROC SP_STOCKQTY
@B DATETIME,--起始日期
@E DATETIME=NULL --截止日期
AS
SET @E=ISNULL(@E,GETDATE())--如果载止日期没输入,默认为当前日期SELECT A.产品编码,A.产品名称,A.数量 入数量,ISNULL(B.数量,0) 出数量,ISNULL(C.数量,0) 返厂数量,
ISNULL(D.数量,0) 返库数量,A.转存数量-ISNULL(B.转存数量,0)-ISNULL(C.转存数量,0)+ISNULL(D.转存数量,0) 转存数量,
A.总数量-ISNULL(B.总数量,0)-ISNULL(C.总数量,0)+ISNULL(D.总数量,0) 库存
FROM (
SELECT 产品编码,产品名称,SUM(case when [时间] BETWEEN @B AND @E then 数量 else 0 end) 数量,
SUM(case when [时间] < @B then 数量 else 0 end) 转存数量,SUM(数量) 总数量
FROM A
GROUP BY 产品编码,产品名称
) A
LEFT JOIN (
SELECT 产品编码,SUM(case when [时间] BETWEEN @B AND @E then 数量 else 0 end) 数量,
SUM(case when [时间] < @B then 数量 else 0 end) 转存数量,SUM(数量) 总数量
FROM B
GROUP BY 产品编码
) B
ON A.产品编码=B.产品编码
LEFT JOIN (
SELECT 产品编码,SUM(case when [时间] BETWEEN @B AND @E then 数量 else 0 end) 数量,
SUM(case when [时间] < @B then 数量 else 0 end) 转存数量,SUM(数量) 总数量
FROM C
GROUP BY 产品编码
) C
ON A.产品编码=C.产品编码
LEFT JOIN (
SELECT 产品编码,SUM(case when [时间] BETWEEN @B AND @E then 数量 else 0 end) 数量,
SUM(case when [时间] < @B then 数量 else 0 end) 转存数量,SUM(数量) 总数量
FROM D
GROUP BY 产品编码
) D
ON A.产品编码=D.产品编码
ORDER BY 产品编码
GOEXEC SP_STOCKQTY '2010-01-01'
/*
产品编码 产品名称 入数量 出数量 返厂数量 返库数量 转存数量 库存
---------- ---------- ----------- ----------- ----------- ----------- ----------- -----------
cp001 手机 13 4 1 2 8 18
cp002 笔记本 8 0 0 0 0 8(2 行受影响)
*/
/*------------------------------------------------------------------
-- Author : htl258(Tony)
-- Date : 2010-04-10 21:44:47
-- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
Jul 9 2008 14:43:34
Copyright (c) 1988-2008 Microsoft Corporation
Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)------------------------------------------------------------------*/
--> 生成测试数据表:EIF OBJECT_ID('[E]') IS NOT NULL
DROP TABLE [E]
GO
CREATE TABLE [E]([产品编码] NVARCHAR(10),[产品名称] NVARCHAR(10),[库存数量] INT)
INSERT [E]
SELECT 'cp001',N'手机',18 UNION ALL
SELECT 'cp002',N'笔记本',8
GO
--SELECT * FROM [E]--> 生成测试数据表:AIF OBJECT_ID('[A]') IS NOT NULL
DROP TABLE [A]
GO
CREATE TABLE [A]([产品编码] NVARCHAR(10),[产品名称] NVARCHAR(10),[数量] INT,[时间] DATETIME)
INSERT [A]
SELECT 'cp001',N'手机',8,N'2009-7-1' UNION ALL
SELECT 'cp001',N'手机',13,N'2010-1-1' UNION ALL
SELECT 'cp002',N'笔记本',8,N'2010-1-1'
GO
--SELECT * FROM [A]--> 生成测试数据表:BIF OBJECT_ID('[B]') IS NOT NULL
DROP TABLE [B]
GO
CREATE TABLE [B]([产品编码] NVARCHAR(10),[产品名称] NVARCHAR(10),[数量] INT,[时间] DATETIME)
INSERT [B]
SELECT 'cp001',N'手机',4,N'2010-1-1'
GO
--SELECT * FROM [B]--> 生成测试数据表:cIF OBJECT_ID('[c]') IS NOT NULL
DROP TABLE [c]
GO
CREATE TABLE [c]([产品编码] NVARCHAR(10),[产品名称] NVARCHAR(10),[数量] INT,[时间] DATETIME)
INSERT [c]
SELECT 'cp001',N'手机',1,N'2010-1-1'
GO
--SELECT * FROM [c]--> 生成测试数据表:dIF OBJECT_ID('[d]') IS NOT NULL
DROP TABLE [d]
GO
CREATE TABLE [d]([产品编码] NVARCHAR(10),[产品名称] NVARCHAR(10),[数量] INT,[时间] DATETIME)
INSERT [d]
SELECT 'cp001',N'手机',2,N'2010-1-1'
GO
--SELECT * FROM [d]-->SQL查询如下:
IF OBJECT_ID('[SP_STOCKQTY]') IS NOT NULL
DROP PROC [SP_STOCKQTY]
GO
CREATE PROC SP_STOCKQTY
@B DATETIME,--起始日期
@E DATETIME=NULL --截止日期
AS
SET @E=ISNULL(@E,GETDATE())--如果载止日期没输入,默认为当前日期SELECT ISNULL(ISNULL(ISNULL(A.产品编码,B.产品编码),C.产品编码),D.产品编码) 产品编码,
ISNULL(ISNULL(ISNULL(A.产品名称,B.产品名称),C.产品名称),D.产品名称) 产品名称,
ISNULL(A.数量,0) 入数量,ISNULL(B.数量,0) 出数量,ISNULL(C.数量,0) 返厂数量,ISNULL(D.数量,0) 返库数量,
ISNULL(A.转存数量,0)-ISNULL(B.转存数量,0)-ISNULL(C.转存数量,0)+ISNULL(D.转存数量,0) 转存数量,
ISNULL(A.总数量,0)-ISNULL(B.总数量,0)-ISNULL(C.总数量,0)+ISNULL(D.总数量,0) 库存
FROM (
SELECT 产品编码,产品名称,SUM(case when [时间] BETWEEN @B AND @E then 数量 else 0 end) 数量,
SUM(case when [时间] < @B then 数量 else 0 end) 转存数量,SUM(数量) 总数量
FROM A
GROUP BY 产品编码,产品名称
) A
FULL JOIN (
SELECT 产品编码,产品名称,SUM(case when [时间] BETWEEN @B AND @E then 数量 else 0 end) 数量,
SUM(case when [时间] < @B then 数量 else 0 end) 转存数量,SUM(数量) 总数量
FROM B
GROUP BY 产品编码,产品名称
) B
ON A.产品编码=B.产品编码
FULL JOIN (
SELECT 产品编码,产品名称,SUM(case when [时间] BETWEEN @B AND @E then 数量 else 0 end) 数量,
SUM(case when [时间] < @B then 数量 else 0 end) 转存数量,SUM(数量) 总数量
FROM C
GROUP BY 产品编码,产品名称
) C
ON A.产品编码=C.产品编码
FULL JOIN (
SELECT 产品编码,产品名称,SUM(case when [时间] BETWEEN @B AND @E then 数量 else 0 end) 数量,
SUM(case when [时间] < @B then 数量 else 0 end) 转存数量,SUM(数量) 总数量
FROM D
GROUP BY 产品编码,产品名称
) D
ON A.产品编码=D.产品编码
ORDER BY 产品编码
GOEXEC SP_STOCKQTY '2010-01-01'
/*
产品编码 产品名称 入数量 出数量 返厂数量 返库数量 转存数量 库存
---------- ---------- ----------- ----------- ----------- ----------- ----------- -----------
cp001 手机 13 4 1 2 8 18
cp002 笔记本 8 0 0 0 0 8(2 行受影响)
*/