统计功能

Question

采购合同基本表
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
现在要统计出没有按时到货以及还没有交货的采购合同的产品信息

Answer 1

看錯用inner join 連接3表

Answer 2

顯示的結果集是?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

Answer 3

----------------------------------------------------------------
-- 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 行受影响)*/

Answer 4

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 采购合同基本表,采购产品信息表,到货信息表

Answer 5

改改,借用小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 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
*/
[/code]

Answer 6

修正:
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 采购合同基本表,采购产品信息表,到货信息表

Answer 7

--///////////////////////////////////--------------
--           小为      向高手学习                --
--///////////////////////////////////--------------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', 155create 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 行受影响)

Answer 8

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',155insert [采购产品信息表]
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 

Answer 9

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 行受影响)

Answer 10

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 行受影响)

Answer 11

把上面各位大大写得where条件反过来不就是时到货的记录

Answer 12

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)

Answer 13

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,'') = ''

Answer 14

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,'') = ''查询出来未到货的、到货数量不足的、未按期到货的