Oracle的greatest和least函数

我就是我 2022-11-09 12:45 219阅读 0赞

碰巧看到一篇文章提到了Oracle的greatest函数，相对应的有个least，以为这俩是个新的函数，但是看了下官方文档，其实在11g的《SQL Language Reference》，就提到了他们，是我孤陋寡闻了。

从函数的名称上，能猜到这两个函数分别求的是最大值和最小值。

greatest函数介绍，

> GREATEST returns the greatest of a list of one or more expressions. Oracle Database uses the first expr to determine the return type. If the first expr is numeric, then Oracle determines the argument with the highest numeric precedence, implicitly converts the remaining arguments to that data type before the comparison, and returns that data type. If the first expr is not numeric, then each expr after the first is implicitly converted to the data type of the first expr before the comparison.
> 
> Oracle Database compares each expr using nonpadded comparison semantics. The comparison is binary by default and is linguistic if the NLS\_COMP parameter is set to LINGUISTIC and the NLS\_SORT parameter has a setting other than BINARY. Character comparison is based on the numerical codes of the characters in the database character set and is performed on whole strings treated as one sequence of bytes, rather than character by character. If the value returned by this function is character data, then its data type is VARCHAR2 if the first expr is a character data type and NVARCHAR2 if the first expr is a national character data type.

least函数介绍，

> LEAST returns the least of a list of one or more expressions. Oracle Database uses the first expr to determine the return type. If the first expr is numeric, then Oracle determines the argument with the highest numeric precedence, implicitly converts the remaining arguments to that data type before the comparison, and returns that data type. If the first expr is not numeric, then each expr after the first is implicitly converted to the data type of the first expr before the comparison.
> 
> Oracle Database compares each expr using nonpadded comparison semantics. The comparison is binary by default and is linguistic if the NLS\_COMP parameter is set to LINGUISTIC and the NLS\_SORT parameter has a setting other than BINARY. Character comparison is based on the numerical codes of the characters in the database character set and is performed on whole strings treated as one sequence of bytes, rather than character by character. If the value returned by this function is character data, then its data type is VARCHAR2 if the first expr is a character data type and NVARCHAR2 if the first expr is a national character data type.

概括来讲，

1. greatest/least可以接收一个或多个字面值/字段列，返回其中的最大值/最小值。

2. greatest/least返回的数据类型，参照第一个参数的数据类型。

greatest作为例子，如果是字面值，直接返回最大值，

SQL> select greatest(1, 2, 3) from dual;
    GREATEST(1,2,3)
    ---------------
                  3

创建测试表，具备2个number类型，2个varchar2类型，

SQL> create table t_compare (id1 number, id2 number, name1 varchar2(1), name2 varchar2(1));
    Table created.
    
    
    SQL> insert into t_compare values(1, 2, 'a', 'c');
    1 row created.
    
    
    SQL> insert into t_compare values(2, 3, 'A', 'D');
    1 row created.
    
    
    SQL> commit;
    Commit complete.

当参数是两个number类型的，返回的是每行中数值最大的值，

SQL> select greatest(id1, id2) from t_compare;
    GREATEST(ID1,ID2)
    -----------------
                    2
                    3

当参数是两个varchar2类型的，返回的是每行中字符最大的值，

SQL> select greatest(name1, name2) from t_compare;
    G
    -
    c
    D

number和varchar2类型混合比较时，如果number在前，返回的是number类型，此时name1和name2是字母，不能转换成数字，因此报错，

SQL> select least(id1, id2, name1, name2) from t_compare;
    select least(id1, id2, name1, name2) from t_compare
                           *
    ERROR at line 1:
    ORA-01722: invalid number

如果name1和name2是能转换成数字的值，就可以执行，

SQL> insert into t_compare values(1, 2, '3', '2', sysdate, sysdate);
    1 row created.
    
    
    SQL> select greatest(id1, id2, name1, name2) from t_compare where name1='3';
    GREATEST(ID1,ID2,NAME1,NAME2)
    -----------------------------
                                3

如果varchar2在前，返回的是varchar2类型，此时id1和id2可以转换成varchar2，因此不会报错，

SQL> select greatest(name1, name2, id1, id2) from t_compare;
    GREATEST(NAME1,NAME2,ID1,ID2)
    ----------------------------------------
    c
    D

除了数值、字符串类型，日期类型也能进行比较，

SQL> alter table t_compare add (t1 date, t2 date);
    Table altered.
    
    
    SQL> select * from t_compare;
           ID1        ID2 N N T1                 T2
    ---------- ---------- - - ------------------ ------------------
             1          2 a c 22-FEB-21          21-FEB-21
             2          3 A D 22-FEB-21          21-FEB-21
    
    
    SQL> select greatest(t1, t2) from t_compare;
    GREATEST(T1,T2)
    ------------------
    22-FEB-21
    22-FEB-21

如果date和number混合比较，提示两者不能互相转换，

SQL> select greatest(id1, id2, t1, t2) from t_compare;
    select greatest(id1, id2, t1, t2) from t_compare
                              *
    ERROR at line 1:
    ORA-00932: inconsistent datatypes: expected NUMBER got DATE
    
    
    SQL> select greatest(t1, t2, id1, id2) from t_compare;
    select greatest(t1, t2, id1, id2) from t_compare
                            *
    ERROR at line 1:
    ORA-00932: inconsistent datatypes: expected DATE got NUMBER

可以想象，Oracle可能存在其他和greatest/least相近的函数，看着很小，但在某些场景下，还是能起到一定的作用。个人理解，未必都得记住，当需要的时候，我们能进行有效的搜索，找到他们，知道怎么用，就可以了。

**近期更新的文章：**

《[我的股市生涯][Link 1]》

《[Oracle创建主键的三种方式][Oracle]》

《[非Oracle Linux下Oracle 19c CDB数据库安装][Oracle Linux_Oracle 19c CDB]》

《[案例纠正一则][Link 2]》

《[小白学习MySQL - 数据库软件和初始化安装][MySQL -]》

《[小白学习MySQL - 闲聊聊][MySQL - 1]》

《[Redis和Sentinel的安装部署和配置][Redis_Sentinel]》

《[“火线”和“零线”][Link 3]》

《[通过索引提升SQL性能案例一则][SQL]》

《[如何手动添加jar包到maven本地库？][jar_maven]》

《[1元股权转让的一点思考][1]》

《[如何打造一个经常宕机的业务系统？][Link 4]》

《[Linux恢复误删文件的操作][Linux]》

《[Linux的scp指令使用场景][Linux_scp]》

《[Oracle处理IN的几种方式][Oracle_IN]》

《[如何搭建一支拖垮公司的技术团队？][Link 5]》

《[IP地址解析的规则][IP]》

《[MySQL的skip-grant-tables][MySQL_skip-grant-tables]》

《[国产数据库不平凡的一年][Link 6]》

《[Oracle要求顺序的top数据检索问题][Oracle_top]》

《[日常工作中碰到的几个技术问题][Link 7]》

《[了解一下sqlhc][sqlhc]》

《[Oracle的MD5函数介绍][Oracle_MD5]》

《[Oracle 19c的examples静默安装][Oracle 19c_examples]》

《[sqlplus登录缓慢的解决][sqlplus]》

《[VMWare 11安装RedHat Linux 7过程中碰到的坑][VMWare 11_RedHat Linux 7]》

《[COST值相同？是真是假？][COST]》

《[Oracle 11g的examples静默安装][Oracle 11g_examples]》

《[同名的同义词和视图解惑][Link 8]》

《[v$和v\_$的一些玄机][v_v]》

**文章分类和索引：**

《[公众号700篇文章分类和索引][700]》

[Link 1]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a4aeddf52db865a96331d9a023f535d44393483a2d043c91eb1de4ef0d51c64264eb571e&idx=1&mid=2247491933&scene=21&sn=65fbd01000f8d350592c715f5f7c8779#wechat_redirect
[Oracle]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a4a7ddf52db166b63c72899ebcf8a6fbebc15bec11312bd89400bd29d88f0bfc9a8a812c&idx=1&mid=2247491924&scene=21&sn=6eafb363347d9624e2427d40510acd3f#wechat_redirect
[Oracle Linux_Oracle 19c CDB]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a4a3ddf52db52f85ca508e24fa60fe999ab2b73826fa79f8ea32df5aab88e36e44e267d8&idx=1&mid=2247491920&scene=21&sn=7db77f383482f0b45a094439d9af7dc7#wechat_redirect
[Link 2]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a4bfddf52da94c9ee9de7a30aaa255922422914250c9e70470f9bd3ce75af611da041c53&idx=1&mid=2247491916&scene=21&sn=69f664d411a22d9998f46916695885ab#wechat_redirect
[MySQL -]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a4c8ddf52dde849a70b34f2b62ff16593f81898cca7bc1fd7f0321588823016f0becaa18&idx=1&mid=2247491899&scene=21&sn=7affd185a81761eeaa5c3e143fc6f894#wechat_redirect
[MySQL - 1]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a4dcddf52dcad1708150cc6ceb101423eb5f4326ceff0f9dd53c05103bea76c1c5b7ccbc&idx=1&mid=2247491887&scene=21&sn=91485c182868ef19ad822c3ac9bf2ea9#wechat_redirect
[Redis_Sentinel]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a4c3ddf52dd5f4475241b627f5f1cf6aa602d15c94eec40795b600ff95bda09db52e108a&idx=1&mid=2247491888&scene=21&sn=f4133fb5ae9ce0018149e02c2c195b06#wechat_redirect
[Link 3]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a4ebddf52dfda1f8a857c0a1a527c7d7e663640b07ff8a67ad295e8c3218b87647981126&idx=1&mid=2247491864&scene=21&sn=e7cb2840e3ce2bab373f77f63d4b9a9d#wechat_redirect
[SQL]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a4fdddf52debb9cc3041f52085038f09b173444ada300999b058dc7ad19a9102fd1b5695&idx=1&mid=2247491854&scene=21&sn=d0f6880089bcbb4fe8ab7a658a460cd0#wechat_redirect
[jar_maven]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a4faddf52dec09683e7b533b56ed0430b99cda91ba3b41df893504d77690f68b8447c68a&idx=1&mid=2247491849&scene=21&sn=d502e38f03e1a222353544d1ce0aaaee#wechat_redirect
[1]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a4f6ddf52de04ccf3ab1219e1be19c9fa6a38cf8e3e74682d1f31b82cb1d8333692c1cce&idx=1&mid=2247491845&scene=21&sn=da9d31fd892da7e76671f0720721847e#wechat_redirect
[Link 4]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a51eddf52c085623fb43d61a2201e44477e04169098dd533076dbdf99377b931d671964d&idx=1&mid=2247491821&scene=21&sn=8169bff24df141481de7dfa370b91909#wechat_redirect
[Linux]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a514ddf52c02eb450efabec94a91e5f5e85ab76653cc4dd9d4cf87e8e15f766f5821f8b7&idx=1&mid=2247491815&scene=21&sn=d3954508c6e9ed2acc10a936aecc92b2#wechat_redirect
[Linux_scp]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a512ddf52c04b580ef998bcf292225635f04c743efee72fcc04fa0ffa8834e8f5351ea2f&idx=1&mid=2247491809&scene=21&sn=4b6e5ad829253bb6522f97c241f19d37#wechat_redirect
[Oracle_IN]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a528ddf52c3ed5a5613456abcaad5af328c5e8c59ed2fcf3a8ae21f0e87f2705545c80b6&idx=1&mid=2247491803&scene=21&sn=d40ee2f3334435f933b881e43389b716#wechat_redirect
[Link 5]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a527ddf52c310590250aea3a7f50e6ef0f3239a097a68a2f5506bae22bc618ce000e3440&idx=1&mid=2247491796&scene=21&sn=3fedb602e4d5acf03fc5460f288bf43c#wechat_redirect
[IP]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a53fddf52c29969fa924ed4644ebfabb1ce3d117443134060e4f7cc8f37e8177f5338700&idx=1&mid=2247491788&scene=21&sn=2790176fc4bd58abb337fd0ce786a190#wechat_redirect
[MySQL_skip-grant-tables]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a535ddf52c23f84ef003cdbadc868463c274971da2b6c79b5fe78660fc013b696c4f4339&idx=1&mid=2247491782&scene=21&sn=40c079eb9f84430e163ead1ee7b9fc85#wechat_redirect
[Link 6]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a549ddf52c5fb741cde2dad195f245871b0d7226a612ca326524d5af0968d674b64f0ac8&idx=1&mid=2247491770&scene=21&sn=d706bcda7de477177a15b14f64c99517#wechat_redirect
[Oracle_top]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a541ddf52c572d7c9c90b93b9f37674c07761a26b7b2566302f24fcb86636cae4e1c4cb8&idx=1&mid=2247491762&scene=21&sn=e92bdd8616515e6403c3ae3280142373#wechat_redirect
[Link 7]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a559ddf52c4f288da18bb70439b1e213a90f0587fdf2cd1d00c948f33f35be40de33bd05&idx=1&mid=2247491754&scene=21&sn=95533b540fe5809c9516468ad2f58c3e#wechat_redirect
[sqlhc]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a555ddf52c43093e82ff00b137c136c5aade5e902f84b523e609429a2de7bfacbd18ad67&idx=1&mid=2247491750&scene=21&sn=6ef9cc04a1a88217277d96072aa86fe2#wechat_redirect
[Oracle_MD5]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a57addf52c6c9d61af3dcf4f1ec9f6b93e51e6fd2d606c36fb8a243d70562c14cc525554&idx=1&mid=2247491721&scene=21&sn=8bdb65a2e11ef287f48d3d213642e4a8#wechat_redirect
[Oracle 19c_examples]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a588ddf52c9e16329e277f2dfdc74734b91bfde0770c545933fca6fb4d7cb224825169c4&idx=1&mid=2247491707&scene=21&sn=569f50f91b58c7cbcb652089e420ea11#wechat_redirect
[sqlplus]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a582ddf52c949d97894f8a2defe4b7fc21d58defa73dcae13f1cfe56d0cec41935b81c88&idx=1&mid=2247491697&scene=21&sn=ecb0e5cc2dc189be23279649b6aee945#wechat_redirect
[VMWare 11_RedHat Linux 7]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a593ddf52c85d1c3e8c3a8767e05f32dd38b61f3da14f3f8fd5521b60a6ca6fa081878c0&idx=1&mid=2247491680&scene=21&sn=27acee6fbe440c573488b4155314944d#wechat_redirect
[COST]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a5afddf52cb9bd94da2c5abd6f5bdcefad9035eacccf0fb96db79ff09ec8c38ae9f1941a&idx=1&mid=2247491676&scene=21&sn=4c9c289836ef2d1774136a88705960c7#wechat_redirect
[Oracle 11g_examples]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a5ccddf52cda2812771d563eea1db4c3b66f9891869920171bbe5650a45a2865bde0100e&idx=1&mid=2247491647&scene=21&sn=eca45fd1d7dffbf91ab199198adc1591#wechat_redirect
[Link 8]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a5c9ddf52cdfe10f04528a52dc3522a0bce8e452ad8f28aa72546ac95c83399c23fbf563&idx=1&mid=2247491642&scene=21&sn=9f4541a6750ff754c17d6d9ddd1da09c#wechat_redirect
[v_v]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a5c2ddf52cd4ed4a1a6de5d89f7a6638d8d9378c66739e63ebfd7f670002d76212eb88a2&idx=1&mid=2247491633&scene=21&sn=56f7465a50792828e5c679c69c2f6182#wechat_redirect
[700]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a5dfddf52cc949a5b8b316ee905927ff399713b34aec7d806c656e8ada09bfb562c039ca&idx=1&mid=2247491628&scene=21&sn=83bd320bd28da84501fada2e8d515e77#wechat_redirect