求一sql

[mingl11]

现有一表，结构如下：

a b
nnn 855
nnn 986
eee 123
rrr 485
eee 598
ttt 625
eee 256
rrr 186

现在我想a列相同的只得到最后一行的。 也就是只得到蓝色的行

[ASPNETCHENGXU]

如果就a,b 两个字段就是， select a,max(b) from t group by a
否则
select * from table1 t where 字段=（slect max(字段) from t.分组字段=分组字段 ）

[sweetqueen1]

先根據B列排序
select distinct a,b from table order by b desc

[wyq29]

select a, max(b) from T group by a

[sth79]

学习。。。。收藏

[挨踢直男]

select * from tb a where id = (select max(id) from tb where a=a.a )

你那个表有没有id 有的话像上面那样写
没有的话不太好办

[hao1hao2hao3]

if object_id('tb')>0	
	drop table tb
create table tb
(
	a varchar(10),
	b int
)
go
insert into tb
select 'nnn', 855
union all
select 'nnn', 986
union all
select 'eee', 123
union all
select 'rrr', 485
union all
select 'eee', 598
union all
select 'ttt', 625
union all
select 'eee', 256
union all
select 'rrr', 186


with cte as
(
	select row_number() over(order by getdate()) as id,a,b from tb
)
select a.a,a.b
from cte as a 
where a.id = (select max(b.id) from cte as b where a.a = b.a group by b.a )


--结果

nnn	986
ttt	625
eee	256
rrr	186


--这是SQL Server2005才有的写法，2000 的话也要自己先构造一个id列。标示你的记录的顺序。

[挨踢直男]

with temp as
(
select *,row_number() over( order by getdate()) id from test
)

select a,b from temp a where id = (select max(id) from temp where a=a.a ) 

 

[zthsn]

学习下...

[xinshouno7]

with table as
(
select row_number() over(order by getdate()) as id,a,b from table_1
)
select a.a,a.b
from table as a
where a.id = (select max(b.id) from talbe as b where a.a = b.a group by b.a )

5楼的可以，不过插入的时候需要注意先后顺序啊

[TearsStoneJava]

select distinct a,b from table where time

[chen_ya_ping]

create table #t
(
id int not null identity(1,1) primary key,
a varchar(20) not null,
b varchar(20) not null
)

insert #t
values
('nnn','855'),
('nnn','986'),
('eee','123'),
('rrr','485'),
('eee','598'),
('ttt','625'),
('eee','256'),
('rrr','186')

select a,b from #t
where id in 
(
select max(id) from #t
group by a
)

drop table #t

/*
the result is:
nnn 986
ttt 625
eee 256
rrr 186
*/

[mingl11]

多谢各位解答 。结贴