如何寻找最小/大值！

karaxebury

表格如下：
id num
01 1
02 3
03 3
04 5
05 18
06 99
..  ..

num有可能重复，id最大为10。

求
最小/最大   1/99
次小/次大   3/18
第三小/第三大 5
第四小/第四大
第五小/第五大  

wgfxman

--第5小（大）
select top 1 * from (select top 5 * from 表格 ORDER BY num (DESC)) m

。。。1\2\..同理

karaxebury

id数目不是固定的

michealhenry

--建表
create table tab
(
id varchar(2),
num int
)

insert into tab select '01', 1
insert into tab select '02', 3
insert into tab select '03', 3
insert into tab select '04', 5
insert into tab select '05', 18
insert into tab select '06', 99

--建立辅助表
select * into info
from
(
select 1 as num,'一' as cnum union
select 2,'二' union
select 3,'三' union
select 4,'四' union
select 5,'五' union
select 6,'六' union
select 7,'七' union
select 8,'八' union
select 9,'九' union
select 10,'十'
)tt

--语句
select
'第'+ c.cnum + '小/第'+ c.cnum + '大'as name,
case when a.id < (a.cou +1)/2 then cast(a.num as varchar) + '/'+cast(b.num as varchar)
     when a.id = (a.cou +1)/2 then cast(a.num as varchar)
     else '' end  as value
from
(
select distinct id = (select count(distinct num) from tab where num <= a.num),
cou = (select count(distinct num) from tab),
num
from tab a
)a inner join
(
select distinct id = (select count(distinct num) from tab where num <= a.num),
cou = (select count(distinct num) from tab),
num
from tab a
)b on a.id = b.cou + 1 - b.id
inner join info c on a.id = c.num

--结果
第一小/第一大        1/99
第二小/第二大        3/18
第三小/第三大        5
第四小/第四大       
第五小/第五大       

michealhenry

--修改一下


--建表
create table tab
(
id varchar(2),
num int
)

insert into tab select '01', 1
insert into tab select '02', 3
insert into tab select '03', 3
insert into tab select '04', 5
insert into tab select '05', 18
insert into tab select '06', 99

select * into info
from
(
select 1 as num,'最' as cnum union
select 2,'次' union
select 3,'第三' union
select 4,'第四' union
select 5,'第五' union
select 6,'第六' union
select 7,'第七' union
select 8,'第八' union
select 9,'第九' union
select 10,'第十'
)tt

--语句
select
c.cnum + '小/'+ c.cnum + '大'as name,
case when a.id < (a.cou +1)/2 then cast(a.num as varchar) + '/'+cast(b.num as varchar)
     when a.id = (a.cou +1)/2 then cast(a.num as varchar)
     else '' end  as value
from
(
select distinct id = (select count(distinct num) from tab where num <= a.num),
cou = (select count(distinct num) from tab),
num
from tab a
)a inner join
(
select distinct id = (select count(distinct num) from tab where num <= a.num),
cou = (select count(distinct num) from tab),
num
from tab a
)b on a.id = b.cou + 1 - b.id
inner join info c on a.id = c.num

--结果
最小/最大        1/99
次小/次大        3/18
第三小/第三大        5
第四小/第四大       
第五小/第五大       

michealhenry

最小/最大                1/99
次小/次大        3/18
第三小/第三大    5
第四小/第四大       
第五小/第五大       

3107869

用下面的数据
insert into tab select '01', 1
insert into tab select '02', 3
insert into tab select '03', 2
insert into tab select '04', 5
insert into tab select '05', 18
insert into tab select '06', 99
insert into tab select '07', 19
insert into tab select '08', 29
insert into tab select '09', 39
insert into tab select '10', 49
结果是：
最小/最大        1/99
次小/次大        2/49
第三小/第三大        3/39
第四小/第四大        5/29
第五小/第五大        18
第六小/第六大       
第七小/第七大       
第八小/第八大       
第九小/第九大       
第十小/第十大       

缺少一个19

michealhenry

--确实有问题

--建表
create table tab
(
id varchar(2),
num int
)

insert into tab select '01', 1
insert into tab select '02', 3
insert into tab select '03', 2
insert into tab select '04', 5
insert into tab select '05', 18
insert into tab select '06', 99
insert into tab select '07', 19
insert into tab select '08', 29
insert into tab select '09', 39
insert into tab select '10', 49

--修改后的语句
select
c.cnum + '小/'+ c.cnum + '大'as name,
case when a.id < (a.cou + 1)*1.0/2 then cast(a.num as varchar) + '/'+cast(b.num as varchar)
     when a.id = (a.cou + 1)*1.0/2 then cast(a.num as varchar)
     else '' end  as value
from
(
select distinct id = (select count(distinct num) from tab where num <= a.num),
cou = (select count(distinct num) from tab),
num
from tab a
)a inner join
(
select distinct id = (select count(distinct num) from tab where num <= a.num),
cou = (select count(distinct num) from tab),
num
from tab a
)b on a.id = b.cou + 1 - b.id
inner join info c on a.id = c.num

--结果
name          value                                                         
------------- -------------------------------------------------------------
最小/最大         1/99
次小/次大         2/49
第三小/第三大       3/39
第四小/第四大       5/29
第五小/第五大       18/19
第六小/第六大      
第七小/第七大      
第八小/第八大      
第九小/第九大      
第十小/第十大      

（所影响的行数为 10 行）


--====================================
--再测试之前的数据
--====================================
--建表 drop table tab
create table tab
(
id varchar(2),
num int
)

insert into tab select '01', 1
insert into tab select '02', 3
insert into tab select '03', 3
insert into tab select '04', 5
insert into tab select '05', 18
insert into tab select '06', 99

select
c.cnum + '小/'+ c.cnum + '大'as name,
case when a.id < (a.cou + 1)*1.0/2 then cast(a.num as varchar) + '/'+cast(b.num as varchar)
     when a.id = (a.cou + 1)*1.0/2 then cast(a.num as varchar)
     else '' end  as value
from
(
select distinct id = (select count(distinct num) from tab where num <= a.num),
cou = (select count(distinct num) from tab),
num
from tab a
)a inner join
(
select distinct id = (select count(distinct num) from tab where num <= a.num),
cou = (select count(distinct num) from tab),
num
from tab a
)b on a.id = b.cou + 1 - b.id
inner join info c on a.id = c.num

--结果
name          value                                                         
------------- -------------------------------------------------------------
最小/最大         1/99
次小/次大         3/18
第三小/第三大       5
第四小/第四大      
第五小/第五大      

（所影响的行数为 5 行）

mill1128

高手啊
我看不懂了 哈哈

jak1984

select '01' as id, 1 as num into #ls
union all select '02' as id,3
union all select '03' as id,4
union all select '04' as id,5
union all select '05' as id,8
union all select '06' as id,12
union all select '07' as id,22
union all select '08' as id,23
union all select '09' as id,90

select distinct '第'+cast(z.mc as varchar(10))+'大/第'+cast(z.mc as varchar(10))+'小' as mc,
cast(z.num as varchar(10))+'/'+cast(m.num as varchar(10)) as value
from
(select (select count(distinct num) from #ls where num>=a.num)as mc,a.num from #ls a ) z
inner join
(select (select count(distinct num) from #ls where num<=a.num)as mc,a.num from #ls a ) m on z.mc=m.mc
order by mc

drop table #ls

我写的一个，不知道是不是这样。