求复杂SQL语句

[tommychim]

表A,记录每个物品每期间的发出数量:
INVTID Per Qty
------------------------
a 01 20
a 02 30
a 03 40
b 01 15
b 02 25
b 03 35
b 04 45

表B,记录每个物品每期间的最后一次收入单价:
INVTID Per Price
------------------------
a 01 12
a 03 14
b 02 21
b 03 22

求sql,得到以下结果:
INVTID(物品编码),Per(物品发出期间),Qty(物品发出数量),Price(物品最后一次收入单价)
---------------------------------------------------------------------------
a 01 20 12
a 02 30 12(02无数据,采用01数据)
a 03 40 14
b 01 15 0(01无数据,为0)
b 02 25 21
b 03 35 22
b 04 45 22(04无数据,采用03数据)

[tommychim]

由于条件限制,不能用存储过程.

[冷箫轻笛]

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', 45

insert 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

[paoluo]

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', 45

Create 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
*/

[biao1]

mark

[paoluo]

剛CSDN是不是出問題了，剛貼上來，結果該頁無法顯示了，現在才好。

[mengmou]

刚刚csdn有问题。

[tommychim]

目的达到,但好像效率低了好多,不知道还能优化吗?

[paoluo]

這樣效率應該優一些，再看看能不能優化.


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', 45

Create 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
*/