不用组函数(Max) ,取得某列最高值的三种解法

kyng1988

关注

最新推荐文章于 2026-02-24 01:45:08 发布 · 799 阅读

原创 ·

本内容遵循 CC 4.0 BY-SA 版权协议

SQL分组函数与分组查询（group by,sum,avg,max,min,count）分组函数功能：用作统计使用，又称为聚合函数或统计函数或组函数分类：sum 求和、avg 平均值、max 最大值、min 最小值、count 计算个数特点： sum/avg一般用于处理数值型数据，max/min/count可以处理任何类型数据以上分组函数都忽略null值 1、简单的使用 SELECT sum(salary) FROM employees; SELECT avg(salary) FROM employees; SELECT min(salary) FROM emplo 阅读详情

select xxx,jine from bpcmain where policycreday = '20220501' into temp tmp1;
select max(jine) from tmp1;
19420.34
常规做法：
select distinct (jine) from tmp1;

解法1：
select first 1 xxx,jine from bpcmain where policycreday = '20220501' order by jine desc;
10:09:59 [SELECT - 1 row(s), 0.020 secs] Result set fetched
... 1 statement(s) executed, 1 row(s) affected, exec/fetch time: 0.020/0.003 sec [1 successful, 0 warnings, 0 errors]

解法2：
select jine from tmp1
where jine not in
(
select distinct A.jine from tmp1 A, tmp1 B
where A.jine < B.jine
)
10:09:51 [SELECT - 1 row(s), 1.407 secs] Result set fetched
... 1 statement(s) executed, 1 row(s) affected, exec/fetch time: 1.407/0.003 sec [1 successful, 0 warnings, 0 errors]

解法3：
select * from tmp1 where jine >= all(select jine from tmp1);
10:09:43 [SELECT - 1 row(s), 0.013 secs] Result set fetched
... 1 statement(s) executed, 1 row(s) affected, exec/fetch time: 0.013/0.003 sec [1 successful, 0 warnings, 0 errors]

收录于