select top 1 * from a inner join b on a.单据编号=b.单据编号 where b.商品=商品XX order by b.日期 desc
WITH A(单据编号,日期) AS ( SELECT 'O1','2013-07-06' UNION ALL SELECT 'O2','2013-07-20' ) ,B(单据编号,商品内码,数量,价格) AS ( SELECT 'O1','P1',100,12.50 UNION ALL SELECT 'O1','P2',1000,10.00 UNION ALL SELECT 'O2','P1',200,10.50
)SELECT TOP 1 a.日期,b.价格,b.数量 FROM a,b where a.单据编号 = b.单据编号 AND b.商品内码 = 'P1' ORDER BY 1 DESC
from a
inner join b on a.单据编号=b.单据编号
where b.商品=商品XX
order by b.日期 desc
AS
(
SELECT 'O1','2013-07-06'
UNION ALL
SELECT 'O2','2013-07-20'
)
,B(单据编号,商品内码,数量,价格)
AS
(
SELECT 'O1','P1',100,12.50
UNION ALL
SELECT 'O1','P2',1000,10.00
UNION ALL
SELECT 'O2','P1',200,10.50
)SELECT TOP 1 a.日期,b.价格,b.数量 FROM a,b where a.单据编号 = b.单据编号
AND b.商品内码 = 'P1'
ORDER BY 1 DESC