select name,age,gender,createdate from (select no=row_number() over(partition by name,age order by createdate desc),* from tb) t where no=1
select name,age,gender,createdate from (select no=row_number() over(partition by name,age order by getdate()),* from tb) t where no=1
---------------------------------------------------------------- -- Author :fredrickhu(小F,向高手学习) -- Date :2011-12-10 20:23:03 -- Version: -- Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) -- Jul 9 2008 14:43:34 -- Copyright (c) 1988-2008 Microsoft Corporation -- Enterprise Evaluation Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3) -- ---------------------------------------------------------------- --> 测试数据:[tb] if object_id('[tb]') is not null drop table [tb] go create table [tb]([name] varchar(4),[age] int,[gender] varchar(2),[createdate] datetime) insert [tb] select '张三',15,'男','2011-12-5' union all select '张三',16,'男','2011-12-6' union all select '张三',15,'男','2011-12-9' union all select '张三',15,'男','2011-12-9' union all select '李四',14,'女','2011-12-3' union all select '李四',14,'女','2011-12-15' --------------开始查询-------------------------- select name,age,gender,createdate from (select no=row_number() over(partition by name,age order by createdate desc),* from tb) t where no=1 ----------------结果---------------------------- /* name age gender createdate ---- ----------- ------ ----------------------- 李四 14 女 2011-12-15 00:00:00.000 张三 15 男 2011-12-09 00:00:00.000 张三 16 男 2011-12-06 00:00:00.000(3 行受影响)*/
select name,age,gender,createdate from (select no=row_number() over(partition by name,age order by getdate()),* from tb) t where no=1
select name,age,gender,createdate from (select no=row_number() over(partition by name,age order by getdate()),* from tb) t where no=1
(select no=row_number() over(partition by name,age order by createdate desc),* from tb) t
where no=1
select
name,age,gender,createdate
from
(select no=row_number() over(partition by name,age order by getdate()),* from tb) t
where
no=1
-- Author :fredrickhu(小F,向高手学习)
-- Date :2011-12-10 20:23:03
-- Version:
-- Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Enterprise Evaluation Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([name] varchar(4),[age] int,[gender] varchar(2),[createdate] datetime)
insert [tb]
select '张三',15,'男','2011-12-5' union all
select '张三',16,'男','2011-12-6' union all
select '张三',15,'男','2011-12-9' union all
select '张三',15,'男','2011-12-9' union all
select '李四',14,'女','2011-12-3' union all
select '李四',14,'女','2011-12-15'
--------------开始查询--------------------------
select
name,age,gender,createdate
from
(select no=row_number() over(partition by name,age order by createdate desc),* from tb) t
where
no=1
----------------结果----------------------------
/* name age gender createdate
---- ----------- ------ -----------------------
李四 14 女 2011-12-15 00:00:00.000
张三 15 男 2011-12-09 00:00:00.000
张三 16 男 2011-12-06 00:00:00.000(3 行受影响)*/
name,age,gender,createdate
from
(select no=row_number() over(partition by name,age order by getdate()),* from tb) t
where
no=1
name,age,gender,createdate
from
(select no=row_number() over(partition by name,age order by getdate()),* from tb) t
where
no=1