--生成测试数据
create table #t(
生產單號 INT,
入庫數量 INT,
入庫日期日期 DATETIME,
本次送貨數量 INT)INSERT INTO #T SELECT 1008929,4080 ,'2005/07/26 09:03:00',13140
INSERT INTO #T SELECT 1008929,8700 ,'2005/07/27 08:59:00',13140
INSERT INTO #T SELECT 1008929,10050,'2005/07/28 10:59:00',13140
INSERT INTO #T SELECT 1008929,2310 ,'2005/07/30 09:34:00',13140
INSERT INTO #T SELECT 1009004,1020 ,'2005/08/01 10:47:00',20000
INSERT INTO #T SELECT 1009004,8160 ,'2005/08/03 08:34:00',20000
INSERT INTO #T SELECT 1009004,7650 ,'2005/08/03 08:35:00',20000
--执行查询
DECLARE @OUTNUM INT
SET @OUTNUM = 24000SELECT
*
FROM
#T
WHERE
入庫日期日期 <=
(SELECT
TOP 1 A.入庫日期日期
FROM
#T A,
#T B
WHERE
A.入庫日期日期 >= B.入庫日期日期
GROUP BY
A.入庫日期日期
HAVING
SUM(B.入庫數量) >= @OUTNUM
ORDER BY
A.入庫日期日期)
--输出结果
/*
生產單號 入庫數量 入庫日期日期 本次送貨數量
-------- -------- ----------------------- ------------
1008929 4080 2005-07-26 09:03:00.000 13140
1008929 8700 2005-07-27 08:59:00.000 13140
1008929 10050 2005-07-28 10:59:00.000 13140
1008929 2310 2005-07-30 09:34:00.000 13140
*/
create table #t(
生產單號 INT,
入庫數量 INT,
入庫日期日期 DATETIME,
本次送貨數量 INT)INSERT INTO #T SELECT 1008929,4080 ,'2005/07/26 09:03:00',13140
INSERT INTO #T SELECT 1008929,8700 ,'2005/07/27 08:59:00',13140
INSERT INTO #T SELECT 1008929,10050,'2005/07/28 10:59:00',13140
INSERT INTO #T SELECT 1008929,2310 ,'2005/07/30 09:34:00',13140
INSERT INTO #T SELECT 1009004,1020 ,'2005/08/01 10:47:00',20000
INSERT INTO #T SELECT 1009004,8160 ,'2005/08/03 08:34:00',20000
INSERT INTO #T SELECT 1009004,7650 ,'2005/08/03 08:35:00',20000
--执行查询
DECLARE @OUTNUM INT
SET @OUTNUM = 24000SELECT
*
FROM
#T
WHERE
入庫日期日期 <=
(SELECT
TOP 1 A.入庫日期日期
FROM
#T A,
#T B
WHERE
A.入庫日期日期 >= B.入庫日期日期
GROUP BY
A.入庫日期日期
HAVING
SUM(B.入庫數量) >= @OUTNUM
ORDER BY
A.入庫日期日期)
--输出结果
/*
生產單號 入庫數量 入庫日期日期 本次送貨數量
-------- -------- ----------------------- ------------
1008929 4080 2005-07-26 09:03:00.000 13140
1008929 8700 2005-07-27 08:59:00.000 13140
1008929 10050 2005-07-28 10:59:00.000 13140
1008929 2310 2005-07-30 09:34:00.000 13140
*/
libin_ftsafe(子陌红尘)兄再幫我看看
===========================================
生產單號 入庫數量 入庫時間 送貨數量
1004625 800 2005/7/26 09:04 10000
1006548 251 2005/7/22 03:02 1150
1004625 1998 2005/7/26 09:04 10000
1008929 13140 2005/7/26 09:03 4080
1008929 13140 2005/7/27 08:59 8700
1008929 13140 2005/7/28 10:59 10050
1008929 13140 2005/7/30 09:34 2310
1007869 2060 2005/7/5 08:47 1680
1007869 2060 2005/7/6 09:47 1343
1008977 1500 2005/7/31 10:33 1584
1008977 1500 2005/8/2 09:59 1489
1008975 700 2005/8/1 10:44 740
1008991 160 2005/7/31 10:32 200
1008972 5787 2005/8/1 10:44 5787
1007557 40 2005/7/3 09:34 500
1008834 675 2005/7/27 09:04 760
1008833 169 2005/7/28 10:58 190
1008836 674 2005/7/27 09:03 790
1008835 1349 2005/8/1 10:48 1450
1008841 1351 2005/8/1 10:48 1355
1008856 2124 2005/8/1 10:48 2999
1008854 178 2005/7/28 10:59 208
1008840 114 2005/7/28 10:59 114
1008855 2124 2005/8/1 10:47 2215
1008842 1351 2005/8/1 10:47 1355
1008961 420 2005/7/30 09:27 429
1008963 1674 2005/7/31 10:33 1795
1008962 1674 2005/8/1 10:48 1966
1008844 115 2005/7/27 09:02 140
1008845 1355 2005/7/30 09:34 1525
1008846 1355 2005/8/1 10:44 1819
1008837 217 2005/7/28 10:57 220
1008838 870 2005/7/29 08:56 1000
1008839 870 2005/7/30 09:34 1070
1008995 162 2005/7/29 08:58 262
1008183 48 2005/7/15 03:04 800
1008901 90 2005/7/27 09:04 170
1008074 1000 2005/7/8 08:16 1010
1008074 1000 2005/7/8 19:30 2870
1008074 1000 2005/7/10 09:34 2340
1008996 463 2005/7/29 08:58 1160
1006399 50 2005/6/15 18:22 480
1008184 687 2005/7/11 08:12 3450
1008999 475 2005/7/29 08:58 1400
1008879 1025 2005/8/1 10:46 1480
生產單號 INT,
入庫數量 INT,
入庫日期日期 DATETIME,
本次送貨數量 INT)INSERT INTO #T SELECT 1008929,4080 ,'2005/07/26 09:03:00',13140
INSERT INTO #T SELECT 1008929,8700 ,'2005/07/27 08:59:00',13140
INSERT INTO #T SELECT 1008929,10050,'2005/07/28 10:59:00',13140
INSERT INTO #T SELECT 1008929,2310 ,'2005/07/30 09:34:00',13140
INSERT INTO #T SELECT 1009004,1020 ,'2005/08/01 10:47:00',20000
INSERT INTO #T SELECT 1009004,8160 ,'2005/08/03 08:34:00',20000
INSERT INTO #T SELECT 1009004,7650 ,'2005/08/03 08:35:00',20000SELECT
*
FROM
#T
WHERE
入庫日期日期 <=
(SELECT
TOP 1 A.入庫日期日期
FROM
#T A,
#T B
WHERE
A.入庫日期日期 >= B.入庫日期日期
GROUP BY
A.入庫日期日期,A.本次送貨數量
HAVING
SUM(B.入庫數量) >= A.本次送貨數量)
UNION
SELECT
t.*
FROM
#T t
WHERE
t.生產單號 IN(SELECT
生產單號
FROM
#T
GROUP BY
生產單號,本次送貨數量
HAVING
SUM(入庫數量)<本次送貨數量)--输出结果
/*
生產單號 入庫數量 入庫日期日期 本次送貨數量
-------- -------- ----------------------- ------------
1008929 4080 2005-07-26 09:03:00.000 13140
1008929 8700 2005-07-27 08:59:00.000 13140
1008929 10050 2005-07-28 10:59:00.000 13140
1009004 1020 2005-08-01 10:47:00.000 20000
1009004 7650 2005-08-03 08:35:00.000 20000
1009004 8160 2005-08-03 08:34:00.000 20000
*/
insert into @t
select '1008929',4080,'2005/7/26 09:03',13140 union
select '1008929',8700 ,'2005/7/27 08:59', 13140 union
select '1008929',10050 ,'2005/7/28 10:59' ,13140 union
select '1008929',2310 ,'2005/7/30 09:34' ,13140 union
select '1009004',1020 ,'2005/8/1 10:47' ,20000 union
select '1009004',8160 ,'2005/8/3 08:34' ,20000 union
select '1009004',7650, '2005/8/3 08:35' ,20000declare @limit int
select @limit=10000
select 生產單號,入庫數量,入庫日期日期,本次送貨數量
from @t a
where (case when (select sum(入庫數量) from @t where 生產單號=a.生產單號 and 入庫日期日期<=a.入庫日期日期)>=@limit
and (select sum(入庫數量) from @t where 生產單號=a.生產單號 and 入庫日期日期<a.入庫日期日期)>=@limit
then '1' else '0' end)='0'
order by 生產單號 ,入庫日期日期
/*
结果
--------------------------------------------------
生產單號,入庫數量,入庫日期日期,本次送貨數量
1008929 4080 2005-07-26 09:03:00.000 13140
1008929 8700 2005-07-27 08:59:00.000 13140
1009004 1020 2005-08-01 10:47:00.000 20000
1009004 8160 2005-08-03 08:34:00.000 20000
1009004 7650 2005-08-03 08:35:00.000 20000*/
但是語句還是有點問題,因為我的結果集中有很多條記錄
================================
--SELECT
-- *
--FROM
-- #T
--WHERE
入庫日期日期 <=
(SELECT
TOP 1 A.入庫日期日期
FROM
#T A,
#T B
WHERE
A.入庫日期日期 >= B.入庫日期日期
GROUP BY
A.入庫日期日期,A.本次送貨數量
HAVING
SUM(B.入庫數量) >= A.本次送貨數量)
====================================
這個語句只能返回最後一組取到的時間的值
COPY表進EXCEL 再導入MSSQL則可
t.*
FROM
#T t
WHERE
t.入庫日期日期 <=
(SELECT
TOP 1 A.入庫日期日期
FROM
#T A,
#T B
WHERE
A.生產單號 = B.生產單號
AND
A.生產單號 = t.生產單號
AND
A.入庫日期日期 >= B.入庫日期日期
GROUP BY
A.入庫日期日期,A.本次送貨數量
HAVING
SUM(B.入庫數量) >= A.本次送貨數量)
declare @limit int
select @limit=1000
select 生產單號,入庫數量,入庫日期日期,本次送貨數量
from tbt a
where (case when (select sum(入庫數量) from tbt where 入庫日期日期<=a.入庫日期日期)>=@limit
and (select sum(入庫數量) from tbt where 入庫日期日期<a.入庫日期日期)>=@limit
then '1' else '0' end)='0'
order by 入庫日期日期
/*新数据测试如下。
原来跟“生產單號”是没有关系的。开始写成了按照“生產單號”来了。
结果
--------------------------------------------------
生產單號,入庫數量,入庫日期日期,本次送貨數量
1006399 50 2005-06-15 18:22:00.000 480
1007557 40 2005-07-03 09:34:00.000 500
1007869 2060 2005-07-05 08:47:00.000 1680
*/
結貼了
順便貼出我的存儲過程
========================
/*
功能:列出所有送貨單所送產品的具體入庫數量和日期
日期:2005-08-19
*/
alter proc ship_zj3
@user_id varchar(8),--使用的用戶名
@sd datetime, --統計開始時間
@ed datetime --統計結束時間
with ENCRYPTIONas
--已經入庫部分 並建立臨時表
select s.khbh,s.inv_nbr,s.zwmc,s.fpzk,rtrim(ltrim(s.shddbh)) as shddbh,s.fprq,s.shddmc,
sh.dj,sh.shsl,sh.zje,sh.re,sh.flag, zsl/1000 as zl,
dd.client_ref as ddbh,ddmx.pfqcdj,
ltrim(rtrim(ddmx.cpbh)) as cpbh ,ddmx.cust_part,sh.scdbh,i.in_date,i.in_qty into #t
-- ddmx.cpbh,ddmx.cust_part
from SongHuDan as s,id_rep,SongHuDanMingXi as sh,
DingDan as dd ,DingDanMingXi as ddmx,in_inventory i
where id_rep.[user_id]=@user_id
and sh.scdbh=i.prod_nbr
and s.khbh=id_rep.kh_id
and s.fprq>=@sd
and s.fprq<@ed
and s.inv_nbr=sh.inv_nbr
and sh.ddxh=dd.ddxh
and sh.ddmxxh=ddmx.ddmxxh
and sh.flag=0
order by ltrim(rtrim(ddmx.cpbh)),s.fprq, s.khbh,s.shddbh,i.in_date--未入庫部分
insert into #t
select s.khbh,s.inv_nbr,s.zwmc,s.fpzk,rtrim(ltrim(s.shddbh)) as shddbh,s.fprq,s.shddmc,
sh.dj,sh.shsl,sh.zje,sh.re,sh.flag, zsl/1000 as zl,
dd.client_ref as ddbh,ddmx.pfqcdj,
ltrim(rtrim(ddmx.cpbh)) as cpbh ,ddmx.cust_part,sh.scdbh,s.fprq as in_date,sh.shsl as in_qty
-- ddmx.cpbh,ddmx.cust_part
from SongHuDan as s,id_rep,SongHuDanMingXi as sh,
DingDan as dd ,DingDanMingXi as ddmx
where id_rep.[user_id]=@user_id
and not exists(select prod_nbr from in_inventory where sh.scdbh=prod_nbr)
and s.khbh=id_rep.kh_id
and s.fprq>=@sd
and s.fprq<@ed
and s.inv_nbr=sh.inv_nbr
and sh.ddxh=dd.ddxh
and sh.ddmxxh=ddmx.ddmxxh
and sh.flag=0
order by ltrim(rtrim(ddmx.cpbh)),s.fprq, s.khbh,s.shddbh,i.in_date
--剔除入庫產品中的未動到的記錄,按先進先出原則
SELECT
t.*
FROM
#t t
WHERE
in_date <= (SELECT TOP 1 A.in_date
FROM
#t A,
#t B
WHERE
A.in_date >= B.in_date
and A.scdbh=B.scdbh
and A.scdbh=t.scdbh
and A.shsl=B.shsl
GROUP BY
A.in_date,A.shsl
HAVING
SUM(B.in_qty) >= A.shsl)
UNION
--加上入庫數量少於出庫數量的記錄
SELECT
t.*
FROM
#t t
WHERE
t.scdbh IN(SELECT
scdbh
FROM
#t
GROUP BY
scdbh,shsl
HAVING
SUM(in_qty)<shsl)
--刪除臨時表
drop table #t
========================