sql group by 查询
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
求改写得到的数据是
312011-11-11 00:00:00.000
522011-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
[解决办法]
更改一下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
[解决办法]
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*/
[解决办法]
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 行）*/
[解决办法]
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*/
[解决办法]
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 date = (select max(date) 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 date > t.date) 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 行）*/--以ID算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 行）*/--以ID算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 行）*/
[解决办法]
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 date = (select max(date) 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 date > t.date) 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 行）*/--以ID算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 行）*/--以ID算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 行）*/--同时按照时间+ID算，即如果最大时间相同，则取最大的IDselect t.* from @table t where not exists (select 1 from @table where type = t.type and (date > t.date or (date = t.date 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 行）*/
[解决办法]
我的两种方法都可以实现啊，他们的也都是那个意思
