使用sql的常用函数说明

本文档内容来源于 http://doc.myapps.cn/docs/common-script/common-script-1f1a4plofu22k

脚本说明

本文档说明在MyApps平台中使用SQL时常用的函数，包括聚合函数、字符串函数、日期函数、数学函数、条件函数等。

聚合函数

COUNT() - 统计记录数

-- 统计所有记录数
SELECT COUNT(*) as total FROM tlk_表单名;

-- 统计非空记录数
SELECT COUNT(item_字段名) as count FROM tlk_表单名;

-- 统计不同值的个数
SELECT COUNT(DISTINCT item_字段名) as distinct_count FROM tlk_表单名;

// 使用示例
var sql = "SELECT COUNT(*) as total FROM tlk_表单名 WHERE domainid = '" + domainId + "'";
var result = findBySQL(sql);
var count = result.getItemValueAsDouble("total");

SUM() - 求和

-- 对数值字段求和
SELECT SUM(item_金额字段) as total FROM tlk_表单名;

-- 条件求和
SELECT SUM(item_金额字段) as total FROM tlk_表单名 WHERE item_状态 = '已完成';

// 使用示例
var sql = "SELECT SUM(item_金额字段) as total FROM tlk_表单名 WHERE domainid = '" + domainId + "'";
var result = findBySQL(sql);
var sum = result.getItemValueAsDouble("total");

AVG() - 求平均值

-- 计算平均值
SELECT AVG(item_数值字段) as average FROM tlk_表单名;

// 使用示例
var sql = "SELECT AVG(item_数值字段) as average FROM tlk_表单名 WHERE domainid = '" + domainId + "'";
var result = findBySQL(sql);
var avg = result.getItemValueAsDouble("average");

MAX() - 求最大值

-- 获取最大值
SELECT MAX(item_数值字段) as max_value FROM tlk_表单名;

// 使用示例
var sql = "SELECT MAX(item_数值字段) as max_value FROM tlk_表单名 WHERE domainid = '" + domainId + "'";
var result = findBySQL(sql);
var max = result.getItemValueAsDouble("max_value");

MIN() - 求最小值

-- 获取最小值
SELECT MIN(item_数值字段) as min_value FROM tlk_表单名;

// 使用示例
var sql = "SELECT MIN(item_数值字段) as min_value FROM tlk_表单名 WHERE domainid = '" + domainId + "'";
var result = findBySQL(sql);
var min = result.getItemValueAsDouble("min_value");

字符串函数

CONCAT() - 字符串连接

-- 连接多个字符串
SELECT CONCAT(item_字段1, '-', item_字段2) as combined FROM tlk_表单名;

-- 连接字符串和数字
SELECT CONCAT('编号:', item_编号字段) as number FROM tlk_表单名;

// 使用示例
var sql = "SELECT CONCAT(item_姓名字段, '(', item_编号字段, ')') as display FROM tlk_表单名";
var result = findBySQL(sql);
var display = result.getItemValueAsString("display");

SUBSTRING() - 截取字符串

-- 从指定位置截取字符串
SELECT SUBSTRING(item_字段名, 1, 5) as sub FROM tlk_表单名;

-- 从指定位置截取到末尾
SELECT SUBSTRING(item_字段名, 3) as sub FROM tlk_表单名;

// 使用示例
var sql = "SELECT SUBSTRING(item_编号字段, 1, 3) as prefix FROM tlk_表单名";
var result = findBySQL(sql);
var prefix = result.getItemValueAsString("prefix");

LENGTH() - 字符串长度

-- 获取字符串长度
SELECT LENGTH(item_字段名) as len FROM tlk_表单名;

// 使用示例
var sql = "SELECT LENGTH(item_姓名字段) as len FROM tlk_表单名 WHERE id = '" + docId + "'";
var result = findBySQL(sql);
var length = result.getItemValueAsDouble("len");

UPPER() - 转大写

-- 转换为大写
SELECT UPPER(item_字段名) as upper FROM tlk_表单名;

// 使用示例
var sql = "SELECT UPPER(item_代码字段) as upper_code FROM tlk_表单名";
var result = findBySQL(sql);
var upperCode = result.getItemValueAsString("upper_code");

LOWER() - 转小写

-- 转换为小写
SELECT LOWER(item_字段名) as lower FROM tlk_表单名;

// 使用示例
var sql = "SELECT LOWER(item_代码字段) as lower_code FROM tlk_表单名";
var result = findBySQL(sql);
var lowerCode = result.getItemValueAsString("lower_code");

TRIM() - 去除空格

-- 去除前后空格
SELECT TRIM(item_字段名) as trimmed FROM tlk_表单名;

-- 去除左侧空格
SELECT LTRIM(item_字段名) as left_trimmed FROM tlk_表单名;

-- 去除右侧空格
SELECT RTRIM(item_字段名) as right_trimmed FROM tlk_表单名;

REPLACE() - 替换字符串

-- 替换字符串
SELECT REPLACE(item_字段名, '旧值', '新值') as replaced FROM tlk_表单名;

日期和时间函数

NOW() - 当前日期时间

-- 获取当前日期时间
SELECT NOW() as current_time FROM tlk_表单名;

-- 在WHERE条件中使用
SELECT * FROM tlk_表单名 WHERE item_日期字段 > NOW();

// 使用示例
var sql = "SELECT NOW() as current_time";
var result = findBySQL(sql);
var currentTime = result.getItemValueAsDate("current_time");

CURDATE() - 当前日期

-- 获取当前日期
SELECT CURDATE() as current_date FROM tlk_表单名;

-- 查询今天的记录
SELECT * FROM tlk_表单名 WHERE DATE(item_日期字段) = CURDATE();

CURTIME() - 当前时间

-- 获取当前时间
SELECT CURTIME() as current_time FROM tlk_表单名;

DATE_FORMAT() - 格式化日期

-- 格式化日期
SELECT DATE_FORMAT(item_日期字段, '%Y-%m-%d') as formatted_date FROM tlk_表单名;
SELECT DATE_FORMAT(item_日期字段, '%Y年%m月%d日') as formatted_date FROM tlk_表单名;

// 使用示例
var sql = "SELECT DATE_FORMAT(item_日期字段, '%Y-%m-%d') as formatted_date FROM tlk_表单名";
var result = findBySQL(sql);
var formattedDate = result.getItemValueAsString("formatted_date");

DATE_ADD() - 日期加法

-- 日期加天数
SELECT DATE_ADD(item_日期字段, INTERVAL 7 DAY) as future_date FROM tlk_表单名;

-- 日期加月份
SELECT DATE_ADD(item_日期字段, INTERVAL 1 MONTH) as next_month FROM tlk_表单名;

DATE_SUB() - 日期减法

-- 日期减天数
SELECT DATE_SUB(item_日期字段, INTERVAL 7 DAY) as past_date FROM tlk_表单名;

DATEDIFF() - 日期差

-- 计算两个日期之间的天数差
SELECT DATEDIFF(item_结束日期, item_开始日期) as days_diff FROM tlk_表单名;

YEAR() / MONTH() / DAY() - 提取日期部分

-- 提取年份
SELECT YEAR(item_日期字段) as year FROM tlk_表单名;

-- 提取月份
SELECT MONTH(item_日期字段) as month FROM tlk_表单名;

-- 提取日期
SELECT DAY(item_日期字段) as day FROM tlk_表单名;

数学函数

ABS() - 绝对值

-- 获取绝对值
SELECT ABS(item_数值字段) as abs_value FROM tlk_表单名;

ROUND() - 四舍五入

-- 四舍五入到整数
SELECT ROUND(item_数值字段) as rounded FROM tlk_表单名;

-- 四舍五入到指定小数位
SELECT ROUND(item_数值字段, 2) as rounded FROM tlk_表单名;

// 使用示例
var sql = "SELECT ROUND(item_金额字段, 2) as rounded_amount FROM tlk_表单名";
var result = findBySQL(sql);
var roundedAmount = result.getItemValueAsDouble("rounded_amount");

CEIL() - 向上取整

-- 向上取整
SELECT CEIL(item_数值字段) as ceiling FROM tlk_表单名;

FLOOR() - 向下取整

-- 向下取整
SELECT FLOOR(item_数值字段) as floor_value FROM tlk_表单名;

MOD() - 取模

-- 取模运算
SELECT MOD(item_数值字段, 10) as remainder FROM tlk_表单名;

条件函数

IF() - 条件判断

-- 简单条件判断
SELECT IF(item_状态字段 = '已完成', '是', '否') as is_completed FROM tlk_表单名;

-- 数值条件判断
SELECT IF(item_金额字段 > 1000, '高', '低') as level FROM tlk_表单名;

// 使用示例
var sql = "SELECT IF(item_状态字段 = '已完成', '已完成', '未完成') as status FROM tlk_表单名";
var result = findBySQL(sql);
var status = result.getItemValueAsString("status");

CASE - 多条件判断

-- CASE WHEN THEN ELSE END
SELECT 
    CASE 
        WHEN item_金额字段 > 10000 THEN '高'
        WHEN item_金额字段 > 5000 THEN '中'
        ELSE '低'
    END as level
FROM tlk_表单名;

// 使用示例
var sql = "SELECT " +
          "CASE " +
          "  WHEN item_金额字段 > 10000 THEN '高' " +
          "  WHEN item_金额字段 > 5000 THEN '中' " +
          "  ELSE '低' " +
          "END as level " +
          "FROM tlk_表单名";
var result = findBySQL(sql);
var level = result.getItemValueAsString("level");

COALESCE() - 返回第一个非NULL值

-- 返回第一个非NULL值
SELECT COALESCE(item_字段1, item_字段2, '默认值') as value FROM tlk_表单名;

ISNULL() / IFNULL() - 处理NULL值

-- 如果为NULL则返回默认值
SELECT IFNULL(item_字段名, '默认值') as value FROM tlk_表单名;

其他常用函数

GROUP BY - 分组

-- 按字段分组
SELECT item_状态字段, COUNT(*) as count 
FROM tlk_表单名 
GROUP BY item_状态字段;

ORDER BY - 排序

-- 升序排序
SELECT * FROM tlk_表单名 ORDER BY item_日期字段 ASC;

-- 降序排序
SELECT * FROM tlk_表单名 ORDER BY item_金额字段 DESC;

-- 多字段排序
SELECT * FROM tlk_表单名 ORDER BY item_状态字段 ASC, item_日期字段 DESC;

LIMIT - 限制记录数

-- 限制返回记录数
SELECT * FROM tlk_表单名 LIMIT 10;

-- 分页查询
SELECT * FROM tlk_表单名 LIMIT 10 OFFSET 20;

DISTINCT - 去重

-- 去重查询
SELECT DISTINCT item_字段名 FROM tlk_表单名;

综合示例

示例1：统计和格式化

(function(){
    var domainId = getWebUser().getDomainid();
    var sql = "SELECT " +
              "COUNT(*) as total, " +
              "SUM(item_金额字段) as total_amount, " +
              "AVG(item_金额字段) as avg_amount, " +
              "MAX(item_金额字段) as max_amount, " +
              "MIN(item_金额字段) as min_amount " +
              "FROM tlk_表单名 " +
              "WHERE domainid = '" + domainId + "'";

    var result = findBySQL(sql);

    if(result != null){
        var stats = {
            total: result.getItemValueAsDouble("total"),
            totalAmount: result.getItemValueAsDouble("total_amount"),
            avgAmount: result.getItemValueAsDouble("avg_amount"),
            maxAmount: result.getItemValueAsDouble("max_amount"),
            minAmount: result.getItemValueAsDouble("min_amount")
        };
        return JSON.stringify(stats);
    }

    return "{}";
})();

示例2：字符串处理

(function(){
    var sql = "SELECT " +
              "CONCAT(item_姓名字段, '(', item_编号字段, ')') as display_name, " +
              "UPPER(item_代码字段) as upper_code, " +
              "LENGTH(item_姓名字段) as name_length " +
              "FROM tlk_表单名 " +
              "WHERE id = '文档ID'";

    var result = findBySQL(sql);

    if(result != null){
        var info = {
            displayName: result.getItemValueAsString("display_name"),
            upperCode: result.getItemValueAsString("upper_code"),
            nameLength: result.getItemValueAsDouble("name_length")
        };
        return JSON.stringify(info);
    }

    return "{}";
})();

示例3：日期处理

(function(){
    var sql = "SELECT " +
              "DATE_FORMAT(item_日期字段, '%Y-%m-%d') as formatted_date, " +
              "DATEDIFF(CURDATE(), item_日期字段) as days_ago, " +
              "YEAR(item_日期字段) as year, " +
              "MONTH(item_日期字段) as month " +
              "FROM tlk_表单名 " +
              "WHERE id = '文档ID'";

    var result = findBySQL(sql);

    if(result != null){
        var dateInfo = {
            formattedDate: result.getItemValueAsString("formatted_date"),
            daysAgo: result.getItemValueAsDouble("days_ago"),
            year: result.getItemValueAsDouble("year"),
            month: result.getItemValueAsDouble("month")
        };
        return JSON.stringify(dateInfo);
    }

    return "{}";
})();

注意事项

• ⚠️ 函数兼容性: 不同数据库的函数可能有所不同，注意数据库类型
• ⚠️ NULL值处理: 注意函数对NULL值的处理方式
• ⚠️ 性能优化: 聚合函数和字符串函数可能影响查询性能
• ⚠️ 字段类型: 确保函数参数的类型匹配
• ⚠️ SQL注入: 构建SQL时注意防止SQL注入攻击
• 不同数据库的函数语法可能略有不同
• 建议在查询前测试函数的返回结果
• 注意函数的参数类型和返回值类型

使用场景

• 数据统计和分析
• 字符串处理和格式化
• 日期计算和格式化
• 数值计算和处理
• 条件判断和数据转换

---

如需查看完整脚本代码，请访问源文档链接。