计算函数
本章主要展示关于计算函数的代码参考，比如IS NULL/NVL/COALESCE、CAST/CONVERT、CASE/DECODE、AND/OR/NOT, <>、LIKE、COUNT/SUM/AVG/MAX/MIN、IN/EXISTS等。
IS NULL/NVL/COALESCE
|
|
A |
|
|
1 |
=demo.query("select EID,NAME,STATE,GENDER,BIRTHDAY, HIREDATE,DEPT, SALARY from EMPLOYEE") |
|
|
2 |
=A1.select(DEPT!=null) |
不为空 |
|
3 |
=A1.select(DEPT ==null) |
为空 |
|
4 |
=demo.query("select NAME,UNITPRICE,QUANTITY from RECEIPT") |
|
|
5 |
=A4.(NAME).ifn() |
第一个不为空的成员 |
CAST/CONVERT
|
|
A |
|
|
1 |
=date("1983-09-12") |
字符变日期 |
|
2 |
=string(A1,"yyyyMMdd") |
将日期转换为字符串 |
|
3 |
=int("5") |
字符变整数 |
|
4 |
=string(5) |
整数变字符 |
|
5 |
=decimal(A3) |
整数变big decimal |
|
6 |
=ifnumber(A5) |
判断A5是否数字 |
|
7 |
=float("234") |
字符变float |
CASE/DECODE
|
|
A |
|
|
1 |
1 |
|
|
2 |
=case(A1,1:"ClassOne",2:"ClassTwo";"ClassThree") |
|
|
3 |
=if(A1==1:"ClassOne", A1==2:"ClassTwo";"ClassThree") |
|
AND/OR/NOT, <>
|
|
A |
|
|
1 |
=demo.query("select * from SALES") |
|
|
2 |
=A1.select(CLIENT=="PWQ" || CLIENT=="QUICK") |
OR |
|
3 |
=A1.select(AMOUNT>5000 && AMOUNT<10000) |
AND |
|
4 |
=A1.select(!(CLIENT=="TRADH")) |
NOT |
|
5 |
=A1.select(CLIENT!="TRADH") |
<> |
LIKE
|
|
A |
|
|
1 |
=demo.query("select * from SALES") |
|
|
2 |
=A1.select(like(CLIENT,"*AY*")) |
|
COUNT/SUM/AVG/MAX/MIN
|
|
A |
|
|
1 |
=demo.query("select * from SALES") |
|
|
2 |
=A1.sum(AMOUNT) |
|
|
3 |
=A1.count() |
|
|
4 |
=A1.avg(AMOUNT) |
|
|
5 |
=A1.max(AMOUNT) |
|
|
6 |
=A1.min(AMOUNT) |
|
IN/EXISTS
|
|
A |
|
|
1 |
[1,3,5,7,9] |
|
|
2 |
=demo.query("select * from EMPLOYEE") |
|
|
3 |
=A2.select(A1.pos(EID)>0) |
IN |
|
4 |
[English,Math] |
|
|
5 |
=demo.query("select * from SCORES where CLASS='Class one'") |
|
|
6 |
=A5.select(SCORE>75).group(STUDENTID) |
|
|
7 |
=A6.select(~.(SUBJECT).pos(A4)!=null) |
EXISTS |
|
8 |
=A7.(STUDENTID) |
|