SELECT 
*
FROM 采购合同基本表 AS a
    INNER JOIN 采购产品信息表 AS b ON a.ContractNO=b.ContractNO
    LEFT JOIN (SELECT ContractNO,ProductNo,SUM(ProductNum) AS ProductNum,MAX(ProductDate) AS MaxProductDate from 到货信息表 GROUP BY ContractNO,ProductNo) AS c ON c.ContractNO=b.ContractNO AND b.ProductNo=c.ProductNo AND c.ProductNum<=b.ProductNum AND c.MaxProductDate<b.ProductDate
WHERE c.ContractNO IS null

------解决方案--------------------
SQL code
----------------------------
-- Author  :fredrickhu(小F，向高手学习)
-- Date    :2011-10-12 16:29:03
-- Version:
--      Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86) 
--    Apr 22 2011 11:57:00 
--    Copyright (c) Microsoft Corporation
--    Enterprise Evaluation Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)
--
----------------------------
--> 测试数据：[采购合同基本表]
if object_id('[采购合同基本表]') is not null drop table [采购合同基本表]
go 
create table [采购合同基本表]([ID] int,[ContractNO] varchar(5),[CreateDate] datetime,[CustomID] int)
insert [采购合同基本表]
select 1,'CN111','2011-9-10',155 union all
select 2,'CN112','2011-10-12',155
--> 测试数据：[采购产品信息表]
if object_id('[采购产品信息表]') is not null drop table [采购产品信息表]
go 
create table [采购产品信息表]([ID] int,[ContractNO] varchar(5),[ProductName] varchar(6),[ProductNo] int,[ProductNum] int,[ProductDate] datetime)
insert [采购产品信息表]
select 1,'CN111','塑料袋',111,100,'2011-9-20' union all
select 2,'CN111','纸箱',112,100,'2011-9-30' union all
select 3,'CN112','外壳',116,200,'2011-10-30' union all
select 4,'CN112','纸箱',112,300,'2011-10-10'
--> 测试数据：[到货信息表]
if object_id('[到货信息表]') is not null drop table [到货信息表]
go 
create table [到货信息表]([ID] int,[ContractNO] varchar(5),[ProductName] varchar(6),[ProductNo] int,[ProductNum] int,[ProductDate] datetime)
insert [到货信息表]
select 1,'CN111','塑料袋',111,50,'2011-9-15' union all
select 2,'CN111','塑料袋',111,50,'2011-9-20' union all
select 3,'CN112','纸箱',112,150,'2011-10-10'
--------------开始查询--------------------------
select
  b.*
from
  采购合同基本表 a,采购产品信息表 b,到货信息表 c
where
  a.ContractNO=b.ContractNO
and
  b.ProductName=c.ProductName
and
  a.ContractNO=c.ContractNO
and
  a.CreateDate<b.ProductDate
and
  c.ProductDate<b.ProductDate
----------------结果----------------------------
/* ID          ContractNO ProductName ProductNo   ProductNum  ProductDate
----------- ---------- ----------- ----------- ----------- -----------------------
1           CN111      塑料袋         111         100         2011-09-20 00:00:00.000

(1 行受影响)

*/

------解决方案--------------------
SQL code
create table 采购合同基本表(ID int,ContractNO varchar(10),CreateDate datetime,CustomID int)
i