统计功能
采购合同基本表
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
现在要统计出没有按时到货以及还没有交货的采购合同的产品信息
[解决办法]
顯示的結果集是?
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
[解决办法]
------------------------------ 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 行受影响)*/
[解决办法]
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 采购合同基本表,采购产品信息表,到货信息表
[解决办法]
改改,借用小F的數據
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/*ID ContractNO CreateDate CustomID ID ContractNO ProductName ProductNo ProductNum ProductDate ContractNO ProductNo ProductNum MaxProductDate1 CN111 2011-09-10 00:00:00.000 155 2 CN111 纸箱 112 100 2011-09-30 00:00:00.000 NULL NULL NULL NULL2 CN112 2011-10-12 00:00:00.000 155 3 CN112 外壳 116 200 2011-10-30 00:00:00.000 NULL NULL NULL NULL*/
[解决办法]
我理解错误了？？不是按照时间来比对的么？
[解决办法]
修正:
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,ContractNO,sum(productNum) as productnum from 到货信息表 group by productno,ContractNO)b on a.productno=b.productno and a.ContractNO=b.ContractNOwhere a.ProductNum-isnull(b.ProductNum,0)>0/*ID ContractNO ProductName ProductNo ProductNum ProductDate 未到货----------- ---------- ----------- ----------- ----------- ----------------------- -----------2 CN111 纸箱 112 100 2011-09-30 00:00:00.000 1003 CN112 外壳 116 200 2011-10-30 00:00:00.000 2004 CN112 纸箱 112 300 2011-10-10 00:00:00.000 150(3 行受影响)*/godrop table 采购合同基本表,采购产品信息表,到货信息表
[解决办法]
--///////////////////////////////////---------------- 小为 向高手学习 ----///////////////////////////////////--------------create table jbb(id int identity(1,1),ContractNO varchar(20),CreateDate datetime,CustomID int)goinsert into jbbselect 'CN111', '2011-9-10', 155 union allselect 'CN112', '2011-10-12', 155create table cgb(id int identity(1,1),ContractNO varchar(20),ProductName varchar(20),ProductNo varchar(20),ProductNum int ,ProductDate datetime)goinsert into cgbselect 'CN111' ,'塑料袋', '111' ,100 ,'2011-9-20' union allselect 'CN111' ,'纸箱', '112' ,100 ,'2011-9-30' union allselect 'CN112' ,'外壳 ', '116' ,200 ,'2011-10-30' union allselect 'CN112' ,'纸箱', '112' ,300 ,'2011-10-10' create table dhb(id int identity(1,1),ContractNO varchar(20),ProductName varchar(20),ProductNo varchar(20),ProductNum int ,ProductDate datetime)goinsert into dhbselect 'CN111' ,'塑料袋', '111' ,50 ,'2011-9-15' union allselect 'CN111' ,'塑料袋', '111' ,50 ,'2011-9-20' union allselect 'CN112' ,'纸箱', '112' ,150 ,'2011-10-10' --现在要统计出没有按时到货以及还没有交货的采购合同的产品信息 select c.ContractNO,c.ProductName,c.ProductDate '到货日期',d.ProductDate'交货日期',j.CreateDate '制作日期' from cgb c inner join jbb j on c.ContractNO=j.ContractNO inner join dhb d on d.ContractNO=c.ContractNO where c.ProductDate>j.CreateDate or d.ProductDate>c.ProductDate --为按时到货的--//结果ContractNO ProductName 到货日期 交货日期 制作日期-------------------- -------------------- ----------------------- ----------------------- -----------------------CN111 塑料袋 2011-09-20 00:00:00.000 2011-09-15 00:00:00.000 2011-09-10 00:00:00.000CN111 塑料袋 2011-09-20 00:00:00.000 2011-09-20 00:00:00.000 2011-09-10 00:00:00.000CN111 纸箱 2011-09-30 00:00:00.000 2011-09-15 00:00:00.000 2011-09-10 00:00:00.000CN111 纸箱 2011-09-30 00:00:00.000 2011-09-20 00:00:00.000 2011-09-10 00:00:00.000CN112 外壳 2011-10-30 00:00:00.000 2011-10-10 00:00:00.000 2011-10-12 00:00:00.000(5 行受影响)
[解决办法]
create table [采购合同基本表]([ID] int,[ContractNO] varchar(5),[CreateDate] datetime,[CustomID] int)create table [采购产品信息表]([ID] int,[ContractNO] varchar(5),[ProductName] varchar(6),[ProductNo] int,[ProductNum] int,[ProductDate] datetime)create table [到货信息表]([ID] int,[ContractNO] varchar(5),[ProductName] varchar(6),[ProductNo] int,[ProductNum] int,[ProductDate] datetime)insert [采购合同基本表]select 1,'CN111','2011-9-10',155 union allselect 2,'CN112','2011-10-12',155insert [采购产品信息表]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'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 a.*,b.ProductName,b.ProductNo,b.ProductNum,b.ProductDatefrom 采购合同基本表 ainner join 采购产品信息表 b on a.ContractNO=b.ContractNOleft join 到货信息表 c on b.ContractNO=c.ContractNO and b.ProductNo=c.ProductNowhere (c.ContractNO is null and c.ProductNo is null) --条件1 还没有交货or c.ProductDate>b.ProductDate --条件2 没有按时到货ID ContractNO CreateDate CustomID ProductName ProductNo ProductNum ProductDate----------- ---------- ----------------------- ----------- ----------- ----------- ----------- -------------1 CN111 2011-09-10 00:00:00.000 155 纸箱 112 100 2011-09-30 2 CN112 2011-10-12 00:00:00.000 155 外壳 116 200 2011-10-30
[解决办法]
select c.ContractNO,c.ProductName,c.ProductDate 'c 到货日期',null' d交货日期',j.CreateDate '制作日期' from cgb c inner join jbb j on c.ContractNO=j.ContractNO where c.ProductDate>j.CreateDate union all select c.ContractNO,c.ProductName,c.ProductDate 'c 到货日期',d.ProductDate' d交货日期', null '制作日期' from cgb c left join dhb d on d.ContractNO=c.ContractNO where d.ProductDate>c.ProductDate --为按时到货的--//ContractNO ProductName c 到货日期 d交货日期 制作日期-------------------- -------------------- ----------------------- ----------------------- -----------------------CN111 塑料袋 2011-09-20 00:00:00.000 NULL 2011-09-10 00:00:00.000CN111 纸箱 2011-09-30 00:00:00.000 NULL 2011-09-10 00:00:00.000CN112 外壳 2011-10-30 00:00:00.000 NULL 2011-10-12 00:00:00.000(3 行受影响)
[解决办法]
create table #t1(ID int identity(1,1), ContractNO varchar(50), CreateDate datetime, CustomID int)insert into #t1 values('CN111', '2011-9-10', 155)insert into #t1 values('CN112 ', '2011-10-12', 155)create table #t2(ID int identity(1,1),ContractNO varchar(50), ProductName varchar(50), ProductNo varchar(50), ProductNum int , ProductDate datetime)insert into #t2 values('CN111', '塑料袋', 111, 100, '2011-9-20')insert into #t2 values('CN111', '纸箱', 112, 100, '2011-9-30')insert into #t2 values('CN112', '外壳', 116, 200, '2011-10-30')insert into #t2 values('CN112', '纸箱', 112, 300, '2011-10-10')create table #t3(ID int identity(1,1), ContractNO varchar(50), ProductName varchar(50), ProductNo varchar(50), ProductNum int, ProductDate datetime)insert into #t3 values('CN111', '塑料袋', 111, 50, '2011-9-15')insert into #t3 values('CN111', '塑料袋', 111, 50, '2011-9-20')insert into #t3 values('CN112 ', '纸箱', 112, 150, '2011-10-10')select * from #t2where ProductNum != isnull((select sum(ProductNum) from #t3 where #t3.ContractNO = #t2.ContractNO and #t3.ProductNo = #t2.ProductNo and #t3.ProductDate <= #t2.ProductDate ),0)drop table #t1drop table #t2drop table #t3-----------------------------------------ID ContractNO ProductName ProductNo ProductNum ProductDate----------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- ----------- -----------------------2 CN111 纸箱 112 100 2011-09-30 00:00:00.0003 CN112 外壳 116 200 2011-10-30 00:00:00.0004 CN112 纸箱 112 300 2011-10-10 00:00:00.000(3 行受影响)