导航
  • 主页
  • 基础类
  • 应用实例
  • 新技术前沿

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

robin100 2007-12-19 03:20:47
有表 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语句应该怎么写呢。
...全文
231 点赞 收藏 11
写回复
11 条回复
切换为时间正序
请发表友善的回复…
发表回复
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
回复
发动态
发帖子
MS-SQL Server
创建于2007-09-28

3.2w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
社区公告
暂无公告