表面 tt
fname fcolor pic
001 红 aa.jpg
001 红 bb.jpg
002 绿 cc.jpg
002 绿 dd.jpg
003 绿 11.jpg
003 红 22.jpg如何取出FNAME和FCOLOR相同的只取第一个图。比如要的是这样结果集。001 红 aa.jpg
002 绿 cc.jpg
003 绿 11.jpg
fname fcolor pic
001 红 aa.jpg
001 红 bb.jpg
002 绿 cc.jpg
002 绿 dd.jpg
003 绿 11.jpg
003 红 22.jpg如何取出FNAME和FCOLOR相同的只取第一个图。比如要的是这样结果集。001 红 aa.jpg
002 绿 cc.jpg
003 绿 11.jpg
select distinct fname,fcolor,pic from tt
from tt t
where pic=(select min(pic)
from tt
where fname=t.fname and fcolor=t.fcolor )
if object_id('tempdb.dbo.#tt') is not null drop table #tt
create table #tt (fname varchar(3),fcolor varchar(2),pic varchar(6))
insert into #tt
select '001','红','aa.jpg' union all
select '001','红','bb.jpg' union all
select '002','绿','cc.jpg' union all
select '002','绿','dd.jpg' union all
select '003','绿','11.jpg' union all
select '003','红','22.jpg'
with cte as(
select row_number() over(partition by fname order by getdate()) id,fname,fcolor,pic
from #tt
)
select * from cte where id=1id fname fcolor pic
-------------------- ----- ------ ------
1 001 红 aa.jpg
1 002 绿 cc.jpg
1 003 绿 11.jpg(3 行受影响)
if object_id('tempdb.dbo.#tt') is not null drop table #tt
create table #tt (fname varchar(3),fcolor varchar(2),pic varchar(6))
insert into #tt
select '001','红','aa.jpg' union all
select '001','红','bb.jpg' union all
select '002','绿','cc.jpg' union all
select '002','绿','dd.jpg' union all
select '003','绿','11.jpg' union all
select '003','红','22.jpg'
with cte as(
select row_number() over(partition by fname order by getdate()) id,fname,fcolor,pic
from #tt
)
select * from cte where id=1id fname fcolor pic
-------------------- ----- ------ ------
1 001 红 aa.jpg
1 002 绿 cc.jpg
1 003 绿 11.jpg(3 行受影响)
from tt t
where not exists(select 1
from tt
where fname=t.fname and fcolor=t.fcolor
and pic<t.pic)
create table #tt (fname varchar(3),fcolor varchar(2),pic varchar(6))
insert into #tt
select '001','红','aa.jpg' union all
select '001','红','bb.jpg' union all
select '002','绿','cc.jpg' union all
select '002','绿','dd.jpg' union all
select '003','绿','11.jpg' union all
select '003','红','22.jpg'select * from #tt t where not exists(
select 1 from #tt where t.fname=fname and t.pic>pic)fname fcolor pic
----- ------ ------
001 红 aa.jpg
002 绿 cc.jpg
003 绿 11.jpg(3 行受影响)
FROM tt
GROUP BY fname
go
create table tb (fname varchar(3),fcolor varchar(2),pic varchar(6))
insert into tb
select '001','红','aa.jpg' union all
select '001','红','bb.jpg' union all
select '002','绿','cc.jpg' union all
select '002','绿','dd.jpg' union all
select '003','绿','11.jpg' union all
select '003','红','22.jpg'declare @fname varchar(20),@fcolor varchar(20),@pic varchar(20)
declare @t table(fname varchar(20),fcolor varchar(20),pic varchar(20))
declare cur_1 cursor for
select fname,fcolor,pic from tb
open cur_1
fetch next from cur_1 into @fname,@fcolor,@pic
while @@fetch_status=0
begin
if not exists(select 1 from @t where fname=@fname and fcolor=@fcolor )
insert into @t values(@fname,@fcolor,@pic)
fetch next from cur_1 into @fname,@fcolor,@pic
end
select * from @t
close cur_1
deallocate cur_1/*
fname fcolor pic
-------------------- -------------------- --------------------
001 红 aa.jpg
002 绿 cc.jpg
003 绿 11.jpg
003 红 22.jpg(所影响的行数为 4 行)
*/