php - 表格数据计算-在表格中间求和

Question

考虑一个简单的查询:

SELECT id、date、hours FROM employees where id=10

以及以下结果集:

id | date     | hours
---------------------
1   4-19-2012   4
1   4-19-2012   2.5
1   4-19-2012   1
1   4-20-2012   2
1   4-20-2012   3

我正在显示结果集的典型 HTML 表格输出，但我想在当天结果下方的一行中显示每日汇总。

+--------------------------------------+
| David | 4-19-2012 | 4                |
|       | 4-19-2012 | 2.5              |
|       | 4-19-2012 | 1                |
|--------------------------------------|
| TOTAL ............  7.5              |
|--------------------------------------|
| David | 4-20-2012 | 2                |
|       | 4-20-2012 | 1                |
|--------------------------------------|
| TOTAL ............ 3                 |
|--------------------------------------|
| GRAND TOTAL ..........10.5           |
+--------------------------------------+

处理该问题的最佳方法有哪些？有很多选择。子查询...将 PHP 代码放在我的 View 中...肯定有更好的方法。

Answer 1

已升级为答案

在 MySQL 中，您可以使用 WITH ROLLUP 在使用 GROUP BY 分组的表中添加额外的汇总行修饰符:

The GROUP BY clause permits a WITH ROLLUP modifier that causes extra rows to be added to the summary output. These rows represent higher-level (or super-aggregate) summary operations. ROLLUP thus enables you to answer questions at multiple levels of analysis with a single query. It can be used, for example, to provide support for OLAP (Online Analytical Processing) operations.

根据您的查询，您可以:

SELECT id, date, SUM(hours) AS totalhours
FROM employees
WHERE id = 10
GROUP BY id, date, hours WITH ROLLUP