如何通过单条SQLiteDatabase query()实现多分组求和及总距离统计？

你可以通过UNION ALL将三个统计逻辑合并成单条查询，同时添加一个标识字段区分不同的统计维度，无需多次查询。以下是具体实现方案：

1. 编写SQL语句 #

假设你的表名为sports_data，字段分别是sport_type（运动类型）、distance（距离）、timestamp（时间戳，秒级），可以用下面的SQL：

-- 统计总距离
SELECT '总距离' AS stat_type, NULL AS group_key, SUM(distance) AS total_distance
FROM sports_data
UNION ALL
-- 按运动类型分组统计
SELECT '类型分组' AS stat_type, sport_type AS group_key, SUM(distance) AS total_distance
FROM sports_data
GROUP BY sport_type
UNION ALL
-- 按日期分组统计（将时间戳转为YYYY-MM-DD格式）
SELECT '日期分组' AS stat_type, DATE(timestamp, 'unixepoch') AS group_key, SUM(distance) AS total_distance
FROM sports_data
GROUP BY DATE(timestamp, 'unixepoch')

• stat_type：标记当前行属于哪种统计结果（总距离/类型分组/日期分组）
• group_key：存储分组维度的值（运动类型或日期，总距离行设为NULL）
• total_distance：对应维度的距离总和

2. 在Android中执行查询 #

SQLiteDatabase.query()更适合单表查询，推荐用rawQuery()执行上述SQL：

String sql = "SELECT '总距离' AS stat_type, NULL AS group_key, SUM(distance) AS total_distance " +
             "FROM sports_data " +
             "UNION ALL " +
             "SELECT '类型分组' AS stat_type, sport_type AS group_key, SUM(distance) AS total_distance " +
             "FROM sports_data " +
             "GROUP BY sport_type " +
             "UNION ALL " +
             "SELECT '日期分组' AS stat_type, DATE(timestamp, 'unixepoch') AS group_key, SUM(distance) AS total_distance " +
             "FROM sports_data " +
             "GROUP BY DATE(timestamp, 'unixepoch')";

Cursor cursor = db.rawQuery(sql, null);

3. 遍历Cursor处理结果 #

通过stat_type字段判断当前行的统计类型，分别提取对应数据：

if (cursor != null && cursor.moveToFirst()) {
    float totalOverall = 0;
    Map<String, Float> typeStats = new HashMap<>();
    Map<String, Float> dateStats = new HashMap<>();

    do {
        String statType = cursor.getString(cursor.getColumnIndexOrThrow("stat_type"));
        float distance = cursor.getFloat(cursor.getColumnIndexOrThrow("total_distance"));

        switch (statType) {
            case "总距离":
                totalOverall = distance;
                break;
            case "类型分组":
                String sportType = cursor.getString(cursor.getColumnIndexOrThrow("group_key"));
                typeStats.put(sportType, distance);
                break;
            case "日期分组":
                String date = cursor.getString(cursor.getColumnIndexOrThrow("group_key"));
                dateStats.put(date, distance);
                break;
        }
    } while (cursor.moveToNext());

    // 这里可以使用统计好的数据生成报表
    Log.d("Stats", "总距离: " + totalOverall);
    Log.d("Stats", "类型统计: " + typeStats);
    Log.d("Stats", "日期统计: " + dateStats);

    cursor.close();
}

注意事项 #

• 如果你的时间戳是毫秒级，需要调整日期转换逻辑：DATE(timestamp / 1000, 'unixepoch')
• 确保字段名和表名与你的实际数据库一致
• 若数据量极大，单条UNION查询的性能可能略低于多条单独查询，但日常使用场景下差异可以忽略

内容的提问来源于stack exchange，提问作者Style-7