类似数据:
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