以下测试 create table test(id int,name nvarchar(20),address varchar(20),status int,date varchar(10)) go insert into test select 1 id,'张三'name,'海淀'address, 1 status,'2002-12-12'date union all select 2,'张三','海淀',1,'2002-12-13' union all select 3, '张三', '海淀', 2 , '2002-12-14' union all select 4, '张三', '海淀',3 , '2002-12-17' union all select 5, '李四', '朝阳', 1 , '2002-12-11' union all select 6, '李四', '朝阳', 2 , '2002-12-12' union all select 7, '李四', '朝阳',2 , '2002-12-13' union all select 8, '李四', '朝阳', 3 , '2002-12-18' go select * from test t where date=(select max(date) from test t1 where t1.name=t.name and t1.status=t.status) order by id drop table test 结果: 2 张三 海淀 1 2002-12-13 3 张三 海淀 2 2002-12-14 4 张三 海淀 3 2002-12-17 5 李四 朝阳 1 2002-12-11 7 李四 朝阳 2 2002-12-13 8 李四 朝阳 3 2002-12-18
select id,name,address,max(status),date from table
select id,name,address,max(status),max(date) from table
得到你的例子结果select max(id),name,max(address),status,max([date]) from #t group by name,status
得到二行的数据select a.* from #T a where status=(select max(status) from #T where name=a.name) and [date]=(select max([date]) from #T where status=a.status)
补充select a.* from #T a where status=(select max(status) from #T where name=a.name) and [date]=(select max([date]) from #T where status=a.status and name=a.name)
exec dbo.create_table '##T','int,varchar(10),varchar(10),varchar(10),int,datetime',' ID Name Address x Status Date 1 张三 海淀 ...... 1 2002-12-12 2 张三 海淀 ...... 1 2002-12-13 3 张三 海淀 ...... 2 2002-12-14 4 张三 海淀 ...... 3 2002-12-17 5 李四 朝阳 ...... 1 2002-12-11 6 李四 朝阳 ...... 2 2002-12-12 7 李四 朝阳 ...... 2 2002-12-13'select a.* from ##T a where status=(select max(status) from ##T where name=a.name) and [date]=(select max([date]) from ##T where status=a.status and name=a.name)D Name Address x Status date ----------- ---------- ---------- ---------- ----------- ---------------------4 张三 海淀 ...... 3 2002-12-17 00:00:00.000 7 李四 朝阳 ...... 2 2002-12-13 00:00:00.000(2 row(s) affected)
select name,address,max(id),max(status) from table group by name,address
create table test(id int,name nvarchar(20),address varchar(20),status int,date varchar(10))
go
insert into test select 1 id,'张三'name,'海淀'address, 1 status,'2002-12-12'date union all
select 2,'张三','海淀',1,'2002-12-13'
union all select 3, '张三', '海淀', 2 , '2002-12-14'
union all select 4, '张三', '海淀',3 , '2002-12-17'
union all select 5, '李四', '朝阳', 1 , '2002-12-11'
union all select 6, '李四', '朝阳', 2 , '2002-12-12'
union all select 7, '李四', '朝阳',2 , '2002-12-13'
union all select 8, '李四', '朝阳', 3 , '2002-12-18'
go
select * from test t where date=(select max(date) from test t1 where t1.name=t.name and t1.status=t.status) order by id
drop table test
结果:
2 张三 海淀 1 2002-12-13
3 张三 海淀 2 2002-12-14
4 张三 海淀 3 2002-12-17
5 李四 朝阳 1 2002-12-11
7 李四 朝阳 2 2002-12-13
8 李四 朝阳 3 2002-12-18
数据如下
ID Name Address ...... Status Date
1 张三 海淀 ...... 1 2002-12-12
2 张三 海淀 ...... 1 2002-12-13
3 张三 海淀 ...... 2 2002-12-14
4 张三 海淀 ...... 3 2002-12-17
5 李四 朝阳 ...... 1 2002-12-11
6 李四 朝阳 ...... 2 2002-12-12
7 李四 朝阳 ...... 2 2002-12-13
...我想实现取出如下数据4 张三 海淀 ...... 3 2002-12-17
7 李四 朝阳 ...... 2 2002-12-13同一Status中取Date最大值,然后根据Name取Status最大值
from #t
group by name,status
where status=(select max(status) from #T where name=a.name)
and [date]=(select max([date]) from #T where status=a.status)
where status=(select max(status) from #T where name=a.name)
and [date]=(select max([date]) from #T where status=a.status and name=a.name)
ID Name Address x Status Date
1 张三 海淀 ...... 1 2002-12-12
2 张三 海淀 ...... 1 2002-12-13
3 张三 海淀 ...... 2 2002-12-14
4 张三 海淀 ...... 3 2002-12-17
5 李四 朝阳 ...... 1 2002-12-11
6 李四 朝阳 ...... 2 2002-12-12
7 李四 朝阳 ...... 2 2002-12-13'select a.* from ##T a
where status=(select max(status) from ##T where name=a.name)
and [date]=(select max([date]) from ##T where status=a.status and name=a.name)D Name Address x Status date
----------- ---------- ---------- ---------- ----------- ---------------------4 张三 海淀 ...... 3 2002-12-17 00:00:00.000
7 李四 朝阳 ...... 2 2002-12-13 00:00:00.000(2 row(s) affected)
from table
group by name,address