关于SQL查询结果的问题。
有一个表，其中主要有3个字段：
货号、收货时间、收货数量。

每个货号可能有多次收货（收货时间不同），每次收货的数量可能一样也可能不一样。

现在有个需求是：
需要提取每个货号最后一次收货时间和最后一次收货数量，要求用一条语句实现，最好不要用子查询。
例如：
货号---------收货时间---------收货数量
1123 2012-06-10 10
1124 2012-06-11 13
1123 2012-06-15 22
1125 2012-06-19 9
1122 2012-06-01 14
1123 2012-06-19 20

查询得到：
货号---------收货时间---------收货数量
1123 2012-06-19 20
1124 2012-06-11 13
1125 2012-06-19 9
1122 2012-06-01 14

请各位大侠不吝赐教，谢谢。

[解决办法]

SQL code

;with T as ( select row_number() over( PARTITION BY  [货号] order by [收货时间] desc ) as Row, * from [table])select * from t where Row = 1
[解决办法]
SQL code
--> 测试数据：[test]if object_id('[test]') is not null drop table [test]create table [test]([货号] int,[收货时间] datetime,[收货数量] int)insert [test]select 1123,'2012-06-10',10 union allselect 1124,'2012-06-11',13 union allselect 1123,'2012-06-15',22 union allselect 1125,'2012-06-19',9 union allselect 1122,'2012-06-01',14 union allselect 1123,'2012-06-19',20--看错了，改好了select distinct * from test awhere a.收货时间=(select MAX(b.收货时间) from test b where a.货号=b.货号)/*货号    收货时间    收货数量---------------------------------------------1122    2012-06-01 00:00:00.000    141123    2012-06-19 00:00:00.000    201124    2012-06-11 00:00:00.000    131125    2012-06-19 00:00:00.000    9*/
[解决办法]
SQL code
select [货号],MAX([收货时间]) 收货时间,[收货数量] from tb group by [货号],[收货数量]
[解决办法]
SQL code
if object_id('[test]') is not null drop table [test]create table [test]([货号] int,[收货时间] datetime,[收货数量] int)insert [test]select 1123,'2012-06-10',10 union allselect 1124,'2012-06-11',13 union allselect 1123,'2012-06-15',22 union allselect 1124,'2012-06-15',30 union allselect 1125,'2012-06-19',9 union allselect 1122,'2012-06-01',14 union allselect 1125,'2012-06-20',19 union allselect 1123,'2012-06-19',20;with T as ( select row_number() over( PARTITION BY  [货号] order by [收货时间] desc ) as Row, * from [test])select * from t where Row = 1(8 行受影响)Row                  货号          收货时间                    收货数量-------------------- ----------- ----------------------- -----------1                    1122        2012-06-01 00:00:00.000 141                    1123        2012-06-19 00:00:00.000 201                    1124        2012-06-15 00:00:00.000 301                    1125        2012-06-20 00:00:00.000 19(4 行受影响)
[解决办法]
SQL code
CREATE TABLE t1(    huohao INT,    shijian DATETIME,    shuliang INT)INSERT INTO t1SELECT 1123, '2012-06-10', 10 UNION ALLSELECT 1124, '2012-06-11', 13 UNION ALLSELECT 1123, '2012-06-15', 22 UNION ALLSELECT 1125, '2012-06-19', 9 UNION ALLSELECT 1122, '2012-06-01', 14 UNION ALLSELECT 1123, '2012-06-19', 20SELECT * FROM t1SELECT * FROM t1 AS a WHERE NOT EXISTS (SELECT 1 FROM t1 WHERE huohao=a.huohao AND shijian>a.shijian)ORDER BY huohao---------------------------huohao    shijian                    shuliang1122    2012-06-01 00:00:00.000    141123    2012-06-19 00:00:00.000    201124    2012-06-11 00:00:00.000    131125    2012-06-19 00:00:00.000    9