MS-SQL的查询有关问题

MS-SQL的查询问题
idorder_numberorder_time
13841894 2012-05-19 19:40:02
23841894 2012-05-20 19:40:02
33841894 2012-05-20 19:50:02
43841894 2012-05-21 19:40:02
5123456789122012-05-21 19:40:02
6123456789122012-05-21 19:40:02
7123456789122012-05-21 19:40:02
83H1gyBZNaq62012-05-25 20:22:01
9BAy2pH5LEnk2012-05-25 20:23:51

上面的表 Tab 中，order_number 列有很多相同的值，怎么筛选出 order_number 相同的行只有一行，按照 id 逆序排，结果如下：

9BAy2pH5LEnk2012-05-25 20:23:51
83H1gyBZNaq62012-05-25 20:22:01
7123456789122012-05-21 19:40:02
43841894 2012-05-21 19:40:02


[解决办法]

SQL code

if object_id('[tab]') is not null drop table [tab]gocreate table [tab]([id] int,[order_number] varchar(11),[order_time] datetime)insert [tab]select 1,'3841894','2012-05-19 19:40:02' union allselect 2,'3841894','2012-05-20 19:40:02' union allselect 3,'3841894','2012-05-20 19:50:02' union allselect 4,'3841894','2012-05-21 19:40:02' union allselect 5,'12345678912','2012-05-21 19:40:02' union allselect 6,'12345678912','2012-05-21 19:40:02' union allselect 7,'12345678912','2012-05-21 19:40:02' union allselect 8,'3H1gyBZNaq6','2012-05-25 20:22:01' union allselect 9,'BAy2pH5LEnk','2012-05-25 20:23:51'goselect * from tab twhere not exists(select 1 from tab where order_number=t.order_number and id>t.id)order by id desc/**id          order_number order_time----------- ------------ -----------------------9           BAy2pH5LEnk  2012-05-25 20:23:51.0008           3H1gyBZNaq6  2012-05-25 20:22:01.0007           12345678912  2012-05-21 19:40:02.0004           3841894      2012-05-21 19:40:02.000(4 行受影响)**/
[解决办法]
SQL code
create table Tab(id int, order_number varchar(20), order_time datetime)insert into Tabselect 1, '3841894', '2012-05-19 19:40:02' union allselect 2, '3841894', '2012-05-20 19:40:02' union allselect 3, '3841894', '2012-05-20 19:50:02' union allselect 4, '3841894', '2012-05-21 19:40:02' union allselect 5, '12345678912', '2012-05-21 19:40:02' union allselect 6, '12345678912', '2012-05-21 19:40:02' union allselect 7, '12345678912', '2012-05-21 19:40:02' union allselect 8, '3H1gyBZNaq6', '2012-05-25 20:22:01' union allselect 9, 'BAy2pH5LEnk', '2012-05-25 20:23:51'with t as(select row_number() over(partition by order_number order by id desc) rn, id,order_number,order_time from Tab)select id,order_number,order_timefrom t where rn=1 order by id desc/*id          order_number         order_time----------- -------------------- -----------------------9           BAy2pH5LEnk          2012-05-25 20:23:51.0008           3H1gyBZNaq6          2012-05-25 20:22:01.0007           12345678912          2012-05-21 19:40:02.0004           3841894              2012-05-21 19:40:02.000(4 row(s) affected)*/