==求一Sql,谢谢(解决接贴)==
类似数据:
    name                                             Unit
Year-end   Population qqq
Year-end   Population 690
Year-end   Population N/A
Year-end   Population 10,000   person
Year-end   Population N/A
Permanent   Residents N/A
Permanent   Residents N/A
Permanent   Residents 10,000   person

希望得到:
    name                                             Unit
  Year-end   Population qqq
  Permanent   Residents 10,000   person

即:获取name的distinct,   Unit只要为N/A即可(即第一个是N/A则取第二个，直到取到部为N/A为止,仅取一个).

------解决方案--------------------
select
t.*
from
表 t
where
t.Unit = (select top 1 Unit from 表 where name=t.name and Unit <> 'N/A ')
------解决方案--------------------
--试试
select distinct(name),(select top 1 unit where name = a.name and unit <> 'N/A ')
from t a
------解决方案--------------------
create table # (name varchar(50), Unit varchar(50))
insert into # select 'Year-end Population ', 'qqq ' union all
select 'Year-end Population ', '690 ' union all
select 'Year-end Population ', 'N/A ' union all
select 'Year-end Population ', '10,000 person ' union all
select 'Year-end Population ', 'N/A ' union all
select 'Permanent Residents ', 'N/A ' union all
select 'Permanent Residents ', 'N/A ' union all
select 'Permanent Residents ', '10,000person '

select * from # t where Unit = (select top 1 Unit from # where name=t.name and Unit!= 'N/A ')

--
Year-end Population qqq
Permanent Residents 10,000person
------解决方案--------------------
好了：
create table #temp
( name varchar(50), Unit varchar(50)
)
insert into #temp
select 'Year-endPopulation ', 'qqq ' union all select 'Year-endPopulation ', '690 ' union all select 'Year-endPopulation ', 'N/A ' union all select 'Year-endPopulation ', '10,000person ' union all select 'Year-endPopulation ', 'N/A ' union all select 'PermanentResidents ', 'N/A ' union all select 'PermanentResidents ', 'N/A ' union all select 'PermanentResidents ', '10,000person '
select * from #temp


select name,max(unit) unit from #temp where unit not like '%N/A% '
group by name
order by name desc


name unit
------- -------

Year-endPopulation qqq
PermanentResidents 10,000person