有如下的数据表(test)
id type priority
1 a 3
2 b 4
3 a 2
4 a 1
5 b 3
6 c 5
7 b 6
8 c 4
9 d 3请教各位大虾:如何将上表中按type分类,并求出各类中priority最大的一条记录?小弟多谢了……
id type priority
1 a 3
2 b 4
3 a 2
4 a 1
5 b 3
6 c 5
7 b 6
8 c 4
9 d 3请教各位大虾:如何将上表中按type分类,并求出各类中priority最大的一条记录?小弟多谢了……
group by type
insert into @t select 1,'a',3
union all select 2,'b',4
union all select 3,'a',2
union all select 4,'a',1
union all select 5,'b',3
union all select 6,'c',5
union all select 7,'b',6
union all select 8,'c',4
union all select 9,'d',3select * from @t a where not exists(select 1 from @t where type=a.type and priority>a.priority)
这个表是从数据库中取出的一部分,所以……
--------------------------------------------------------------------------------------
试下这样,应该就可以了:select id,type,max(priority) from test
group by type,id
select id,type,max(priority) from test
group by type,id上面这句,是通过type,id进行分组,如果你仅需要通过type,就要group by一个就行了,...
要不你贴出你的表数据和你想要的结果数据...再来看。
select test.* from test right join(select max(priority) as t,type
from test group by type) temp on temp.type=test.type and temp.t=test.priority;可能我的表述不太清楚,没说明白题意,还请各位谅解^_^
这个大家看看
declare @t table(id int,type varchar(10),priority int)
insert into @t select 1,'a',3
union all select 2,'b',4
union all select 3,'a',2
union all select 4,'a',1
union all select 5,'b',3
union all select 6,'c',5
union all select 7,'b',6
union all select 8,'c',4
union all select 9,'d',3
union all select 10,'a',3select * from @t where
type+ cast(priority as varchar(100))
in
(
select type+ cast(max(priority) as varchar(100)) from @t
group by type )