日期:2014-05-18 浏览次数:20579 次
declare @test table ( id int identity primary key, name nvarchar(20), Logo nvarchar(20) ) insert into @test values('aaa','aaa.jpg') insert into @test values('aaa',null) insert into @test values('bbb',null) insert into @test values('ccc',null) insert into @test values('ccc',null) insert into @test values('ccc','ccc.jpg') insert into @test values('bbb','bbb.jpg') insert into @test values('ddd','ddd.jpg')
declare @test table ( id int identity primary key, name nvarchar(20), Logo nvarchar(20) ) insert into @test values('aaa','aaa.jpg') insert into @test values('aaa',null) insert into @test values('bbb',null) insert into @test values('ccc',null) insert into @test values('ccc',null) insert into @test values('ccc','ccc.jpg') insert into @test values('bbb','bbb.jpg') insert into @test values('ddd','ddd.jpg') select distinct t.* from @test a cross apply(select top 1 * from @test where name=a.name order by Logo desc) t /* id name Logo ----------- -------------------- -------------------- 1 aaa aaa.jpg 6 ccc ccc.jpg 7 bbb bbb.jpg 8 ddd ddd.jpg (4 行受影响)
------解决方案--------------------
declare @test table ( id int identity primary key, name nvarchar(20), Logo nvarchar(20) ) insert into @test values('aaa','aaa.jpg') insert into @test values('aaa',null) insert into @test values('bbb',null) insert into @test values('ccc',null) insert into @test values('ccc',null) insert into @test values('ccc','ccc.jpg') insert into @test values('bbb','bbb.jpg') insert into @test values('ddd','ddd.jpg') insert into @test values('eee',null) select * from @test a where isnull(Logo,'')= (select isnull(max(Logo),'') from @test where name=a.name) /* id name Logo ----------- -------------------- -------------------- 1 aaa aaa.jpg 6 ccc ccc.jpg 7 bbb bbb.jpg 8 ddd ddd.jpg 9 eee NULL */