小弟我有一个小疑点，到哪也不急(小弟我着急了，刚才回答的朋友们小弟我粗心把null加引号了，所以答案基本都不对)

我有一个小问题，到哪也不急~~(我着急了，刚才回答的朋友们我粗心把null加引号了，所以答案基本都不对)

SQL code

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')

需要结果：
id name logo
1 aaa aaa.jpg
4 ccc null
5 bbb bbb.jpg
6 ddd ddd.jpg

解释下：就是说按照name分组排序，条件是如果有N条，那么取logo不为空的那条，如果只有一条，那么无论为空不为空都显示出来。
谢谢大家。

蛋疼，刚才null加引号了。郁闷

[解决办法]

SQL code

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 across apply(select top 1 * from @test where name=a.name order by Logo desc) t/*id          name                 Logo----------- -------------------- --------------------1           aaa                  aaa.jpg6           ccc                  ccc.jpg7           bbb                  bbb.jpg8           ddd                  ddd.jpg(4 行受影响)
[解决办法]
SQL code
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.jpg6           ccc                  ccc.jpg7           bbb                  bbb.jpg8           ddd                  ddd.jpg9           eee                  NULL*/ 

 

查看更多 下一篇