fid classname updated
1 classa 2007.09.24
1 classa1 2009.01.01
1 classab 2008.08.08
2 classc1 2007.04.14
2 classbt 2008.02.02
3 classd1 2009.10.12
3 classd2 2007.01.15 选择,最后跟新的每一类(fid),对应的classname.
如:
fid classname updated 1 classa1 2009.01.01
2 classbt 2008.02.02
3 classd1 2009.10.12
1 classa 2007.09.24
1 classa1 2009.01.01
1 classab 2008.08.08
2 classc1 2007.04.14
2 classbt 2008.02.02
3 classd1 2009.10.12
3 classd2 2007.01.15 选择,最后跟新的每一类(fid),对应的classname.
如:
fid classname updated 1 classa1 2009.01.01
2 classbt 2008.02.02
3 classd1 2009.10.12
from tb t
where not exists(select * from tb where t.fid=fid and t.updated<updated)
from tb t
where updated=(select top 1 updated from tb where t.fid=fid order by updated desc)select *
from tb t
where updated=(select max(updated) from tb where t.fid=fid)
where not exists (select 1 from tb where a.fid=fid and a.updated<updated)
----------------------------------------------------------------
-- Author :fredrickhu(小F,向高手学习)
-- Date :2009-10-26 20:47:30
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([fid] int,[classname] varchar(7),[updated] datetime)
insert [tb]
select 1,'classa','2007.09.24' union all
select 1,'classa1','2009.01.01' union all
select 1,'classab','2008.08.08' union all
select 2,'classc1','2007.04.14' union all
select 2,'classbt','2008.02.02' union all
select 3,'classd1','2009.10.12' union all
select 3,'classd2','2007.01.15'
--------------开始查询--------------------------
select
*
from
tb a
where
not exists (select 1 from tb where a.fid=fid and a.updated<updated)
----------------结果----------------------------
/* fid classname updated
----------- --------- -----------------------
1 classa1 2009-01-01 00:00:00.000
2 classbt 2008-02-02 00:00:00.000
3 classd1 2009-10-12 00:00:00.000(3 行受影响)
*/
-- Author :fredrickhu(小F,向高手学习)
-- Date :2009-10-26 20:47:30
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([fid] int,[classname] varchar(7),[updated] datetime)
insert [tb]
select 1,'classa','2007.09.24' union all
select 1,'classa1','2009.01.01' union all
select 1,'classab','2008.08.08' union all
select 2,'classc1','2007.04.14' union all
select 2,'classbt','2008.02.02' union all
select 3,'classd1','2009.10.12' union all
select 3,'classd2','2007.01.15'
--------------开始查询--------------------------
select
fid,classname,convert(varchar(10),updated,102) as updated
from
tb a
where
not exists (select 1 from tb where a.fid=fid and a.updated<updated)
----------------结果----------------------------
/*fid classname updated
----------- --------- ----------
1 classa1 2009.01.01
2 classbt 2008.02.02
3 classd1 2009.10.12(3 行受影响)
)
*/
--测试数据
declare @table table(fid int,classname char(10),updated char(10))
insert into @table
select 1 , 'classa', '2007.09.24' union all
select 1 , 'classa1', '2009.01.01' union all
select 1 , 'classab', '2008.08.08' union all
select 2 , 'classc1', '2007.04.14' union all
select 2 , 'classbt', '2008.02.02' union all
select 3 , 'classd1', '2009.10.12' union all
select 3 , 'classd2', '2007.01.15'
--查询
select * from @table t where not exists
(select 1 from @table where fid = t.fid and updated > t.updated)
--结果
-------------------
1 classa1 2009.01.01
2 classbt 2008.02.02
3 classd1 2009.10.12
declare @table table (fid int,classname varchar(7),updated varchar(15))
insert into @table
select 1,'classa','2007.09.24' union all
select 1,'classa1','2009.01.01' union all
select 1,'classab','2008.08.08' union all
select 2,'classc1','2007.04.14' union all
select 2,'classbt','2008.02.02' union all
select 3,'classd1','2009.10.12' union all
select 3,'classd2','2007.01.15'select * from @table t where updated=(select max(updated) from @table where fid=t.fid) order by fid
/*
fid classname updated
----------- --------- ---------------
1 classa1 2009.01.01
2 classbt 2008.02.02
3 classd1 2009.10.12
*/
b.*
From
(
Select
fid,
MAX(uupdated)as updated
From
#T a
Group by
fid
) a Left join #T b on a.fid= b.fid And a.updated=b.uupdated
(select top 1 updated from tb b where a.fid=b.fid order by updated desc)
select fid classname updated from(select fid classname updated ,row_number() over(partition by fid order by updated desc) as rowindex from table) a where rowindex <=1
在2005下,比他们的都快
select *
from tb t
where updated=(select max(updated) from tb where t.fid=fid)SQL Satement 2:
select *
from tb t
where not exists(select * from tb where t.fid=fid and t.updated<updated)SQL Satement 1, 要比SQL Satement 2, 效率高吧? 一般在SQL语句里应该尽量减少in, not in, exists, not exists 对吧?自己再顶一下!
from tt a
where not exists(select * from tt b where a.fid=b.fid and a.updated<b.updated)
from tb a,(select fid,max(updated) updated from tb group by fid) b
where a.fid=b.fid
and a.updated=b.updated