我的表是这样的
id pro_name pro_sort
1 aaa 1
2 bbb 1
3 ccc 2
4 ddd 3
5 eee 1
6 fff 2
7 ggg 2我想得到这个结果
id pro_name pro_sort
5 eee 1
7 ggg 2
4 ddd 3SQL语句怎么写?
最好不用存储过程...
id pro_name pro_sort
1 aaa 1
2 bbb 1
3 ccc 2
4 ddd 3
5 eee 1
6 fff 2
7 ggg 2我想得到这个结果
id pro_name pro_sort
5 eee 1
7 ggg 2
4 ddd 3SQL语句怎么写?
最好不用存储过程...
insert into @t select 1,'aaa',1
insert into @t select 2,'bbb',1
insert into @t select 3,'ccc',2
insert into @t select 4,'ddd',3
insert into @t select 5,'eee',1
insert into @t select 6,'fff',2
insert into @t select 7,'ggg',2select
t.*
from
@t t
where
not exists(select 1 from @t where pro_sort=t.pro_sort and id>t.id)
order by
t.pro_sort/*
id pro_name pro_sort
----------- -------- -----------
5 eee 1
7 ggg 2
4 ddd 3
*/
insert into @t select 1,'aaa',1
insert into @t select 2,'bbb',1
insert into @t select 3,'ccc',2
insert into @t select 4,'ddd',3
insert into @t select 5,'eee',1
insert into @t select 6,'fff',2
insert into @t select 7,'ggg',2select
b.*
from
(select pro_sort,max(id) as id from @t group by pro_sort) a,
@t b
where
a.id=b.id
order by
b.pro_sort/*
id pro_name pro_sort
----------- -------- -----------
5 eee 1
7 ggg 2
4 ddd 3
*/
t
(id int,pro_name varchar(6),pro_sort int)
goinsert into t select 1,'aaa',1
union all select 2,'bbb',1
union all select 3,'ccc',2
union all select 4,'ddd',3
union all select 5,'eee',1
union all select 6,'fff',2
union all select 7,'ggg',2
goselect
t.*
from t
inner join
(select max(id) as id,pro_sort from t group by pro_sort) a
on
t.id=a.id and a.pro_sort is not null
order by
t.pro_sort结果如下:
id pro_name pro_sort
5 eee 1
7 ggg 2
4 ddd 3
drop table tb
gocreate table tb(id int,pro_name varchar(10),pro_sort int)
insert into tb(id,pro_name,pro_sort) values(1, 'aaa', 1)
insert into tb(id,pro_name,pro_sort) values(2, 'bbb', 1)
insert into tb(id,pro_name,pro_sort) values(3, 'ccc', 2)
insert into tb(id,pro_name,pro_sort) values(4, 'ddd', 3)
insert into tb(id,pro_name,pro_sort) values(5, 'eee', 1)
insert into tb(id,pro_name,pro_sort) values(6, 'fff', 2)
insert into tb(id,pro_name,pro_sort) values(7, 'ggg', 2)select a.* from tb a,
(select pro_sort , max(id) id from tb group by pro_sort) b
where a.pro_sort = b.pro_sort and a.id = b.id
order by a.pro_sortdrop table tb/*
id pro_name pro_sort
----------- ---------- -----------
5 eee 1
7 ggg 2
4 ddd 3(所影响的行数为 3 行)
*/