php - 如何在mysql中没有group by子句的情况下计算mysql中的总计

Question

我想在不使用 group by clause 的情况下获取列的总 total，因为我不想concat 任何字符串。

我正在寻找这种想要的结果:

|  customer id | project name | Product Name | Quantity | price  | total    |
+--------------+--------------+--------------+----------+--------+----------+
|     9        |  xyz         |   ppn        |   2      |   2    |   4      |
+--------------+--------------+--------------+----------+--------+----------+
|    11        |    pqr       |    xxx       |    2     |   2    |    4     |
+--------------+--------------+--------------+----------+--------+----------+
|              |              |              |          |        |     8    |<=== Grand total  
+---------------------------------------------------------------------------+

这是 SQL Fiddle:http://sqlfiddle.com/#!9/30fdd5/2

Answer 1

我认为这可以满足您的需求:

SELECT customer_id, project_name, product_name, quantity,
       price, quantity * price as total 
FROM project_expenses_data
UNION ALL
SELECT NULL, NULL, NULL, NULL, NULL, sum(quantity * price)
FROM project_expenses_data;

要 100% 清楚总计是结果集中的最后一行，请添加子句 ORDER BY (customer_id IS NULL) ASC。