主要是进行库存领用率的计算,在sql server数据库有物资入库表a1(含字段:编号,名称,数量,入库时间)和物资出库表a2((含字段:编号,名称,数量,入库时间,出库时间),要实现查询入库表中某个月的所有入库物资在该月后的3个月内的出库情况,如2月份的物资入库领用率,应查询该月的所有入库物资在出库表a2中的所有满足从物资入库时间开始至5月31日前且编号和入库时间与入库表a1相同的记录,然后将入库表a1中每一项物资的入库数量减去从出库表中查询结果对应的数量,最后将结果插入数据表3,如:
a1
编号,名称,数量,入库时间
001 a 2 2009-01-10
002 b 5 2009-01-15
003 c 7 2009-01-10
004 d 2 2009-01-20
005 e 2 2009-01-20a2
编号,名称,数量,入库时间 出库时间
001 a 2 2009-01-10 2009-03-10
002 b 2 2009-01-15 2009-02-10
003 c 1 2009-01-10 2009-10-10
005 e 2 2009-02-10 2009-03-10 输出到a3表的结果是
编号,名称,数量
001 a 0
002 b 3
003 c 7
004 d 2
005 e 2 注意a3表中所有的物资编号应与a1表中满足1月份入库时间的物资编号一一对应请问以上sql语句应如何写?
a1
编号,名称,数量,入库时间
001 a 2 2009-01-10
002 b 5 2009-01-15
003 c 7 2009-01-10
004 d 2 2009-01-20
005 e 2 2009-01-20a2
编号,名称,数量,入库时间 出库时间
001 a 2 2009-01-10 2009-03-10
002 b 2 2009-01-15 2009-02-10
003 c 1 2009-01-10 2009-10-10
005 e 2 2009-02-10 2009-03-10 输出到a3表的结果是
编号,名称,数量
001 a 0
002 b 3
003 c 7
004 d 2
005 e 2 注意a3表中所有的物资编号应与a1表中满足1月份入库时间的物资编号一一对应请问以上sql语句应如何写?
解决方案 »
- 数据库中如何删除表中的重复记录只保留其中一条?
- 一个表按记录数分成多个小表,帮忙
- 帮我讲解下一个简单的建表SQL语句,2005都看不懂了
- ado新手,编译问题
- 如何不安装mssql2005导出05的数据库备份文件*.bak,已安装sql2000
- 如何用SQL语句删除由primary key约束或者unique约束创建的索引?(无内容)
- insert into select from 批量插入数据事务
- SQL server安装的系统要求是什么?XP可以吗?
- 十万火急,数据库误操作!! 数据丢失!!!! 没有备份,咋恢复????
- 数据库专家进来,数据库的允许为空和默认值问题
- 求一本适合菜鸟的SQL 数据库的书
- 请教一个SQL自动填充数据的问题,
a1
编号,名称,数量,入库时间
001 a 2 2009-01-10
002 b 5 2009-01-15
003 c 7 2009-01-10
004 d 2 2009-01-20
005 e 2 2009-01-20a2
编号,名称,数量,入库时间 出库时间
001 a 2 2009-01-10 2009-03-10
002 b 2 2009-01-15 2009-02-10
003 c 1 2009-01-10 2009-10-10
005 e 2 2009-02-10 2009-03-10 输出到a3表的结果是
编号,名称,数量
001 a 0
002 b 3
003 c 7
004 d 2
005 e 2
from a1 a left join a2 b on a.编号=b.编号
group by a.编号,a.名称
select a1.编号,a1.名称,a1.数量-a2.数量 as 数量
from a1 left join a2 on a1.编号=a2.编号
where a2.出库时间-a2.入库时间<90
-- Author :fredrickhu(小F,向高手学习)
-- Date :2010-03-25 13:42:37
-- Verstion:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[a1]
if object_id('[a1]') is not null drop table [a1]
go
create table [a1]([编号] varchar(3),[名称] varchar(1),[数量] int,[入库时间] datetime)
insert [a1]
select '001','a',2,'2009-01-10' union all
select '002','b',5,'2009-01-15' union all
select '003','c',7,'2009-01-10' union all
select '004','d',2,'2009-01-20' union all
select '005','e',2,'2009-01-20'
--> 测试数据:[a2]
if object_id('[a2]') is not null drop table [a2]
go
create table [a2]([编号] varchar(3),[名称] varchar(1),[数量] int,[入库时间] datetime,[出库时间] datetime)
insert [a2]
select '001','a',2,'2009-01-10','2009-03-10' union all
select '002','b',2,'2009-01-15','2009-02-10' union all
select '003','c',1,'2009-01-10','2009-10-10' union all
select '005','e',2,'2009-02-10','2009-03-10'
--------------开始查询--------------------------
select
isnull(a1.编号,a2.编号) as 编号,isnull(a1.名称,a2.名称) as 名称,isnull(a1.数量,0)-isnull(a2.数量,0) as 数量
from
a1 left join a2 on a1.编号=a2.编号
and
datediff(dd,a2.入库时间,a2.出库时间)<=90
----------------结果----------------------------
/* 编号 名称 数量
---- ---- -----------
001 a 0
002 b 3
003 c 7
004 d 2
005 e 0(5 行受影响)
*/
-- Author :fredrickhu(小F,向高手学习)
-- Date :2010-03-25 13:42:37
-- Verstion:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[a1]
if object_id('[a1]') is not null drop table [a1]
go
create table [a1]([编号] varchar(3),[名称] varchar(1),[数量] int,[入库时间] datetime)
insert [a1]
select '001','a',2,'2009-01-10' union all
select '002','b',5,'2009-01-15' union all
select '003','c',7,'2009-01-10' union all
select '004','d',2,'2009-01-20' union all
select '005','e',2,'2009-01-20'
--> 测试数据:[a2]
if object_id('[a2]') is not null drop table [a2]
go
create table [a2]([编号] varchar(3),[名称] varchar(1),[数量] int,[入库时间] datetime,[出库时间] datetime)
insert [a2]
select '001','a',2,'2009-01-10','2009-03-10' union all
select '002','b',2,'2009-01-15','2009-02-10' union all
select '003','c',1,'2009-01-10','2009-10-10' union all
select '005','e',2,'2009-02-10','2009-03-10'
--------------开始查询--------------------------
select
isnull(a1.编号,a2.编号) as 编号,isnull(a1.名称,a2.名称) as 名称,isnull(a1.数量,0)-isnull(a2.数量,0) as 数量
from
a1 left join a2 on a1.编号=a2.编号
and
datediff(dd,a1.入库时间,a2.出库时间)<=90
----------------结果----------------------------
/* 编号 名称 数量
---- ---- -----------
001 a 0
002 b 3
003 c 7
004 d 2
005 e 0(5 行受影响)
*/
insert a1
select '001','a', 2 ,'2009-01-10'
union select '002','b', 5 ,'2009-01-15'
union select '003','c', 7 ,'2009-01-10'
union select '004','d', 2 ,'2009-01-20'
union select '005','e', 2 ,'2009-01-20'
go
create table a2(编号 varchar(10),名称 varchar(10),数量 int,入库时间 datetime,出库时间 datetime)
insert a2
select '001','a', 2 ,'2009-01-10','2009-03-10'
union select '002','b', 2 ,'2009-01-15','2009-02-10'
union select '003','c', 1 ,'2009-01-10','2009-10-10'
union select '005','e', 2 ,'2009-02-10','2009-03-10'
go
select a.编号,a.名称,sum(a.数量 - case when datediff(month,b.入库时间,b.出库时间) <=3 then isnull(b.数量,0) else 0 end) 数量
from a1 a left join a2 b on a.编号=b.编号
group by a.编号,a.名称
drop table a1,a2/*
输出到a3表的结果是
编号,名称,数量
001 a 0
002 b 3
003 c 7
004 d 2
005 e 0 -- 不明白这个为什么的 “2”*/
-- Author :fredrickhu(小F,向高手学习)
-- Date :2010-03-25 13:42:37
-- Verstion:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[a1]
if object_id('[a1]') is not null drop table [a1]
go
create table [a1]([编号] varchar(3),[名称] varchar(1),[数量] int,[入库时间] datetime)
insert [a1]
select '001','a',2,'2009-01-10' union all
select '002','b',5,'2009-01-15' union all
select '003','c',7,'2009-01-10' union all
select '004','d',2,'2009-01-20' union all
select '005','e',2,'2009-01-20'
--> 测试数据:[a2]
if object_id('[a2]') is not null drop table [a2]
go
create table [a2]([编号] varchar(3),[名称] varchar(1),[数量] int,[入库时间] datetime,[出库时间] datetime)
insert [a2]
select '001','a',2,'2009-01-10','2009-03-10' union all
select '002','b',2,'2009-01-15','2009-02-10' union all
select '003','c',1,'2009-01-10','2009-10-10' union all
select '005','e',2,'2009-02-10','2009-03-10'
--------------开始查询--------------------------
select
isnull(a1.编号,a2.编号) as 编号,isnull(a1.名称,a2.名称) as 名称,isnull(a1.数量,0)-isnull(a2.数量,0) as 数量
from
a1 left join a2 on a1.编号=a2.编号
and
datediff(mm,a1.入库时间,a2.出库时间)<=3
----------------结果----------------------------
/* 编号 名称 数量
---- ---- -----------
001 a 0
002 b 3
003 c 7
004 d 2
005 e 0(5 行受影响)
*/
IF object_id('ta')IS NOT NULL DROP TABLE ta
CREATE TABLE TA(bm VARCHAR(3),name VARCHAR(5),num INT,rktime datetime)
INSERT ta
SELECT '001' ,'a' ,2, '2009-01-10' UNION ALL
SELECT '002' ,'b' ,5 ,'2009-01-15' UNION ALL
SELECT '003' ,'c' ,7 ,'2009-01-10' UNION ALL
SELECT '004' ,'d' ,2 ,'2009-01-20' UNION ALL
SELECT '005' ,'e', 2 ,'2009-01-20'IF object_id('tb')IS NOT NULL DROP TABLE tb
CREATE TABLE tb(bm VARCHAR(3),name VARCHAR(5),num INT,rktime DATETIME ,cktime datetime)
INSERT tb
SELECT '001', 'a' ,'2', '2009-01-10' ,'2009-03-10' UNION ALL
SELECT '002', 'b' ,'2' ,'2009-01-15', '2009-02-10' UNION ALL
SELECT '003' ,'c' ,'1', '2009-01-10', '2009-10-10' UNION ALL
SELECT '005' ,'e', '2', '2009-02-10', '2009-03-10' SELECT * FROM ta
SELECT * FROM tb SELECT ta.bm,ta.name,isnull(sum(TA.num-tb.num),0) FROM TA left JOIN tb ON TA.bm=tb.bm
WHERE datediff(dd,tb.cktime,tb.rktime)<=90
GROUP BY ta.bm,TA.name
insert into @a1 select 1,'a',2,'2009-01-10'
union all select 2,'b',5,'2009-01-15'
union all select 3,'c',7,'2009-01-10'
union all select 4,'d',2,'2009-01-20'
union all select 5,'e',2,'2009-01-20'
declare @a2 table (编号 int,名称 nvarchar(10),数量 int,入库时间 datetime,出库时间 datetime)
insert into @a2 select 1,'a',2,'2009-01-10','2009-03-10'
union all select 2,'b',2,'2009-1-15','2009-2-10'
union all select 3,'c',1,'2009-1-10','2009-10-10'
union all select 5,'e',2,'2009-2-10','2009-3-10'
select a.编号,a.名称,库存数= case when datediff(dd,b.出库时间,a.入库时间)<=90 then a.数量-b.数量 else a.数量 end
from @a1 a left join @a2 b on a.编号=b.编号(5 行受影响)(4 行受影响)
编号 名称 库存数
----------- ---------- -----------
1 a 0
2 b 3
3 c 6
4 d 2
5 e 0(5 行受影响)
FROM A1
LEFT JOIN A2
ON A11=A21 AND A14=A24
(
A11 VARCHAR(10),
A12 VARCHAR(10),
A13 INT,
A14 DATETIME
)
GOCREATE TABLE A2
(
A21 VARCHAR(10),
A22 VARCHAR(10),
A23 INT,
A24 DATETIME,
A25 DATETIME
)
GOCREATE TABLE A3
(
A31 VARCHAR(10),
A32 VARCHAR(10),
A33 INT
)
GOINSERT INTO A1
SELECT '001','a',2,'2009-01-10'
UNION
SELECT '002','b',5,'2009-01-15'
UNION
SELECT '003','c',7,'2009-01-10'
UNION
SELECT '004','d',2,'2009-01-20'
UNION
SELECT '005','e',2,'2009-01-20'
GOINSERT INTO A2
SELECT '001','a',2,'2009-01-10','2009-03-10'
UNION
SELECT '002','b',2,'2009-01-15','2009-02-10'
UNION
SELECT '003','c',1,'2009-01-10','2009-10-10'
UNION
SELECT '005','e',2,'2009-02-10','2009-03-10'
GOSELECT A11,A12,(A13-ISNULL(CASE WHEN A25>'2009-05-31' THEN NULL ELSE A23 END,0)) NUM
FROM A1
LEFT JOIN A2
ON A11=A21 AND A14=A24
--返回你要的结果