3.2w+
社区成员
- 主页
- 基础类
- 应用实例
- 新技术前沿
求SQL语句，获得表中某字段无重复记录最小值
有表 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语句应该怎么写呢。
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语句应该怎么写呢。
...全文
请发表友善的回复…
发表回复
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
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) twzy_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)
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
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