select distinct fi,其他字段 from b where fi is not null
有主键吗?或者是能唯一标识一行的列,比如idselect * from yourtable where id in(select min(id) as id,f1 from yourtable group by f1)
假如你的表中有主键id ,可以这样 select * from yourtable where id in(select max(id) from yourtable where f1 is not null group by f1)
如果有主键id select * from b where id in(select max(id) from b where f1 is not null group by f1)
declare @m int, @F1 char(10) set @m=0 --把数据写到临时表,并增加标记字段 select *,@m as flag into #tmp_B from B where F1 is not null order by F1 --写标记 set @F1='***' update #tmp_B set @m=flag=case when @F1=F1 then 0 else 1 end, @F1=F1 --返回结果 select * from #tmp_B where flag=1drop table #tmp_B
--这是数据测试 create table b( F1 char(10) ) insert b select 'abc' insert b select 'abc' insert b select 'abc' insert b select 'abd' insert b select 'abd'declare @m int, @F1 char(10) set @m=0select *,@m as flag into #tmp_B from B where F1 is not null order by F1set @F1='***' update #tmp_B set @m=flag=case when @F1=F1 then 0 else 1 end, @F1=F1select * from #tmp_B where flag=1drop table #tmp_B drop table b--结果 F1 flag ---------- ----------- abc 1 abd 1(所影响的行数为 2 行)
select * from yourtable where id in(select max(id) from yourtable where f1 is not null group by f1)
select * from b where id in(select max(id) from b where f1 is not null group by f1)
@F1 char(10)
set @m=0
--把数据写到临时表,并增加标记字段
select *,@m as flag into #tmp_B from B where F1 is not null order by F1
--写标记
set @F1='***'
update #tmp_B set @m=flag=case when @F1=F1 then 0 else 1 end,
@F1=F1
--返回结果
select * from #tmp_B where flag=1drop table #tmp_B
create table b(
F1 char(10)
)
insert b select 'abc'
insert b select 'abc'
insert b select 'abc'
insert b select 'abd'
insert b select 'abd'declare @m int,
@F1 char(10)
set @m=0select *,@m as flag into #tmp_B from B where F1 is not null order by F1set @F1='***'
update #tmp_B set @m=flag=case when @F1=F1 then 0 else 1 end,
@F1=F1select * from #tmp_B where flag=1drop table #tmp_B
drop table b--结果
F1 flag
---------- -----------
abc 1
abd 1(所影响的行数为 2 行)