类似数据:
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为止,仅取一个).
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为止,仅取一个).
即:获取name的distinct, Unit只要不为N/A即可(即第一个是N/A则取第二个,直到取到部为N/A为止,仅取一个).
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
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 #tempselect 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
select * from # t where Unit = (select top 1 Unit from # where name=t.name and Unit!='N/A')改为
select distinct * from # t where Unit = (select top 1 Unit from # where name=t.name and Unit!='N/A')
可以了,再次感谢.