--创建测试环境 create table [表] ( ptime varchar(10), ptype int, pname varchar(20) ) insert [表] select '2003-03-15',1,'A' union select '2003-03-15',2,'B' union select '2003-07-15',3,'C' union select '2003-07-19',2,'B' union select '2003-03-01',1,'A' union select '2003-07-01',1,'A' --子查询测试 select * from (select * from [表]) A where not exists(select 1 from (select * from [表])B where B.ptype=A.ptype and B.ptime>A.ptime) order by A.ptype--删除测试环境 drop table [表]--结果 /* ptime ptype pname ---------- ----------- -------------------- 2003-07-01 1 A 2003-07-19 2 B 2003-07-15 3 C(所影响的行数为 3 行) */
create table [表] ( ptime varchar(10), ptype int, pname varchar(20) ) insert [表] select '2003-03-15',1,'A' union select '2003-03-15',2,'B' union select '2003-07-15',3,'C' union select '2003-07-19',2,'B' union select '2003-03-01',1,'A' union select '2003-07-01',1,'A' select max(ptime),ptype,max(pname) from (select * from [表] where pname='A') 表B group by ptype
create table [表]
(
ptime varchar(10),
ptype int,
pname varchar(20)
)
insert [表]
select '2003-03-15',1,'A' union
select '2003-03-15',2,'B' union
select '2003-07-15',3,'C' union
select '2003-07-19',2,'B' union
select '2003-03-01',1,'A' union
select '2003-07-01',1,'A'
--子查询测试
select * from (select * from [表]) A
where not exists(select 1 from (select * from [表])B where B.ptype=A.ptype and B.ptime>A.ptime)
order by A.ptype--删除测试环境
drop table [表]--结果
/*
ptime ptype pname
---------- ----------- --------------------
2003-07-01 1 A
2003-07-19 2 B
2003-07-15 3 C(所影响的行数为 3 行)
*/
(
ptime varchar(10),
ptype int,
pname varchar(20)
)
insert [表]
select '2003-03-15',1,'A' union
select '2003-03-15',2,'B' union
select '2003-07-15',3,'C' union
select '2003-07-19',2,'B' union
select '2003-03-01',1,'A' union
select '2003-07-01',1,'A' select max(ptime),ptype,max(pname) from (select * from [表] where pname='A') 表B group by ptype