select * from tb
where (select count(1) from tb t where 编号=tb.编号 and 值<tb.值)=1
where (select count(1) from tb t where 编号=tb.编号 and 值<tb.值)=1
解决方案 »
- bom问题
- 不同服务器数据库表同步
- 在sql中日期的格式是如何控制的,在线等
- 如何写按B表分类求和排序的SQL语句?
- 根据字段查询表时,将另一字段部分数据加-号,怎么办呢?
- sql server 2008 未能解决的问题
- 急求,如何讲一个TEXT类型的数据拆分插入到一个表中
- 求一份展开BOM的方法,易飞ERP
- 在SQL2008中使用TOP造成查询时间差异近60倍的问题,为什么?
- 取recordset中大最大值
- 从一个数据表中读取某一字段的所有数据,譬如说共有3个数据,我现在想做的是针对于这8个数据中的每一行数据,我都用一个别名来表示,例如select ss.top1 as A,ss.top2 as B,ss.top3 as C from table;其中ss为
- 应“背着灵魂漫步”的要求,散吧
insert tb select '01', 10
union all select '01', 15
union all select '01', 18
union all select '01', 20
union all select '02', 5
union all select '02', 10
union all select '02', 15
union all select '03', 10
union all select '03', 15
union all select '03', 20
union all select '03', 25
union all select '03', 30
select * from tb
where (select count(1) from tb t where 编号=tb.编号 and 值<tb.值)=1/*
编号 值
-------- -----------
01 15
02 10
03 15(3 row(s) affected)
*/drop table tb
insert into @t values('01',10)
insert into @t values('01',15)
insert into @t values('01',18)
insert into @t values('01',20)
insert into @t values('02', 5)
insert into @t values('02',10)
insert into @t values('02',15)
insert into @t values('03',10)
insert into @t values('03',15)
insert into @t values('03',20)
insert into @t values('03',25)
insert into @t values('03',30)select
t.*
from
@t t
where
t.值=(select
min(b.值)
from
@t b
where
b.编号=t.编号
and
exists(select 1 from @t where 编号=b.编号 and 值<b.值))
/*
编号 值
---------- -----------
01 15
02 10
03 15
*/
insert into @t values('01',10)
insert into @t values('01',15)
insert into @t values('01',18)
insert into @t values('01',20)
insert into @t values('02', 5)
insert into @t values('02',10)
insert into @t values('02',15)
insert into @t values('03',10)
insert into @t values('03',15)
insert into @t values('03',20)
insert into @t values('03',25)
insert into @t values('03',30)select
a.*
from
@t a,@t b
where
a.编号=b.编号 and a.值>b.值
group by
a.编号,a.值
having count(*)=1
order by
a.编号/*
编号 值
---------- -----------
01 15
02 10
03 15
*/
insert into @tb select 1,10
insert into @tb select 1,15
insert into @tb select 1,20
insert into @tb select 2,10
insert into @tb select 2,16
insert into @tb select 2,20select id,num
from @tb a where exists
(select 1 from @tb where id=a.id and num<a.num
group by id having count(1)=1 ) id num
1 15
2 16
create table tb(编号 varchar(8), 值 int)
insert tb select '01', 10
union all select '01', 15
union all select '01', 18
union all select '01', 20
union all select '02', 5
union all select '02', 10
union all select '02', 15
union all select '03', 10
union all select '03', 15
union all select '03', 20
union all select '03', 25
union all select '03', 30--找第二小的
select * from tb
where (select count(1) from tb t where 编号=tb.编号 and 值<=tb.值)=2--第三小的
select * from tb
where (select count(1) from tb t where 编号=tb.编号 and 值<=tb.值)=3--直接改变后面的数字就行了``
insert @t select
'01',10 union select
'01',15 union select
'01',18 union select
'01',20 union select
'02',5 union select
'02',10 union select
'02',15 union select
'03',10 union select
'03',15 union select
'03',20 union select
'03',25 union select
'03',30select * from @t a
where (select count(1) from @t t where 编号=a.编号 and 值<a.值)=1
insert into @tb select 1,10
insert into @tb select 1,11
insert into @tb select 1,15
insert into @tb select 1,20insert into @tb select 2,16
insert into @tb select 2,20
insert into @tb select 2,13
insert into @tb select 2,21select *
from @tb a where
(select count(1) from @tb where id=a.id and num>a.num )=2id num
1 11
2 16好多方法啊