采购合同基本表
ID(自动编号) ContractNO(合同编号) CreateDate（制作日期） CustomID（供应商ID）
1 CN111 2011-9-10 155
2 CN112 2011-10-12 155
采购产品信息表
ID ContractNO ProductName（品名） ProductNo（产品编号） ProductNum（数量） ProductDate（交货期）
1 CN111 塑料袋 111 100 2011-9-20
2 CN111 纸箱 112 100 2011-9-30
3 CN112 外壳 116 200 2011-10-30
4 CN112 纸箱 112 300 2011-10-10
到货信息表
ID ContractNO ProductName（品名） ProductNo（产品编号） ProductNum（到货数量） ProductDate（到货期）
1 CN111 塑料袋 111 50 2011-9-15
2 CN111 塑料袋 111 50 2011-9-20
3 CN112 纸箱 112 150 2011-10-10
现在要统计出没有按时到货以及还没有交货的采购合同的产品信息
------解决方案--------------------
顯示的結果集是?
- SQL code
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.ProductDateWHERE 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 allselect 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 allselect 2,'CN111','纸箱',112,100,'2011-9-30' union allselect 3,'CN112','外壳',116,200,'2011-10-30' union allselect 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 allselect 2,'CN111','塑料袋',111,50,'2011-9-20' union allselect 3,'CN112','纸箱',112,150,'2011-10-10'--------------开始查询--------------------------select b.*from 采购合同基本表 a,采购产品信息表 b,到货信息表 cwhere a.ContractNO=b.ContractNOand b.ProductName=c.ProductNameand a.ContractNO=c.ContractNOand a.CreateDate<b.ProductDateand 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)insert into 采购合同基本表 select 1,'CN111','2011-9-10', 155insert into 采购合同基本表 select 2,'CN112','2011-10-12', 155create table 采购产品信息表(ID int,ContractNO varchar(10),ProductName varchar(10), ProductNo int,ProductNum int, ProductDate datetime)insert into 采购产品信息表 select 1,'CN111','塑料袋',111,100,'2011-9-20'insert into 采购产品信息表 select 2,'CN111','纸箱',112,100,'2011-9-30'insert into 采购产品信息表 select 3,'CN112','外壳',116,200,'2011-10-30'insert into 采购产品信息表 select 4,'CN112','纸箱',112,300,'2011-10-10'create table 到货信息表(ID int,ContractNO varchar(10),ProductName varchar(10), ProductNo int, ProductNum int,ProductDate datetime)insert into 到货信息表 select 1,'CN111','塑料袋',111,50,'2011-9-15'insert into 到货信息表 select 2,'CN111','塑料袋',111,50,'2011-9-20'insert into 到货信息表 select 3,'CN112','纸箱',112,150,'2011-10-10'go--尚未到货:select a.*,a.ProductNum-isnull(b.ProductNum,0) as 未到货from 采购产品信息表 a left join (select productNo,sum(productNum) as productnum from 到货信息表 group by productno)b on a.productno=b.productnowhere a.ProductNum-isnull(b.ProductNum,0)>0/*ID ContractNO ProductName ProductNo ProductNum ProductDate 未到货----------- ---------- ----------- ----------- ----------- ----------------------- -----------3 CN112 外壳 116 200 2011-10-30 00:00:00.000 2004 CN112 纸箱 112 300 2011-10-10 00:00:00.000 150(2 行受影响)*/godrop table 采购合同基本表,采购产品信息表,到货信息表