create table a
(
INVTID varchar(1),
Per varchar(2),
Qty int
)create table b
(
INVTID varchar(1),
Per varchar(2),
Price int
)
insert into a select 'a', '01', 20
insert into a select 'a', '02', 30
insert into a select 'a', '03', 40
insert into a select 'b', '01', 15
insert into a select 'b', '02', 25
insert into a select 'b', '03', 35
insert into a select 'b', '04', 45insert into b select 'a', '01', 12
insert into b select 'a', '03', 14
insert into b select 'b', '02', 21
insert into b select 'b', '03', 22--查询
select tt.invtid,tt.per,tt.qty,
price = (select top 1 price from b where invtid = tt.invtid and per <= tt.per order by per desc)
from a tt--结果
a 01 20 12
a 02 30 12
a 03 40 14
b 01 15 NULL
b 02 25 21
b 03 35 22
b 04 45 22
(
INVTID varchar(1),
Per varchar(2),
Qty int
)create table b
(
INVTID varchar(1),
Per varchar(2),
Price int
)
insert into a select 'a', '01', 20
insert into a select 'a', '02', 30
insert into a select 'a', '03', 40
insert into a select 'b', '01', 15
insert into a select 'b', '02', 25
insert into a select 'b', '03', 35
insert into a select 'b', '04', 45insert into b select 'a', '01', 12
insert into b select 'a', '03', 14
insert into b select 'b', '02', 21
insert into b select 'b', '03', 22--查询
select tt.invtid,tt.per,tt.qty,
price = (select top 1 price from b where invtid = tt.invtid and per <= tt.per order by per desc)
from a tt--结果
a 01 20 12
a 02 30 12
a 03 40 14
b 01 15 NULL
b 02 25 21
b 03 35 22
b 04 45 22
(INVTID Varchar(10),
Per Varchar(10),
Qty Int)
Insert A Select 'a', '01', 20
Union All Select 'a', '02', 30
Union All Select 'a', '03', 40
Union All Select 'b', '01', 15
Union All Select 'b', '02', 25
Union All Select 'b', '03', 35
Union All Select 'b', '04', 45Create Table B
(INVTID Varchar(10),
Per Varchar(10),
Price Int)Insert B Select 'a', '01', 12
Union All Select 'a', '03', 14
Union All Select 'b', '02', 21
Union All Select 'b', '03', 22
GO
Select
A.*,
IsNull((Select TOP 1 Price From B Where INVTID = A.INVTID And Per <= A.Per Order By Per Desc), 0) As Price
From
A
GO
Drop Table A, B
--Result
/*
INVTID Price Qty Price
a 01 20 12
a 02 30 12
a 03 40 14
b 01 15 0
b 02 25 21
b 03 35 22
b 04 45 22
*/
Create Table A
(INVTID Varchar(10),
Per Varchar(10),
Qty Int)
Insert A Select 'a', '01', 20
Union All Select 'a', '02', 30
Union All Select 'a', '03', 40
Union All Select 'b', '01', 15
Union All Select 'b', '02', 25
Union All Select 'b', '03', 35
Union All Select 'b', '04', 45Create Table B
(INVTID Varchar(10),
Per Varchar(10),
Price Int)Insert B Select 'a', '01', 12
Union All Select 'a', '03', 14
Union All Select 'b', '02', 21
Union All Select 'b', '03', 22
GO
Select
C.INVTID,
C.Per,
C.Qty,
IsNull(D.Price, 0) As Price
From
(
Select
A.*,
Max(B.Per) As BPer
From
A
Left Join
B
On A.INVTID = B.INVTID And A.Per >= B.Per
Group By A.INVTID, A.Per, A.Qty
)
C
Left Join B D
On C.INVTID = D.INVTID And C.BPer = D.Per
GO
Drop Table A, B
--Result
/*
INVTID Price Qty Price
a 01 20 12
a 02 30 12
a 03 40 14
b 01 15 0
b 02 25 21
b 03 35 22
b 04 45 22
*/