-- Sql2000
--有一个表如下
SELECT * FROM
(
select 'dw_a' as 单位,'sp_1' as 商品,'2012-02-03' as 销售日期,'17.0' as 单价 union
select 'dw_a' as 单位,'sp_1' as 商品,'2012-04-13' as 销售日期,'21.0' as 单价 union
select 'dw_a' as 单位,'sp_1' as 商品,'2012-05-16' as 销售日期,'16.0' as 单价 union
select 'dw_a' as 单位,'sp_2' as 商品,'2012-01-03' as 销售日期,'11.0' as 单价 union
select 'dw_a' as 单位,'sp_2' as 商品,'2012-02-13' as 销售日期,'12.0' as 单价 union
select 'dw_a' as 单位,'sp_2' as 商品,'2012-03-16' as 销售日期,'15.0' as 单价 union

select 'dw_b' as 单位,'sp_1' as 商品,'2011-02-03' as 销售日期,'12.0' as 单价 union
select 'dw_b' as 单位,'sp_1' as 商品,'2012-04-13' as 销售日期,'121.0' as 单价 union
select 'dw_b' as 单位,'sp_1' as 商品,'2012-05-16' as 销售日期,'136.0' as 单价 union
select 'dw_b' as 单位,'sp_2' as 商品,'2012-01-03' as 销售日期,'101.0' as 单价 union
select 'dw_b' as 单位,'sp_2' as 商品,'2012-02-13' as 销售日期,'102.0' as 单价 union
select 'dw_b' as 单位,'sp_2' as 商品,'2012-03-16' as 销售日期,'105.0' as 单价 ) A

--求一个查询，得到各单位，各商品的最后日期的售价

declare @A table(单位 varchar(20),商品 varchar(20),销售日期 varchar(20),单价 float)
insert into @A
SELECT * FROM
(
select 'dw_a' as 单位,'sp_1' as 商品,'2012-02-03' as 销售日期,'17.0' as 单价 union
select 'dw_a' as 单位,'sp_1' as 商品,'2012-04-13' as 销售日期,'21.0' as 单价 union
select 'dw_a' as 单位,'sp_1' as 商品,'2012-05-16' as 销售日期,'16.0' as 单价 union
select 'dw_a' as 单位,'sp_2' as 商品,'2012-01-03' as 销售日期,'11.0' as 单价 union
select 'dw_a' as 单位,'sp_2' as 商品,'2012-02-13' as 销售日期,'12.0' as 单价 union
select 'dw_a' as 单位,'sp_2' as 商品,'2012-03-16' as 销售日期,'15.0' as 单价 union

select 'dw_b' as 单位,'sp_1' as 商品,'2011-02-03' as 销售日期,'12.0' as 单价 union
select 'dw_b' as 单位,'sp_1' as 商品,'2012-04-13' as 销售日期,'121.0' as 单价 union
select 'dw_b' as 单位,'sp_1' as 商品,'2012-05-16' as 销售日期,'136.0' as 单价 union
select 'dw_b' as 单位,'sp_2' as 商品,'2012-01-03' as 销售日期,'101.0' as 单价 union
select 'dw_b' as 单位,'sp_2' as 商品,'2012-02-13' as 销售日期,'102.0' as 单价 union
select 'dw_b' as 单位,'sp_2' as 商品,'2012-03-16' as 销售日期,'105.0' as 单价 ) A
select * from @A a1 where a1.销售日期=(select max(销售日期) from @A a2
    where a1.单位=a2.单位 and a1.商品=a2.商品)
order by 单位,商品
/*
单位                   商品                   销售日期                 单价
-------------------- -------------------- -------------------- ----------------------
dw_a                 sp_1                 2012-05-16           16
dw_a                 sp_2                 2012-03-16           15
dw_b                 sp_1                 2012-05-16           136
dw_b                 sp_2                 2012-03-16           105

(4 row(s) affected)

*/

------解决方案--------------------
SQL code

SELECT * INTO #t FROM
(
select 'dw_a' as 单位,'sp_1' as 商品,'2012-02-03' as 销售日期,'17.0' as 单价 union
select 'dw_a' as 单位,'sp_1' as 商品,'2012-04-13' as 销售日期,'21.0' as 单价 union
select 'dw_a' as 单位,'sp_1' as 商品,'2012-05-16' as 销售日期,'16.0' as 单价 union
select 'dw_a' as 单位,'sp_2' as 商品,'2012-01-03' as 销售日期,'11.0' as 单价 union
select 'dw_a' as 单位,'sp_2' as 商品,'2012-02-13' as 销售日期,'12.0' as 单价 union
select 'dw_a' as 单位,'sp_2' as 商品,'2012-03-16' as 销售日期,'15.0' as 单价 union

select 'dw_b' as 单位,'sp_1' as 商品,'2011-02-03' as 销售日期,'12.0' as 单价 union
select 'dw_b' as 单位,'sp_1' as 商品,'2012-04-13' as 销售日期,'121.0' as 单价 union
select 'dw_b' as 单位,'sp_1' as 商品,'2012-05-16' as 销售日期,'136.0' as 单价 union
select 'dw_b' as 单位,'sp_2' as 商品,'2012-01-03' as 销售日期,'101.0' as 单价 union
select 'dw_b' as 单位,'sp_2' as 商品,'2012-02-13' as 销售日期,'102.0' as 单价 union
select 'dw_b' as 单位,'sp_2' as 商品,'2012-03-16' as 销售日期,'105.0' as 单价 
) A


SELECT * FROM #t t
WHERE 销售日期=(SELECT MAX(销售日期) FROM #t WHERE 单位=t.单位 AND 商品=t.商品)

/*
单位   商品   销售日期       单价
---- ---- ---------- -----
dw_b sp_2 2012-03-16 105.0
dw_b sp_1 2012-05-16 136.0