求SQL语句，获得表中某字段无重复记录最小值

2007-12-19 03:20:47 324 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

sql基础+考点+题假如你是腾讯视频会员的产品经理：有一个会员表，命名为 txsp_vip，三个字段，date,qq,status(1为会员，0为非会员)，按 date 分区，每个分区全量数据。(select qq from txsp_vip where date>='当月第一天' and date求和函数，over()中加入partition by则可进行分区累计求和。(注:历史状态统一选取昨天对比) 阅读详情

有表 test_table
id value
1 20
2 20
3 25
4 25
5 30
6 35
7 40
现在要获得value字段的无重复最小的那个记录是：第5条 30 那个记录。虽然 1、2、3、4条记录比第5条小，但是有重复。请问这个SQL语句应该怎么写呢。

...全文

用户行为分析（SQL+Tableau+AARRR模型）一、数据来源及含义 1、数据来源阿里云天池：https://tianchi.aliyun.com/dataset/dataDetail?dataId=649&userId=1 UserBehavior.csv 原始数据有100150806条，数据量过大，不方便使用Mysql进行操作，本例先使用python对数据进行处理，选取其中的条数据用于本次用户行为分析。 2、字段含义 user_id：用户id item_id：商品Id category_id：商品类别id behavior_type：用户行阅读详情

robin100

MS-SQL Server 34,876 成员 254,635 内容

+ 关注 + 加入社区

切换为时间正序

areswang 2007-12-19

打赏
举报

declare @a table(id int,value int)
insert @a select 1,20
union all
select 2,20
union all
select 3,25
union all
select 4,25
union all
select 5,30
union all
select 6,35
union all
select 7,40
select * from @a

select min(a.value)
from (select value from @a group by value having count(1) = 1)a
------------
30

-狙击手- 2007-12-19

打赏
举报

create table tb(id  int,   value int)

insert into tb values(1,         20 )

insert into tb values(2,         20 )

insert into tb values(3,         25 )

insert into tb values(4,         25 )

insert into tb values(5,         30 )

insert into tb values(6,         35 )

insert into tb values(7,         40 )



select top 1 *  

from tb a

where exists

(

  select value from tb where value = a.value group by value having count(*) = 1

)



drop table tb

/*

id          value       

----------- ----------- 

5           30



（所影响的行数为 1 行）



*/

dawugui 2007-12-19

打赏
举报

create table tb(id  int,   value int)

insert into tb values(1,         20 )

insert into tb values(2,         20 )

insert into tb values(3,         25 )

insert into tb values(4,         25 )

insert into tb values(5,         30 )

insert into tb values(6,         35 )

insert into tb values(7,         40 )



select a.* from tb a where value in

(select top 1 value from tb group by value having count(*) = 1 order by value)



drop table tb



/*

id          value       

----------- ----------- 

5           30



（所影响的行数为 1 行）

*/

liangCK 2007-12-19

打赏
举报

select top 1 value from (select value from @t group by value having count(*)=1) t

wzy_love_sly 2007-12-19

打赏
举报

declare @tb table (id int,[value] int)

insert into @tb select 1,20

insert into @tb select 2,20

insert into @tb select 3,25

insert into @tb select 4,25

insert into @tb select 5,30

insert into @tb select 6,35

insert into @tb select 7,40



select min(value) from @tb a

where not exists(select 1 from @tb where  value=a.value group by value having count(1)>1)

hqhhh 2007-12-19

打赏
举报

create table #tmp(
id int,
value int)

insert into #tmp values(1, 20)
insert into #tmp values(2, 20)
insert into #tmp values(3, 25)
insert into #tmp values(4, 25)
insert into #tmp values(5, 30)
insert into #tmp values(6, 35)
insert into #tmp values(7, 40)
insert into #tmp values(8, 50)

select * from #tmp
where value=(select top 1 value from #tmp group by value having count(*)=1 order by value)

dawugui 2007-12-19

打赏
举报

create table tb(id  int,   value int)

insert into tb values(1,         20 )

insert into tb values(2,         20 )

insert into tb values(3,         25 )

insert into tb values(4,         25 )

insert into tb values(5,         30 )

insert into tb values(6,         35 )

insert into tb values(7,         40 )



select a.* from tb a where value in

(

  select min(value) from

  (

    select value from tb group by value having count(*) = 1

  ) t

)

drop table tb



/*

id          value       

----------- ----------- 

5           30



（所影响的行数为 1 行）

*/

chenhp520 2007-12-19

打赏
举报

select top 1 value
from test_table
group by value
having count(*) = 1
order by value

liangCK 2007-12-19

打赏
举报

declare @t table(id int,value int)

insert @t select 1,         20 

insert @t select 2 ,        20 

insert @t select 3  ,       25 

insert @t select 4   ,      25 

insert @t select 5    ,     30 

insert @t select 6     ,    35 

insert @t select 7      ,   40



select top 1 id,value from @t where value in(select value from @t group by value having count(*)=1)

order by value asc



/*

id          value       

----------- ----------- 

5           30



（所影响的行数为 1 行）

*/

dawugui 2007-12-19

打赏
举报

create table tb(id  int,   value int)

insert into tb values(1,         20 )

insert into tb values(2,         20 )

insert into tb values(3,         25 )

insert into tb values(4,         25 )

insert into tb values(5,         30 )

insert into tb values(6,         35 )

insert into tb values(7,         40 )



select min(value) from

(

  select value from tb group by value having count(*) = 1

) t



drop table tb



/*

----------- 

30



（所影响的行数为 1 行）

*/

dawugui 2007-12-19

打赏
举报

select min(value) from

(

  select value from tb group by value having count(*) = 1

) t

求SQL语句，获得表中某字段无重复记录最小值

相关推荐