表结构是这样的。
num name state
1 a 4
2 d 5
1 a 4
2 d 6
1 a 5
3 v 4
1 a 6
需要的数据是:
1 a 6
2 d 6
3 v 4
请问这个该如何筛选?
num name state
1 a 4
2 d 5
1 a 4
2 d 6
1 a 5
3 v 4
1 a 6
需要的数据是:
1 a 6
2 d 6
3 v 4
请问这个该如何筛选?
if object_id('tempdb.dbo.#tb') is not null drop table #tb
go
create table #tb (num int,name varchar(1),state int)
insert into #tb
select 1,'a',4 union all
select 2,'d',5 union all
select 1,'a',4 union all
select 2,'d',6 union all
select 1,'a',5 union all
select 3,'v',4 union all
select 1,'a',6select * from #tb t
where not exists(select * from #tb where num=t.num and state>t.state)num name state
----------- ---- -----------
2 d 6
3 v 4
1 a 6(3 行受影响)
SELECT num,NAME,Max(state) FROM tab GROUP BY num,name