declare @table table(id int ,[type] int ,date datetime)
insert into @table
select 1,1,'2009-11-11 00:00:00' union all
select 2,1,'2010-11-11 00:00:00' union all
select 3,1,'2011-11-11 00:00:00' union all
select 4,2,'2009-11-11 00:00:00' union all
select 5,2,'2011-11-11 00:00:00'
select id,type,max(date)as date from @table group by id,type
求改写得到的数据是
3 1 2011-11-11 00:00:00.000
5 2 2011-11-11 00:00:00.000
------解决方案--------------------
select id ,[type] ,date from(
select *,row_number()over(partition by type id order by getdate) as num)a
where num=1
------解决方案--------------------
- SQL code
更改一下select id ,[type] ,date from(select *,row_number()over(partition by type id order by id desc) as num)awhere num=1
------解决方案--------------------
SQL code
更改一下
select id ,[type] ,date from(
select *,row_number()over(partition by type order by id desc) as num from @table
)a where num=1
------解决方案--------------------
- SQL code
declare @table table(id int ,[type] int ,date datetime)insert into @table select 1,1,'2009-11-11 00:00:00' union all select 2,1,'2010-11-11 00:00:00' union all select 3,1,'2011-11-11 00:00:00' union all select 4,2,'2009-11-11 00:00:00' union all select 5,2,'2011-11-11 00:00:00' select * from @table twhere id=(select max(id) from @table where [type]=t.[type])order by 1/*id type date----------- ----------- -----------------------3 1 2011-11-11 00:00:00.0005 2 2011-11-11 00:00:00.000*/
------解决方案--------------------
- SQL code
declare @table table(id int ,[type] int ,date datetime)insert into @table select 1,1,'2009-11-11 00:00:00' union all select 2,1,'2010-11-11 00:00:00' union all select 3,1,'2011-11-11 00:00:00' union all select 4,2,'2009-11-11 00:00:00' union all select 5,2,'2011-11-11 00:00:00' select t.* from @table t where id = (select max(id) from @table where type = t.type ) order by t.type/*id type date ----------- ----------- ------------------------------------------------------ 3 1 2011-11-11 00:00:00.0005 2 2011-11-11 00:00:00.000（所影响的行数为 2 行）*/select t.* from @table t where not exists (select 1 from @table where type = t.type and id > t.id) order by t.type/*id type date ----------- ----------- ------------------------------------------------------ 3 1 2011-11-11 00:00:00.0005 2 2011-11-11 00:00:00.000（所影响的行数为 2 行）*/
------解决方案--------------------
- SQL code
declare @table table(id int ,[type] int ,date datetime)insert into @table select 1,1,'2009-11-11 00:00:00' union all select 2,1,'2010-11-11 00:00:00' union all select 3,1,'2011-11-11 00:00:00' union all select 4,2,'2009-11-11 00:00:00' union all select 5,2,'2011-11-11 00:00:00' select id ,[type] ,date from( select *,row_number()over(partition by [type] order by id desc) as num from @table)a where num=1--select * from @table a where id=(select MAX(id) from @table b where a.[type]=b.[type])order by id/*id type date3 1 2011-11-11 00:00:00.0005 2 2011-11-11 00:00:00.000*/
------解决方案--------------------