求SQL语句..在线等!!

[yuki58]

有表abc,如下

a b
1 2011-01-20
1 2011-05-10
2 2011-05-20
2 2011-07-01
3 2011-03-02
4 2011-05-08
4 2011-09-20
5 2011-08-01


a列重复的话只保留时间值最大的那个,不重复的照保留

筛出的结果如下
a b
1 2011-05-10
2 2011-07-01
3 2011-03-02
4 2011-09-20
5 2011-08-01

[--小F--]

select * from tb t where b=(select max(b) from tb where a=t.a)

[geniuswjt]

select * from abc t where not exists(
select 1 from abc where a=t.a and b>t.b
)

[geniuswjt]

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

[-晴天]

create table abc(a int,b datetime)
insert into abc select 1,'2011-01-20'
insert into abc select 1,'2011-05-10'
insert into abc select 2,'2011-05-20'
insert into abc select 2,'2011-07-01'
insert into abc select 3,'2011-03-02'
insert into abc select 4,'2011-05-08'
insert into abc select 4,'2011-09-20'
insert into abc select 5,'2011-08-01'
go
select * from abc a where not exists(select 1 from abc where a=a.a and b>a.b)
/*
a           b
----------- -----------------------
1           2011-05-10 00:00:00.000
2           2011-07-01 00:00:00.000
3           2011-03-02 00:00:00.000
4           2011-09-20 00:00:00.000
5           2011-08-01 00:00:00.000

(5 行受影响)

*/
go
drop table abc

[hanfeng321]

SELECT a, MAX(b) AS b
FROM abc
GROUP BY a

[ming_Y]

create table #tmp(a int,b datetime)
insert into #tmp
select 1,'2011-01-20' union all
select 1,'2011-05-10' union all
select 2,'2011-05-20' union all
select 2,'2011-07-01' union all
select 3,'2011-03-02' union all
select 4,'2011-05-08' union all
select 4,'2011-09-20' union all
select 5,'2011-08-01'

select * from #tmp c where not exists(select 1 from #tmp where #tmp.a=c.a and #tmp.b>c.b)
drop table #tmp

[q465897859]

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

[sbymdh2003]

create table table1(a int,b datetime)
insert into table1 select 1,'2011-01-20'
insert into table1 select 1,'2011-05-10'
insert into table1 select 2,'2011-05-20'
insert into table1 select 2,'2011-07-01'
insert into table1 select 3,'2011-03-02'
insert into table1 select 4,'2011-05-08'
insert into table1 select 4,'2011-09-20'
insert into table1 select 5,'2011-08-01'

select a,b
from(select row_number()over(partition by a order by b desc)as row,* from table1)a
where row=1

方法很多的

[yuki58]

select * from tb t where b=(select max(b) from tb where a=t.a)


select * from abc t where not exists(
select 1 from abc where a=t.a and b>t.b
)

这2句靠谱


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

这句如果有多列的时候就不管用了

[yuki58]

谢谢大家的帮忙~~本人新手..希望多多交流~

[cxmcxm]

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

[勿勿]

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

[pengxuan]

if object_id('tb','U') is not null
   drop table tb
go
create table tb
(
 a int,
 b varchar(10)
)
go
insert into tb
select '1','2011-01-20' union all
select '1','2011-05-10' union all
select '2','2011-05-20' union all
select '2','2011-07-01' union all
select '3','2011-03-02' union all
select '4','2011-05-08' union all
select '4','2011-09-20' union all
select '5','2011-08-01'
go
select * from tb t1 where not exists(select 1 from tb where a=t1.a and b>t1.b)

[wfkmu]

方法已经很多了

[vileboy]

declare @tb table
(
a int,
b varchar(10)
)

insert into @tb
select '1','2011-01-20' union all
select '1','2011-05-10' union all
select '2','2011-05-20' union all
select '2','2011-07-01' union all
select '3','2011-03-02' union all
select '4','2011-05-08' union all
select '4','2011-09-20' union all
select '5','2011-08-01'



SELECT A,MAX(B)
FROM @TB
GROUP BY A