如何寻找最小/大值！

--第5小（大）
select top 1 * from (select top 5 * from 表格 ORDER BY num (DESC)) m1\2\..同理

--建表
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
第四小/第四大
第五小/第五大

--修改一下
--建表
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', 99select * 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
第四小/第四大
第五小/第五大

用下面的数据
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

--确实有问题--建表
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', 99select
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 行）

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,90select 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 mcdrop table #ls我写的一个，不知道是不是这样。

if object_id('ta')>0 drop table tacreate table ta (id varchar(8),num int)insert into ta select '01', 1
insert into ta select '02', 3
insert into ta select '03', 2
insert into ta select '04', 5
insert into ta select '05', 18
insert into ta select '06', 99
insert into ta select '07', 19
insert into ta select '08', 29
insert into ta select '09', 39
insert into ta select '10', 49insert into #t1 select * from ta
create table #ta(string1 varchar(20),string2 varchar(20))
declare @max int --始终取得最大值
declare @min int --始终取得最小值
declare @i int --次数
set @max=(select max(num) from #t1)
set @min=(select min(num) from #t1)
set @i=0
select * from #t1 where num in(select max(num) from #t1) union all select * from #t1 where num in(select min(num) from #t1)
while @@rowcount>0
begin
set @i=@i+1
insert into #ta select '最'+cast(@i as varchar)+'小/最'+cast(@i as varchar)+'大',cast(@min as varchar)+'/'+cast(@max as varchar)
delete from #t1 where id in(select id from #t1 where num in(select max(num) from #t1)) or id in(select id from #t1 where num in(select min(num) from #t1))
set @max=(select max(num) from #t1)
set @min=(select min(num) from #t1)
select * from #t1 where num in(select max(num) from #t1) union all select * from #t1 where num in(select min(num) from #t1)
endselect * from #ta
/*结果
string1              string2
-------------------- --------------------
最1小/最1大              1/99
最2小/最2大              2/49
最3小/最3大              3/39
最4小/最4大              5/29
最5小/最5大              18/19(5 行受影响)
*/drop table ta

调试易

如何寻找最小/大值！

解决方案 »