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
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
where not exists(select 1 from tab where order_number=t.order_number and id>t.id)
order by id desc
go
create table [tab]([id] int,[order_number] varchar(11),[order_time] datetime)
insert [tab]
select 1,'3841894','2012-05-19 19:40:02' union all
select 2,'3841894','2012-05-20 19:40:02' union all
select 3,'3841894','2012-05-20 19:50:02' union all
select 4,'3841894','2012-05-21 19:40:02' union all
select 5,'12345678912','2012-05-21 19:40:02' union all
select 6,'12345678912','2012-05-21 19:40:02' union all
select 7,'12345678912','2012-05-21 19:40:02' union all
select 8,'3H1gyBZNaq6','2012-05-25 20:22:01' union all
select 9,'BAy2pH5LEnk','2012-05-25 20:23:51'
goselect * from tab t
where 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.000
8 3H1gyBZNaq6 2012-05-25 20:22:01.000
7 12345678912 2012-05-21 19:40:02.000
4 3841894 2012-05-21 19:40:02.000(4 行受影响)
**/
create table Tab
(id int, order_number varchar(20), order_time datetime)insert into Tab
select 1, '3841894', '2012-05-19 19:40:02' union all
select 2, '3841894', '2012-05-20 19:40:02' union all
select 3, '3841894', '2012-05-20 19:50:02' union all
select 4, '3841894', '2012-05-21 19:40:02' union all
select 5, '12345678912', '2012-05-21 19:40:02' union all
select 6, '12345678912', '2012-05-21 19:40:02' union all
select 7, '12345678912', '2012-05-21 19:40:02' union all
select 8, '3H1gyBZNaq6', '2012-05-25 20:22:01' union all
select 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_time
from t where rn=1 order by id desc/*
id order_number order_time
----------- -------------------- -----------------------
9 BAy2pH5LEnk 2012-05-25 20:23:51.000
8 3H1gyBZNaq6 2012-05-25 20:22:01.000
7 12345678912 2012-05-21 19:40:02.000
4 3841894 2012-05-21 19:40:02.000(4 row(s) affected)
*/