非主键关联问题

Question

有如下两个表：
table1:采购入库单
入库单号     供应商      品种       入库日期
1            aaa         1          2009-08-06
2            bbb         2          2009-08-06table2:采购合同
合同号       供应商      品种       合同签定日       单价
1            aaa         1          2008-08-06       5
2            aaa         1          2009-07-06       6
3            aaa         1          2009-09-06       7要找到该入库物品的合同单价：
输出格式如下：
入库单号     供应商      品种       入库日期        单价
1            aaa         1          2009-08-06      6
单价离入库时间最近的合同（并且合同签定日要在入库之前）一致。

Answer 1

select m.* , isnull((select top 1 单价 from 采购合同 n where 供应商 = m.供应商 and 合同签定日 < m.入库日期 order by 合同签定日 desc) , '0') 单价 from 采购入库单 m

Answer 2

create table 采购入库单(入库单号 int, 供应商 varchar(10), 品种 int, 入库日期 datetime)
insert into 采购入库单 values(1 , 'aaa' , 1, '2009-08-06')
insert into 采购入库单 values(2 , 'bbb' , 2, '2009-08-06')
create table 采购合同(合同号 int, 供应商 varchar(10), 品种 int, 合同签定日 datetime, 单价 int)
insert into 采购合同 values(1 , 'aaa' , 1 , '2008-08-06' , 5)
insert into 采购合同 values(2 , 'aaa' , 1 , '2009-07-06' , 6)
insert into 采购合同 values(3 , 'aaa' , 1 , '2009-09-06' , 7)
goselect m.* , isnull((select top 1 单价 from 采购合同 n where 供应商 = m.供应商 and 合同签定日 < m.入库日期 order by 合同签定日 desc) , '0') 单价 from 采购入库单 mdrop table 采购入库单,采购合同/*入库单号        供应商        品种          入库日期                                                   单价          
----------- ---------- ----------- ------------------------------------------------------ ----------- 
1           aaa        1           2009-08-06 00:00:00.000                                6
2           bbb        2           2009-08-06 00:00:00.000                                0（所影响的行数为 2 行）
*/

Answer 3

楼主的结果没错？
--> 测试数据: @table1
declare @table1 table (入库单号 int,供应商 varchar(3),品种 int,入库日期 datetime)
insert into @table1
select 1,'aaa',1,'2009-08-06' union all
select 2,'bbb',2,'2009-08-06'
--> 测试数据: @table2
declare @table2 table (合同号 int,供应商 varchar(3),品种 int,合同签定日 datetime,单价 int)
insert into @table2
select 1,'aaa',1,'2008-08-06',5 union all
select 2,'aaa',1,'2009-07-06',6 union all
select 3,'aaa',1,'2009-09-06',7select b.* from @table2 b,@table1 a
where  a.品种=b. 品种 and 合同签定日<=入库日期 and 
not exists (select * from @table2 where   a.品种=b. 品种 and 合同签定日>b.合同签定日 and 合同签定日<=入库日期)
 合同号         供应商  品种          合同签定日                   单价
----------- ---- ----------- ----------------------- -----------
2           aaa  1           2009-07-06 00:00:00.000 6(1 行受影响)

Answer 4

declare @tb1 table([入库单号] int,[供应商] varchar(3),[品种] int,[入库日期] datetime)
insert @tb1
select 1,'aaa',1,'2009-08-06' union all
select 2,'bbb',2,'2009-08-06'declare @tb2 table([合同号] int,[供应商] varchar(3),[品种] int,[合同签定日] datetime,[单价] int)
insert @tb2
select 1,'aaa',1,'2008-08-06',5 union all
select 2,'aaa',1,'2009-07-06',6 union all
select 3,'aaa',1,'2009-09-06',7select t1.*,(select top 1 单价 from @tb2 where t1.[供应商] = [供应商] order by datediff(hh,入库日期,合同签定日) asc) as [单价]
from @tb1 t1
where (select top 1 单价 from @tb2 where t1.[供应商] = [供应商] order by datediff(hh,入库日期,合同签定日) asc) is not null/*
入库单号        供应商  品种          入库日期                    单价
----------- ---- ----------- ----------------------- -----------
1           aaa  1           2009-08-06 00:00:00.000 5(1 row(s) affected)

Answer 5

----------------------------------------------------------------
-- Author  :fredrickhu(我是小F，向高手学习)
-- Date    :2009-11-20 15:04:38
-- Version:
--      Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) 
-- Nov 24 2008 13:01:59 
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据：[采购入库单]
if object_id('[采购入库单]') is not null drop table [采购入库单]
go 
create table [采购入库单]([入库单号] int,[供应商] varchar(3),[品种] int,[入库日期] datetime)
insert [采购入库单]
select 1,'aaa',1,'2009-08-06' union all
select 2,'bbb',2,'2009-08-06'
--> 测试数据：[采购合同]
if object_id('[采购合同]') is not null drop table [采购合同]
go 
create table [采购合同]([合同号] int,[供应商] varchar(3),[品种] int,[合同签定日] datetime,[单价] int)
insert [采购合同]
select 1,'aaa',1,'2008-08-06',5 union all
select 2,'aaa',1,'2009-07-06',6 union all
select 3,'aaa',1,'2009-09-06',7
--------------开始查询--------------------------
select 
  b.*
from
  [采购入库单] a,采购合同 b
where
  合同签定日<=入库日期
and 
 not exists(select 1 from 采购合同 where 品种=b.品种 and 合同签定日>b.合同签定日 and 合同签定日<=入库日期)
and
  a.品种=b.品种
----------------结果----------------------------
/* 合同号         供应商  品种          合同签定日                   单价
----------- ---- ----------- ----------------------- -----------
2           aaa  1           2009-07-06 00:00:00.000 6(1 行受影响)*/

Answer 6

搞错了一下
declare @tb1 table([入库单号] int,[供应商] varchar(3),[品种] int,[入库日期] datetime)
insert @tb1
select 1,'aaa',1,'2009-08-06' union all
select 2,'bbb',2,'2009-08-06'declare @tb2 table([合同号] int,[供应商] varchar(3),[品种] int,[合同签定日] datetime,[单价] int)
insert @tb2
select 1,'aaa',1,'2008-08-06',5 union all
select 2,'aaa',1,'2009-07-06',6 union all
select 3,'aaa',1,'2009-09-06',7select distinct t1.*,(select top 1 单价 from @tb2 where t1.[供应商] = [供应商] and [入库日期]>[合同签定日] order by 合同签定日 desc) as [单价]
from @tb1 t1 inner join @tb2 t2 on t1.[供应商] = t2.[供应商]/*
入库单号        供应商  品种          入库日期                    单价
----------- ---- ----------- ----------------------- -----------
1           aaa  1           2009-08-06 00:00:00.000 6(1 row(s) affected)