id order_number order_time
1 3841894 2012-05-19 19:40:02
2 3841894 2012-05-20 19:40:02
3 3841894 2012-05-20 19:50:02
4 3841894 2012-05-21 19:40:02
5 12345678912 2012-05-21 19:40:02
6 12345678912 2012-05-21 19:40:02
7 12345678912 2012-05-21 19:40:02
8 3H1gyBZNaq6 2012-05-25 20:22:01
9 BAy2pH5LEnk 2012-05-25 20:23:51
上面的表 Tab 中，order_number 列有很多相同的值，怎么筛选出 order_number 相同的行只有一行，按照 id 逆序排，结果如下：
9 BAy2pH5LEnk 2012-05-25 20:23:51
8 3H1gyBZNaq6 2012-05-25 20:22:01
7 12345678912 2012-05-21 19:40:02
4 3841894 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)*/