日期:2014-05-18  浏览次数:20575 次

sqlserver数据库表操作
在下面这个表中,id是主键如何得到,如何获取到每个personID最近的一条记录,如在下表中
id personID mytime
1 001 2011-11-15
2 001 2011-11-12
3 002 2011-11-13
4 003 2011-11-16
5 003 2011-11-08
6 003 2011-12-12
要求查询返回结果:
id personID mytime
1 001 2011-11-15
3 002 2011-11-13  
6 003 2011-12-12

------解决方案--------------------
SQL code
if object_id('[TB]') is not null drop table [TB]
go
create table [TB] (id int,personID nvarchar(6),mytime datetime)
insert into [TB]
select 1,'001','2011-11-15' union all
select 2,'001','2011-11-12' union all
select 3,'002','2011-11-13' union all
select 4,'003','2011-11-16' union all
select 5,'003','2011-11-08' union all
select 6,'003','2011-12-12'

select * from [TB]

SELECT  *
FROM    TB A
WHERE   EXISTS ( SELECT *
                 FROM   TB
                 WHERE  A.personid = personid
                        AND A.mytime > mytime )
                        
                        
/*
id    personID    mytime
1    001    2011-11-15 00:00:00.000
4    003    2011-11-16 00:00:00.000
6    003    2011-12-12 00:00:00.000*/

------解决方案--------------------
SQL code
SELECT  *
FROM    TB A
WHERE   NOT EXISTS ( SELECT *
                 FROM   TB
                 WHERE  A.personid = personid
                        AND A.mytime < mytime )
                        
/*
id    personID    mytime
1    001    2011-11-15 00:00:00.000
3    002    2011-11-13 00:00:00.000
6    003    2011-12-12 00:00:00.000*/

------解决方案--------------------
SQL code
select * from tb t where mytime=(select max(mytime) from tb where personid =t.personid)

------解决方案--------------------
SQL code

if object_id('tb','U') is not null
   drop table tb
go
create table tb
(
 id int identity(1,1),
 personID varchar(10),
 mytime varchar(10)
)
go
insert into tb (personID,mytime)
select '001','2011-11-15' union all
select '001','2011-11-12' union all
select '002','2011-11-13' union all
select '003','2011-11-16' union all
select '003','2011-11-08' union all
select '003','2011-11-12'
go
select * from tb a where not exists(select 1 from tb where personID=a.personID and mytime>a.mytime)
/*
id          personID   mytime
----------- ---------- ----------
1           001        2011-11-15
3           002        2011-11-13
4           003        2011-11-16

(3 行受影响)

*/