根据某个数值类型字段求和

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

脚本说明

该脚本用于根据某个数值类型字段求和。在MyApps平台中，可以通过SQL的SUM函数或遍历记录来计算数值字段的总和。

常见实现方式

方式1：使用SQL SUM函数求和

(function(){
    var domainId = getWebUser().getDomainid();
    var sql = "SELECT SUM(item_数值字段) as total FROM tlk_表单名 WHERE domainid = '" + domainId + "'";
    var result = findBySQL(sql);

    if(result != null){
        var sum = result.getItemValueAsDouble("total");
        return sum != null ? sum : 0;
    }

    return 0;
})();

方式2：根据条件求和

(function(){
    var condition = "条件值";
    var domainId = getWebUser().getDomainid();
    var sql = "SELECT SUM(item_数值字段) as total FROM tlk_表单名 WHERE item_条件字段 = '" + condition + "' AND domainid = '" + domainId + "'";
    var result = findBySQL(sql);

    if(result != null){
        var sum = result.getItemValueAsDouble("total");
        return sum != null ? sum : 0;
    }

    return 0;
})();

方式3：遍历记录求和

(function(){
    var domainId = getWebUser().getDomainid();
    var sql = "SELECT item_数值字段 FROM tlk_表单名 WHERE domainid = '" + domainId + "'";
    var datas = queryBySQL(sql);

    var sum = 0;
    if(datas != null && datas.size() > 0){
        for(var it = datas.iterator(); it.hasNext();){
            var data = it.next();
            var value = data.getItemValueAsDouble("数值字段");
            if(value != null){
                sum += value;
            }
        }
    }

    return sum;
})();

方式4：多个数值字段求和

(function(){
    var domainId = getWebUser().getDomainid();
    var sql = "SELECT SUM(item_字段1) as sum1, SUM(item_字段2) as sum2, SUM(item_字段3) as sum3 " +
              "FROM tlk_表单名 WHERE domainid = '" + domainId + "'";
    var result = findBySQL(sql);

    if(result != null){
        var sums = {
            field1: result.getItemValueAsDouble("sum1") || 0,
            field2: result.getItemValueAsDouble("sum2") || 0,
            field3: result.getItemValueAsDouble("sum3") || 0
        };
        return JSON.stringify(sums);
    }

    return "{}";
})();

扩展示例

示例1：根据日期范围求和

(function(){
    var startDate = "2024-01-01";
    var endDate = "2024-12-31";
    var domainId = getWebUser().getDomainid();

    var sql = "SELECT SUM(item_金额字段) as total " +
              "FROM tlk_表单名 " +
              "WHERE item_日期字段 >= '" + startDate + "' " +
              "AND item_日期字段 <= '" + endDate + "' " +
              "AND domainid = '" + domainId + "'";

    var result = findBySQL(sql);

    if(result != null){
        var sum = result.getItemValueAsDouble("total");
        return sum != null ? sum : 0;
    }

    return 0;
})();

示例2：根据用户分组求和

(function(){
    var domainId = getWebUser().getDomainid();
    var sql = "SELECT item_用户字段, SUM(item_金额字段) as total " +
              "FROM tlk_表单名 " +
              "WHERE domainid = '" + domainId + "' " +
              "GROUP BY item_用户字段";

    var datas = queryBySQL(sql);
    var sums = {};

    if(datas != null && datas.size() > 0){
        for(var it = datas.iterator(); it.hasNext();){
            var data = it.next();
            var userId = data.getItemValueAsString("用户字段");
            var total = data.getItemValueAsDouble("total") || 0;
            sums[userId] = total;
        }
    }

    return JSON.stringify(sums);
})();

示例3：根据状态分类求和

(function(){
    var domainId = getWebUser().getDomainid();

    // 查询不同状态的金额总和
    var sql = "SELECT item_状态字段, SUM(item_金额字段) as total " +
              "FROM tlk_表单名 " +
              "WHERE domainid = '" + domainId + "' " +
              "GROUP BY item_状态字段";

    var datas = queryBySQL(sql);
    var sums = {};

    if(datas != null && datas.size() > 0){
        for(var it = datas.iterator(); it.hasNext();){
            var data = it.next();
            var status = data.getItemValueAsString("状态字段");
            var total = data.getItemValueAsDouble("total") || 0;
            sums[status] = total;
        }
    }

    return JSON.stringify(sums);
})();

示例4：求和并格式化输出

(function(){
    var domainId = getWebUser().getDomainid();
    var sql = "SELECT SUM(item_金额字段) as total FROM tlk_表单名 WHERE domainid = '" + domainId + "'";
    var result = findBySQL(sql);

    if(result != null){
        var sum = result.getItemValueAsDouble("total") || 0;

        // 格式化金额（保留2位小数）
        var formatted = sum.toFixed(2);

        return "总金额: " + formatted;
    }

    return "总金额: 0.00";
})();

示例5：求和并计算平均值

(function(){
    var domainId = getWebUser().getDomainid();
    var sql = "SELECT SUM(item_数值字段) as total, COUNT(*) as count " +
              "FROM tlk_表单名 WHERE domainid = '" + domainId + "'";
    var result = findBySQL(sql);

    if(result != null){
        var sum = result.getItemValueAsDouble("total") || 0;
        var count = result.getItemValueAsDouble("count") || 0;
        var avg = count > 0 ? sum / count : 0;

        var stats = {
            sum: sum,
            count: count,
            average: avg
        };

        return JSON.stringify(stats);
    }

    return "{}";
})();

示例6：根据父文档求和子表数据

(function(){
    var parentId = "父文档ID";
    var sql = "SELECT SUM(item_金额字段) as total " +
              "FROM tlk_子表名 " +
              "WHERE parent = '" + parentId + "'";

    var result = findBySQL(sql);

    if(result != null){
        var sum = result.getItemValueAsDouble("total");
        return sum != null ? sum : 0;
    }

    return 0;
})();

示例7：求和并判断阈值

(function(){
    var domainId = getWebUser().getDomainid();
    var sql = "SELECT SUM(item_金额字段) as total FROM tlk_表单名 WHERE domainid = '" + domainId + "'";
    var result = findBySQL(sql);

    if(result != null){
        var sum = result.getItemValueAsDouble("total") || 0;
        var threshold = 10000;  // 阈值

        if(sum > threshold){
            return "总金额: " + sum + " (超过阈值 " + threshold + ")";
        }else{
            return "总金额: " + sum + " (未超过阈值 " + threshold + ")";
        }
    }

    return "总金额: 0";
})();

示例8：多条件求和

(function(){
    var condition1 = "条件1";
    var condition2 = "条件2";
    var domainId = getWebUser().getDomainid();

    var sql = "SELECT SUM(item_金额字段) as total " +
              "FROM tlk_表单名 " +
              "WHERE item_字段1 = '" + condition1 + "' " +
              "AND item_字段2 = '" + condition2 + "' " +
              "AND domainid = '" + domainId + "'";

    var result = findBySQL(sql);

    if(result != null){
        var sum = result.getItemValueAsDouble("total");
        return sum != null ? sum : 0;
    }

    return 0;
})();

示例9：求和并返回详细信息

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

    var result = findBySQL(sql);

    if(result != null){
        var stats = {
            sum: result.getItemValueAsDouble("total") || 0,
            count: result.getItemValueAsDouble("count") || 0,
            min: result.getItemValueAsDouble("min") || 0,
            max: result.getItemValueAsDouble("max") || 0
        };

        return JSON.stringify(stats);
    }

    return "{}";
})();

示例10：错误处理和日志记录

(function(){
    var domainId = getWebUser().getDomainid();

    try{
        var sql = "SELECT SUM(item_金额字段) as total FROM tlk_表单名 WHERE domainid = '" + domainId + "'";
        var result = findBySQL(sql);

        if(result != null){
            var sum = result.getItemValueAsDouble("total") || 0;
            println("求和结果: " + sum);
            return sum;
        }else{
            println("未找到数据");
            return 0;
        }
    }catch(e){
        println("错误: " + e.getMessage());
        return 0;
    }
})();

关键函数说明

• findBySQL(sql): 执行SQL查询并返回单条记录
• queryBySQL(sql): 执行SQL查询并返回多条记录
• result.getItemValueAsDouble("字段名"): 获取数值类型字段值
• getWebUser().getDomainid(): 获取当前用户的域ID
• SQL SUM函数: SUM(字段名) 用于计算字段总和

SQL SUM函数说明

基本语法

SELECT SUM(字段名) as total FROM 表名 WHERE 条件

注意事项

• SUM函数会忽略NULL值
• 如果所有值都是NULL，SUM返回NULL
• 可以使用COALESCE或ISNULL处理NULL值
• 可以结合GROUP BY进行分组求和

注意事项

• ⚠️ NULL值处理: SUM函数会忽略NULL值，但结果可能为NULL，需要判断
• ⚠️ 数据类型: 确保字段是数值类型（int、double、decimal等）
• ⚠️ 域ID: 查询时建议加上域ID条件，避免跨域数据
• ⚠️ 性能优化: 使用SQL SUM比遍历记录更高效
• ⚠️ 精度问题: 注意数值精度，可能需要格式化输出
• 使用SQL SUM函数比遍历记录求和更高效
• 注意处理NULL值，避免计算错误
• 可以结合GROUP BY进行分组求和

使用场景

• 统计金额总和
• 计算数量总和
• 数据汇总分析
• 报表统计
• 业务指标计算

---

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