不好意思,上边的错了: select * from 表 A where not exists(select 1 from 表 where ptype=A.ptype and ptime>A.ptime) order by A.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 [表] A where not exists(select 1 from [表] where ptype=A.ptype and 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 行) */
select max(ptime),ptype,max(pname) from [表] group by ptype
select max(ptime),ptype,max(pname) from [表] group by ptype
select DISTINCT ptype,max(ptime),pname from [表] order by ptype order by ptype
select max(a.ptime),a.ptype,b.pname from @t a,(select pname from @t group by pname) b where a.pname = b.pname group by a.ptype,b.pname order by a.ptype这个的效率好像没有关 vivianfdlpw() 写的高
select * from 表 A where not exists(select 1 from 表 where ptype=A.ptype and ptime>A.ptime) order by A.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 [表] A
where not exists(select 1 from [表] where ptype=A.ptype and 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 行)
*/
where a.pname = b.pname
group by a.ptype,b.pname
order by a.ptype这个的效率好像没有关 vivianfdlpw() 写的高