sql函数
1. REPLACE;
2. ROUND;
3. AVG;
4. JSON_EXTRACT
1.
REPLACE(column,String key, String value)
作用:将column属性列内包含key的内容替换成value显示;
Eg:SELECT REPLACE(NAME,"李","PP") AS NAMES FROM XXX;
NAMES
PP元霸(李元霸)
PP大钊(李大钊)
2.
ROUND(number,decimail);
作用:四舍五入函数,根据指定的参数“number”和保留小数位“decimail”进行数据四舍五入计算并返回;
Eg:
select round(123.456, 0) from dual; -- 返回:123
select round(123.456, 1) from dual; -- 返回:123.5
select round(123.456, 2) from dual; -- 返回:123.46
select round(123.456, 3) from dual; -- 返回:123.456
3.
AVG(number);
作用:函数返回数值列的平均值。NULL 值不包括在计算中。
Eg:SELECT AVG(COUNT) AS COUNTS FROM TABLE_NAME;
4.
JSON_EXTRACT(column,"$.key");
作用:获取某json格式存储的属性列内指定json数据内key对应的value值;
Eg:
data:
column
JSON_COLUMN
{"KEY_A":"VALUE_1","KEY_B":"VALUE_2","KEY_C":"VALUE_3", ...... "KEY_N":"VALUE_N"}
......
......
......
SQL:
SELECT JSON_EXTRACT(JSON_COLUMN,"$.key_B") FROM TABLE_NAME;