34,590
社区成员
发帖
与我相关
我的任务
分享
Select *
From 采购产品信息表 As T1 Left Join 到货信息表 As T2
On (T1.ContractNO = T2.ContractNO And T1.ProductNo = T2.ProductNo)
Where (T1.ProductDate < T2.ProductDate) Or (T1.ProductNum > T2.ProductNum) Or isNull(T2.ProductNo,'') = ''
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 #t2
where 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 #t1
drop table #t2
drop table #t3
-----------------------------------------------------------------------------
ID ContractNO ProductName ProductNo ProductNum ProductDate
----------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- ----------- -----------------------
2 CN111 纸箱 112 100 2011-09-30 00:00:00.000
3 CN112 外壳 116 200 2011-10-30 00:00:00.000
4 CN112 纸箱 112 300 2011-10-10 00:00:00.000
(3 行受影响)
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.000
CN111 纸箱 2011-09-30 00:00:00.000 NULL 2011-09-10 00:00:00.000
CN112 外壳 2011-10-30 00:00:00.000 NULL 2011-10-12 00:00:00.000
(3 行受影响)
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 all
select 2,'CN112','2011-10-12',155
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'
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 a.*,b.ProductName,b.ProductNo,b.ProductNum,b.ProductDate
from 采购合同基本表 a
inner join 采购产品信息表 b on a.ContractNO=b.ContractNO
left join 到货信息表 c on b.ContractNO=c.ContractNO and b.ProductNo=c.ProductNo
where
(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
--///////////////////////////////////--------------
-- 小为 向高手学习 --
--///////////////////////////////////--------------
create table jbb(id int identity(1,1),ContractNO varchar(20),CreateDate datetime,CustomID int)
go
insert into jbb
select 'CN111', '2011-9-10', 155 union all
select 'CN112', '2011-10-12', 155
create table cgb(id int identity(1,1),ContractNO varchar(20),ProductName varchar(20),ProductNo varchar(20),ProductNum int ,ProductDate datetime)
go
insert into cgb
select 'CN111' ,'塑料袋', '111' ,100 ,'2011-9-20' union all
select 'CN111' ,'纸箱', '112' ,100 ,'2011-9-30' union all
select 'CN112' ,'外壳 ', '116' ,200 ,'2011-10-30' union all
select '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)
go
insert into dhb
select 'CN111' ,'塑料袋', '111' ,50 ,'2011-9-15' union all
select 'CN111' ,'塑料袋', '111' ,50 ,'2011-9-20' union all
select '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.000
CN111 塑料袋 2011-09-20 00:00:00.000 2011-09-20 00:00:00.000 2011-09-10 00:00:00.000
CN111 纸箱 2011-09-30 00:00:00.000 2011-09-15 00:00:00.000 2011-09-10 00:00:00.000
CN111 纸箱 2011-09-30 00:00:00.000 2011-09-20 00:00:00.000 2011-09-10 00:00:00.000
CN112 外壳 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(10),CreateDate datetime,CustomID int)
insert into 采购合同基本表 select 1,'CN111','2011-9-10', 155
insert into 采购合同基本表 select 2,'CN112','2011-10-12', 155
create 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.ContractNO
where a.ProductNum-isnull(b.ProductNum,0)>0
/*
ID ContractNO ProductName ProductNo ProductNum ProductDate 未到货
----------- ---------- ----------- ----------- ----------- ----------------------- -----------
2 CN111 纸箱 112 100 2011-09-30 00:00:00.000 100
3 CN112 外壳 116 200 2011-10-30 00:00:00.000 200
4 CN112 纸箱 112 300 2011-10-10 00:00:00.000 150
(3 行受影响)
*/
go
drop table 采购合同基本表,采购产品信息表,到货信息表
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 MaxProductDate
1 CN111 2011-09-10 00:00:00.000 155 2 CN111 纸箱 112 100 2011-09-30 00:00:00.000 NULL NULL NULL NULL
2 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', 155
insert into 采购合同基本表 select 2,'CN112','2011-10-12', 155
create 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.productno
where a.ProductNum-isnull(b.ProductNum,0)>0
/*
ID ContractNO ProductName ProductNo ProductNum ProductDate 未到货
----------- ---------- ----------- ----------- ----------- ----------------------- -----------
3 CN112 外壳 116 200 2011-10-30 00:00:00.000 200
4 CN112 纸箱 112 300 2011-10-10 00:00:00.000 150
(2 行受影响)
*/
go
drop table 采购合同基本表,采购产品信息表,到货信息表
----------------------------------------------------------------
-- 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 行受影响)
*/
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