Date ID
2008-01-03 19:21:10.577 001
2008-01-03 19:23:14.000 001
2008-01-03 19:21:16.763 002
2008-01-03 19:23:19.000 003
2008-01-03 19:21:22.327 003
2008-01-03 19:21:22.000 004
2008-01-03 19:22:22.000 004 要求:次结果Date ID
2008-01-03 19:23:14.000 001
2008-01-03 19:23:19.000 003
2008-01-03 19:22:22.000 004
2008-01-03 19:21:10.577 001
2008-01-03 19:23:14.000 001
2008-01-03 19:21:16.763 002
2008-01-03 19:23:19.000 003
2008-01-03 19:21:22.327 003
2008-01-03 19:21:22.000 004
2008-01-03 19:22:22.000 004 要求:次结果Date ID
2008-01-03 19:23:14.000 001
2008-01-03 19:23:19.000 003
2008-01-03 19:22:22.000 004
2008-01-03 19:21:10.577 001 t
2008-01-03 19:23:14.000 001 H
2008-01-03 19:21:16.763 002 G
2008-01-03 19:23:19.000 003 M
2008-01-03 19:21:22.327 003 N
2008-01-03 19:21:22.000 004 C
2008-01-03 19:22:22.000 004 G如果是name不一样呢?怎样去做啊?
create table tb(Date datetime , ID varchar(10) , Name varchar(10))
insert into tb values('2008-01-03 19:21:10.577', '001', 't')
insert into tb values('2008-01-03 19:23:14.000', '001', 'H')
insert into tb values('2008-01-03 19:21:16.763', '002', 'G')
insert into tb values('2008-01-03 19:23:19.000', '003', 'M')
insert into tb values('2008-01-03 19:21:22.327', '003', 'N')
insert into tb values('2008-01-03 19:21:22.000', '004', 'C')
insert into tb values('2008-01-03 19:22:22.000', '004', 'G')
go
select max(date) date , id , name from tb group by id , name order by id , name
/*
date id name
----------------------- ---------- ----------
2008-01-03 19:23:14.000 001 H
2008-01-03 19:21:10.577 001 t
2008-01-03 19:21:16.763 002 G
2008-01-03 19:23:19.000 003 M
2008-01-03 19:21:22.327 003 N
2008-01-03 19:21:22.000 004 C
2008-01-03 19:22:22.000 004 G(7 行受影响)
*/select t.* from tb t where date = (select max(date) from tb where id = t.id) order by t.id , t.name
/*
Date ID Name
----------------------- ---------- ----------
2008-01-03 19:23:14.000 001 H
2008-01-03 19:21:16.763 002 G
2008-01-03 19:23:19.000 003 M
2008-01-03 19:22:22.000 004 G(4 行受影响)
*/
drop table tb
不用字段name
from table01
where id not in (select id from table01 group by id having Count(id)=1)
group by ID
select * from 表名 a where not exists(select 1 from 表名where id=a.id and date>a.date)
where not exists(select 1 from tab b where a.id=b.id and a.date<b.date)
------
你若只求相同ID下日期最大 这个不影响