sqlite 求和同一日期的值并返回 1 行
我正在编写一个 Android 应用程序，存储锻炼日期和燃烧的卡路里。下面是我的表中的数据示例： 这是返回的：
SELECT month, day, calories FROM workouts ORDER BY year ASC, month ASC, day ASC LIMIT 12;
(month | day | calories)
3|2|714
3|3|945
3|4|630
3|10|446
3|16|396
3|20|255
3|22|108
3|23|112
3|23|169
3|23|2160
我为上面编写的代码是：
public Cursor getLastTwelveDays(){
Cursor c;
String[] s = {KEY_MONTH, KEY_DAY, KEY_CALORIES};
String order = KEY_YEAR + " ASC, " + KEY_MONTH + " ASC, " + KEY_DAY + " ASC LIMIT 12" ;
c = db.query(WORKOUT_TABLE, s, null, null, null, null, order);
return c;
}
我想将具有相同日期和月份的行组合成一行，如下所示：
3|2|714
3|3|945
3|4|630
3|10|446
3|16|396
3|20|255
3|22|108
3|23|2441 (112 + 169 + 2160)
另外，如果它很容易与 android 一起使用，那就太好了查询功能。
提前致谢。
I am writing an android application storing workout dates and calories burned. Below is an example of the data in my table:
this is returned by:
SELECT month, day, calories FROM workouts ORDER BY year ASC, month ASC, day ASC LIMIT 12;
(month | day | calories)
3|2|714
3|3|945
3|4|630
3|10|446
3|16|396
3|20|255
3|22|108
3|23|112
3|23|169
3|23|2160
the code i've written for the above is:
public Cursor getLastTwelveDays(){
Cursor c;
String[] s = {KEY_MONTH, KEY_DAY, KEY_CALORIES};
String order = KEY_YEAR + " ASC, " + KEY_MONTH + " ASC, " + KEY_DAY + " ASC LIMIT 12" ;
c = db.query(WORKOUT_TABLE, s, null, null, null, null, order);
return c;
}
I would like to combine the rows with the same day and month into a single row like so:
3|2|714
3|3|945
3|4|630
3|10|446
3|16|396
3|20|255
3|22|108
3|23|2441 (112 + 169 + 2160)
Also, it would be nice if it were easy to use with android's query function.
Thanks in advance.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
您正在寻找的是 group by 子句。使用 SUM() 方法获取每个日期的总卡路里。我认为 group by 子句应该放在 ORDER BY 子句之前。
从按月、日分组的锻炼中选择月、日、总和（卡路里）
What you are looking for is the group by clause. Use the SUM() method to get the total calories for each date. I believe the group by clause should go before the ORDER BY clause.
SELECT month, day, SUM(calories) FROM workouts GROUP BY month, day
听起来你想要一个子查询（即一个查询来计算每日卡路里的总和，在另一个查询中获取每天的卡路里总数）。
尝试查找 QUERY 和子查询示例。也许查询函数允许您将原始查询传递给它，以便执行您所描述的那种更高级的查询。
Sounds like you want a subquery (i.e. one query to calculate the SUM of the daily calories, inside another query that gets the calorie totals per day).
Try looking up QUERY and sub-query examples. Perhaps the query function allows you to pass a raw query into it, in order to do the kind of more advanced query that you're describing.
没有看到你的模式 - 也看看 SUM 和 GROUP BY 。
Without seeing your schema - look at SUM and GROUP BY as well.