求一条SQL语句 求唯一性

[moduofanchen]

如上结果
我想得到user_id 不重复的一条记录

比如 14477 就取一条 哪一条没关系 但是group_id 也要出现
distinct 后就得不到group_id了

14477 P_201204231150031112
14478 P_201206151035405245
14479 P_201208072017098564

这种结果

[开着拖拉机泡妞]

select * from tb a
where a.group_id=(select top 1 group_id from tb b where a.user_id=b.user_id)

[开着拖拉机泡妞]

select * from tb a
where a.group_id=(select top 1 group_id from tb b where a.user_id=b.user_id order by group_id)

[-Tracy-McGrady-]

with cte as (select group_id,user_id,iid=row_number() over (order by getdate()) from 你的表) select a.group_id,a.user_id from cte a where not exists( select 1 from cte b where a.user_id=b.user_id and a.iid<b.iid )

[骑着蜗牛去爬山]

select group_id,user_id from (select *,row=row_number()over(partition by user_id order by getdate()))t where t.row=1

[骑着蜗牛去爬山]

select group_id,user_id from (select *,row=row_number()over(partition by user_id order by getdate()) from 表)t where t.row=1

[-Tracy-McGrady-]

create table tb(group_id varchar(20),user_iid int)
insert into tb(group_id,user_iid)
select 'sadfasfasdas',14477 union all
select 'sadfasfasdas',14477 union all
select 'sadfasfasdas',14477 union all
select 'sadfasfasdas',14478 union all
select 'sadfasfasdas',14478 union all
select 'sadfasfasdas',14478 union all
select 'sadfasfasdas',14478 union all
select 'sadfasfasdas',14479 union all
select 'sadfasfasdas',14479 union all
select 'sadfasfasdas',14479 union all
select 'sadfasfasdas',14477 union all
select 'sadfasfasdas',14477 

with cte as
(select group_id,user_iid,iid=row_number() over (order by getdate()) from tb)
select a.group_id,a.user_iid from cte a where not exists(
  select 1 from cte b where a.user_iid=b.user_iid and a.iid<b.iid
)

group_id             user_iid
-------------------- -----------
sadfasfasdas         14478
sadfasfasdas         14479
sadfasfasdas         14477

[xiaoqi7456]

引用 2 楼 TravyLee 的回复:

SQL code ? 123 select * from tb a where a.group_id=(select top 1 group_id from tb b where a.user_id=b.user_id order by group_id)

如果group_id内有重复的 则不能用这个办法