求最后一次采购收货日期
(按同一供应商、同一货品、最后一次采购日期)
关联关系: M_DRAW.BILLID=M_DRAWD.BILLID
A_DRAW主表
单号 供应商 日期
BILLID VENDORID BD
1001 3003 2009-09-09
1003 3003 2009-09-11
1005 3007 2009-09-11
1004 3008 2009-09-20
1007 3008 2009-09-20A_DRAWD细表
单号 序号 货品 数量
BILLID ITEMNO GOODSID , QTY
1001 1 2002 100
1001 2 2004 200
1001 3 2002 50
1003 1 2002 80 --这里同单号、同日期、同货品、同客户
1003 2 2003 55
1003 3 2002 90 --这里同单号、同日期、同货品、同客户 1005 1 4001 50
1004 1 5001 78
1007 1 2007 68
求结果如下:
BILLID VENDORID BD GOODSID QTY
1003 3003 2009-09-11 2002 90
1003 3003 2009-09-11 2003 55
1001 3003 2009-09-09 2004 200
1005 3007 2009-09-11 4001 50
1004 3008 2009-09-20 5001 78
1007 3008 2009-09-20 2007 68
(按同一供应商、同一货品、最后一次采购日期)
关联关系: M_DRAW.BILLID=M_DRAWD.BILLID
A_DRAW主表
单号 供应商 日期
BILLID VENDORID BD
1001 3003 2009-09-09
1003 3003 2009-09-11
1005 3007 2009-09-11
1004 3008 2009-09-20
1007 3008 2009-09-20A_DRAWD细表
单号 序号 货品 数量
BILLID ITEMNO GOODSID , QTY
1001 1 2002 100
1001 2 2004 200
1001 3 2002 50
1003 1 2002 80 --这里同单号、同日期、同货品、同客户
1003 2 2003 55
1003 3 2002 90 --这里同单号、同日期、同货品、同客户 1005 1 4001 50
1004 1 5001 78
1007 1 2007 68
求结果如下:
BILLID VENDORID BD GOODSID QTY
1003 3003 2009-09-11 2002 90
1003 3003 2009-09-11 2003 55
1001 3003 2009-09-09 2004 200
1005 3007 2009-09-11 4001 50
1004 3008 2009-09-20 5001 78
1007 3008 2009-09-20 2007 68
select a.BILLID,VENDORID ,BD , GOODSID, QTY into #t
from A_DRAW a,A_DRAWD b
where M_DRAW.BILLID=M_DRAWD.BILLID select * from #t t
where not exists(select * from #t where VENDORID=t.VENDORID and GOODSID=t.GOODSID and bd>t.bd)
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-09-24 16:07:21
-- Version:
-- 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.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[A_DRAW]
if object_id('[A_DRAW]') is not null drop table [A_DRAW]
go
create table [A_DRAW]([BILLID] int,[VENDORID] int,[BD] datetime)
insert [A_DRAW]
select 1001,3003,'2009-09-09' union all
select 1003,3003,'2009-09-11' union all
select 1005,3007,'2009-09-11' union all
select 1004,3008,'2009-09-20' union all
select 1007,3008,'2009-09-20'
--> 测试数据:[A_DRAWD]
if object_id('[A_DRAWD]') is not null drop table [A_DRAWD]
go
create table [A_DRAWD]([BILLID] int,[ITEMNO] int,[GOODSID] int,[QTY] int)
insert [A_DRAWD]
select 1001,1,2002,100 union all
select 1001,2,2004,200 union all
select 1001,3,2002,50 union all
select 1003,1,2002,80 union all
select 1003,2,2003,55 union all
select 1003,3,2002,90 union all
select 1005,1,4001,50 union all
select 1004,1,5001,78 union all
select 1007,1,2007,68
--------------开始查询--------------------------
select
a.BILLID,a.VENDORID,a.BD,b.GOODSID,b.QTY
from
[A_DRAW] a ,[A_DRAWD] b
where
a.BILLID=b.BILLID
and
not exists(select 1 from [A_DRAW] where BILLID=a.BILLID and VENDORID=a.VENDORID and bd<a.bd)
----------------结果----------------------------
/*BILLID VENDORID BD GOODSID QTY
----------- ----------- ----------------------- ----------- -----------
1001 3003 2009-09-09 00:00:00.000 2002 100
1001 3003 2009-09-09 00:00:00.000 2004 200
1001 3003 2009-09-09 00:00:00.000 2002 50
1003 3003 2009-09-11 00:00:00.000 2002 80
1003 3003 2009-09-11 00:00:00.000 2003 55
1003 3003 2009-09-11 00:00:00.000 2002 90
1005 3007 2009-09-11 00:00:00.000 4001 50
1004 3008 2009-09-20 00:00:00.000 5001 78
1007 3008 2009-09-20 00:00:00.000 2007 68(9 行受影响)
*/
DROP TABLE A_DRAW
Go
CREATE TABLE A_DRAW(BILLID int, VENDORID int, BD datetime)
insert A_DRAW select
1001, 3003 , '2009-09-09' union all select
1003, 3003 , '2009-09-11' union all select
1005, 3007 , '2009-09-11' union all select
1004, 3008 , '2009-09-20' union all select
1007, 3008, '2009-09-20' IF OBJECT_ID('A_DRAWD') IS NOT NULL
DROP TABLE A_DRAWD
Go
CREATE TABLE A_DRAWd(BILLID int, ITEMNO int, GOODSID int, QTY int)
insert A_DRAWd select
1001 , 1 , 2002, 100 union all select
1001 , 2 , 2004 , 200 union all select
1001 , 3 , 2002 , 50 union all select
1003 , 1 , 2002 , 80 union all select--这里同单号、同日期、同货品、同客户
1003 , 2 , 2003 , 55 union all select
1003 , 3 , 2002 , 90 union all select--这里同单号、同日期、同货品、同客户
1005 , 1 , 4001 , 50 union all select
1004 , 1 , 5001 , 78 union all select
1007 , 1 , 2007 , 68
select a.BILLID,VENDORID ,BD , GOODSID,ITEMNO, QTY into #t
from A_DRAW a,A_DRAWD b
where a.BILLID=b.BILLID select * from #t t
where not exists(
select * from #t where VENDORID=t.VENDORID and GOODSID=t.GOODSID and bd>t.bd )BILLID VENDORID BD GOODSID ITEMNO QTY
----------- ----------- ----------------------- ----------- ----------- -----------
1001 3003 2009-09-09 00:00:00.000 2004 2 200
1003 3003 2009-09-11 00:00:00.000 2002 1 80
1003 3003 2009-09-11 00:00:00.000 2003 2 55
1003 3003 2009-09-11 00:00:00.000 2002 3 90
1005 3007 2009-09-11 00:00:00.000 4001 1 50
1004 3008 2009-09-20 00:00:00.000 5001 1 78
1007 3008 2009-09-20 00:00:00.000 2007 1 68(7 行受影响)
drop table #t
IF OBJECT_ID('A_DRAW') IS NOT NULL
DROP TABLE A_DRAW
Go
CREATE TABLE A_DRAW(BILLID int, VENDORID int, BD datetime)
insert A_DRAW select
1001, 3003 , '2009-09-09' union all select
1003, 3003 , '2009-09-11' union all select
1005, 3007 , '2009-09-11' union all select
1004, 3008 , '2009-09-20' union all select
1007, 3008, '2009-09-20' IF OBJECT_ID('A_DRAWD') IS NOT NULL
DROP TABLE A_DRAWD
Go
CREATE TABLE A_DRAWd(BILLID int, ITEMNO int, GOODSID int, QTY int)
insert A_DRAWd select
1001 , 1 , 2002, 100 union all select
1001 , 2 , 2004 , 200 union all select
1001 , 3 , 2002 , 50 union all select
1003 , 1 , 2002 , 80 union all select--这里同单号、同日期、同货品、同客户
1003 , 2 , 2003 , 55 union all select
1003 , 3 , 2002 , 90 union all select--这里同单号、同日期、同货品、同客户
1005 , 1 , 4001 , 50 union all select
1004 , 1 , 5001 , 78 union all select
1007 , 1 , 2007 , 68
select a.BILLID,VENDORID ,BD , GOODSID,ITEMNO, QTY into #t
from A_DRAW a,A_DRAWD b
where a.BILLID=b.BILLID select * from #t t
where not exists(
select * from #t where VENDORID=t.VENDORID and GOODSID=t.GOODSID and bd>t.bd ) BILLID VENDORID BD GOODSID ITEMNO QTY
----------- ----------- ----------------------- ----------- ----------- -----------
1001 3003 2009-09-09 00:00:00.000 2004 2 200
1003 3003 2009-09-11 00:00:00.000 2002 1 80
1003 3003 2009-09-11 00:00:00.000 2003 2 55
1003 3003 2009-09-11 00:00:00.000 2002 3 90
1005 3007 2009-09-11 00:00:00.000 4001 1 50
1004 3008 2009-09-20 00:00:00.000 5001 1 78
1007 3008 2009-09-20 00:00:00.000 2007 1 68 (7 行受影响)
drop table #t
---修改一下,把qty=80那条记录过滤掉IF OBJECT_ID('A_DRAW') IS NOT NULL
DROP TABLE A_DRAW
Go
CREATE TABLE A_DRAW(BILLID int, VENDORID int, BD datetime)
insert A_DRAW select
1001, 3003 , '2009-09-09' union all select
1003, 3003 , '2009-09-11' union all select
1005, 3007 , '2009-09-11' union all select
1004, 3008 , '2009-09-20' union all select
1007, 3008, '2009-09-20' IF OBJECT_ID('A_DRAWD') IS NOT NULL
DROP TABLE A_DRAWD
Go
CREATE TABLE A_DRAWd(BILLID int, ITEMNO int, GOODSID int, QTY int)
insert A_DRAWd select
1001 , 1 , 2002, 100 union all select
1001 , 2 , 2004 , 200 union all select
1001 , 3 , 2002 , 50 union all select
1003 , 1 , 2002 , 80 union all select--这里同单号、同日期、同货品、同客户
1003 , 2 , 2003 , 55 union all select
1003 , 3 , 2002 , 90 union all select--这里同单号、同日期、同货品、同客户
1005 , 1 , 4001 , 50 union all select
1004 , 1 , 5001 , 78 union all select
1007 , 1 , 2007 , 68
select a.BILLID,VENDORID ,BD , GOODSID,ITEMNO, QTY into #t
from A_DRAW a,A_DRAWD b
where a.BILLID=b.BILLID select * from #t t
where not exists(
select * from #t where VENDORID=t.VENDORID and GOODSID=t.GOODSID and (bd>t.bd or bd=t.bd and ITEMNO>t.ITEMNO))BILLID VENDORID BD GOODSID ITEMNO QTY
----------- ----------- ----------------------- ----------- ----------- -----------
1001 3003 2009-09-09 00:00:00.000 2004 2 200
1003 3003 2009-09-11 00:00:00.000 2003 2 55
1003 3003 2009-09-11 00:00:00.000 2002 3 90
1005 3007 2009-09-11 00:00:00.000 4001 1 50
1004 3008 2009-09-20 00:00:00.000 5001 1 78
1007 3008 2009-09-20 00:00:00.000 2007 1 68(6 行受影响)
drop table #t
from A_DRAW a,A_DRAWD b
where a.BILLID=b.BILLID select * from #t t
where not exists(
select * from #t where VENDORID=t.VENDORID and GOODSID=t.GOODSID and bd>t.bd )
太帅了,学习,