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加引号了。郁闷
(
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
*/
粗心了,结果是不对,唉。我想抽自己
改了inert 没改答案