解决方案 »
- 一个简单的查询问题
- sql2000编程思路(急,再求一次)
- 恢复时候出现错误"发生内部一致性错误,请于技术支持联系以寻求协助,restore database操作异常终止"我的天,这种错误倒底是什么原因?:(
- 一个触发器问题.
- 分组难题
- 如何在select返回的数据中的某一列里面加上内容?
- 奇怪的事(半年了一直没找到原因),为什么啊!高手救我!100分!
- [email protected] 密码299981 有一个数据库,数据库中的存储过程被加密了,解密代码解的有问题,欢迎大家挑战
- 有成功在WIN XP PRO上装上MS SQL server 2000的么?
- cast与covert 有什么区别呢???
- 分页存储过程拼接错误
- 如何通过sql server日志得到某段时间改变(增删改)的数据?
这里面应该能找到你要的吧
IF OBJECT_ID('[批号库存表]') IS NOT NULL DROP TABLE [批号库存表]
GO
CREATE TABLE [批号库存表]([编码] VARCHAR(3),[批号] INT,[批号库存] INT)
INSERT [批号库存表]
SELECT 'A01',201107,100 UNION ALL
SELECT 'A01',201108,100 UNION ALL
SELECT 'A02',201107,400 UNION ALL
SELECT 'A02',201108,500--> 测试数据:[库存出货表]
IF OBJECT_ID('[库存出货表]') IS NOT NULL DROP TABLE [库存出货表]
GO
CREATE TABLE [库存出货表]([编码] VARCHAR(3),[出库日期] DATETIME,[出库数量] INT)
INSERT [库存出货表]
SELECT 'A01','2011-8-15',80 UNION ALL
SELECT 'A01','2011-8-16',10 UNION ALL
SELECT 'A01','2011-8-17',20 UNION ALL
SELECT 'A01','2011-8-18',30 UNION ALL
SELECT 'A02','2011-8-15',50 UNION ALL
SELECT 'A02','2011-8-16',80 UNION ALL
SELECT 'A02','2011-8-17',120 UNION ALL
SELECT 'A02','2011-8-18',300
--------------开始查询--------------------------
select b.[编码] , b.[出库日期],b.[出库数量],a.[批号], [数量]=case when b.出货sum <a.进货sum then b.出货sum else a.进货sum end
-
case when b.出货sum-b.[出库数量] <a.进货sum-a.[批号库存] then a.进货sum-a.[批号库存] else b.出货sum-b.[出库数量]
end
from
(
select *,进货sum=(select sum([批号库存]) from [批号库存表] where [编码]=a.[编码] and [批号]!> a.[批号])
from [批号库存表] a
)a
join
(
select *,出货sum=(select sum([出库数量]) from [库存出货表] where [编码]=b.[编码] and [出库日期]!> b.[出库日期])
from [库存出货表] b
) b
on a.[编码]=b.[编码]
and b.出货sum-b.[出库数量] <a.进货sum
and a.进货sum-a.[批号库存] <b.出货sum
/*
编码 出库日期 出库数量 批号 数量
---- ----------------------- ----------- ----------- -----------
A01 2011-08-15 00:00:00.000 80 201107 80
A01 2011-08-16 00:00:00.000 10 201107 10
A01 2011-08-17 00:00:00.000 20 201107 10
A01 2011-08-17 00:00:00.000 20 201108 10
A01 2011-08-18 00:00:00.000 30 201108 30
A02 2011-08-15 00:00:00.000 50 201107 50
A02 2011-08-16 00:00:00.000 80 201107 80
A02 2011-08-17 00:00:00.000 120 201107 120
A02 2011-08-18 00:00:00.000 300 201107 150
A02 2011-08-18 00:00:00.000 300 201108 150(10 行受影响)
*/
IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'tba')
BEGIN
DROP TABLE tba
END
GO
IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'tbb')
BEGIN
DROP TABLE tbb
END
GO
CREATE TABLE tba
(
item VARCHAR(10),
lot VARCHAR(10),
qty1 INT
)
GO
CREATE TABLE tbb
(
item VARCHAR(10),
riqi DATETIME,
qty2 INT
)
GO
INSERT INTO tba
SELECT 'A01','201207',100 UNION
SELECT 'A01','201208',100 UNION
SELECT 'A02','201207',400 UNION
SELECT 'A02','201208',500
GO
INSERT INTO tbb
SELECT 'A01','2012-08-15',80 UNION
SELECT 'A01','2012-08-16',10 UNION
SELECT 'A01','2012-08-17',20 UNION
SELECT 'A01','2012-08-18',30 UNION
SELECT 'A02','2012-08-15',50 UNION
SELECT 'A02','2012-08-16',80 UNION
SELECT 'A02','2012-08-17',120 UNION
SELECT 'A02','2012-08-18',300
GOWITH t1 AS
(
SELECT item,lot,qty1,(SELECT SUM(qty1) FROM tba WHERE A.item = item AND A.lot >= lot) AS total
FROM tba AS A
),
t2 AS
(
SELECT item,riqi,qty2,(SELECT SUM(qty2) FROM tbb WHERE A.item = item AND A.riqi >= riqi) AS total
FROM tbb AS A
)SELECT t1.item,
CONVERT(VARCHAR(10),riqi,120) AS riqi,
qty2,
lot,
CASE WHEN t2.total <= t1.total THEN t2.total
ELSE t1.total END - CASE WHEN t2.total-t2.qty2 <= t1.total-t1.qty1 THEN t1.total-t1.qty1
ELSE t2.total-t2.qty2 END AS Num
FROM t1 INNER JOIN t2 ON T1.item = t2.item and t2.total-t2.qty2 <= t1.total and t1.total-t1.qty1 <= t2.totalitem riqi qty2 lot Num
A01 2012-08-15 80 201207 80
A01 2012-08-16 10 201207 10
A01 2012-08-17 20 201207 10
A01 2012-08-17 20 201208 10
A01 2012-08-18 30 201208 30
A02 2012-08-15 50 201207 50
A02 2012-08-16 80 201207 80
A02 2012-08-17 120 201207 120
A02 2012-08-18 300 201207 150
A02 2012-08-18 300 201208 150
CREATE TABLE tba ( item VARCHAR2(10), lot VARCHAR2(10), qty1 INT );
CREATE TABLE tbb ( item VARCHAR2(10), riqi date, qty2 INT );
INSERT INTO tba SELECT 'A01','201207',100 from dual
UNION SELECT 'A01','201208',100 from dual
UNION SELECT 'A02','201207',400 from dual
UNION SELECT 'A02','201208',500 from dual;
INSERT INTO tbb SELECT 'A01',to_date('2012-08-15','yyyy-MM-dd HH24:mi:ss'),80 from dual
UNION SELECT 'A01',to_date('2012-08-16','yyyy-MM-dd HH24:mi:ss'),10 from dual
UNION SELECT 'A01',to_date('2012-08-17','yyyy-MM-dd HH24:mi:ss'),20 from dual
UNION SELECT 'A01',to_date('2012-08-18','yyyy-MM-dd HH24:mi:ss'),30 from dual
UNION SELECT 'A02',to_date('2012-08-15','yyyy-MM-dd HH24:mi:ss'),50 from dual
UNION SELECT 'A02',to_date('2012-08-16','yyyy-MM-dd HH24:mi:ss'),80 from dual
UNION SELECT 'A02',to_date('2012-08-17','yyyy-MM-dd HH24:mi:ss'),120 from dual
UNION SELECT 'A02',to_date('2012-08-18','yyyy-MM-dd HH24:mi:ss'),300 from dual;
WITH t1 AS
( SELECT item,lot,qty1,
(SELECT SUM(qty1) FROM tba
WHERE A.item = item AND A.lot >= lot) AS total FROM tba A ) ,
t2 AS
( SELECT item,riqi,qty2,(SELECT SUM(qty2)
FROM tbb WHERE A.item = item AND A.riqi >= riqi) AS total
FROM tbb A )
SELECT t1.item,
to_char(riqi, 'yyyy-MM-dd') AS riqi,
qty2,
lot,
decode(sign(t2.total - t1.total),-1,t2.total, t1.total) -
decode(sign(t2.total - t2.qty2 - t1.total + t1.qty1),-1,
t1.total - t1.qty1,
t2.total - t2.qty2) as num
FROM t1, t2
where T1.item = t2.item(+)
and t2.total - t2.qty2 <= t1.total
and t1.total - t1.qty1 <= t2.total;