SQL将摘要行添加到MySQL结果集 [英] SQL to add a summary row to MySQL result set

问题描述

如果我有一个MySQL表，例如:

If I have a MySQL table such as:

我想使用SQL来计算PositiveResult列和NegativeResult列的总和.通常，我可以简单地在查询中执行SUM(PositiveResult).

I want to use SQL to calculate the sum of the PositiveResult column and also the NegativeResult column. Normally I could simply do SUM(PositiveResult) in a query.

但是，如果我想更进一步，将总计放在结果集的底部，该怎么办?

But what if I wanted to go a step further and place the totals in a row at the bottom of the result set:

这可以在数据级别实现还是表示层问题?如果可以通过SQL完成，该怎么办?我有点SQL新手.

Can this be achieved at the data level or is it a presentation layer issue? If it can be done by SQL, how might I do this? I am a bit of an SQL newbie.

感谢受访者.我现在将与客户核实.

Thanks to the respondents. I will now check things with the customer.

还可以添加文本列，以使摘要行中不显示最后一行数据的值吗?像这样:

Also, can a text column be added so that the value of the last row of data is not shown in the summary row? Like this:

推荐答案

我也将在表示层中执行此操作，但是您可以将其执行MySQL ...

I would also do this in the presentation layer, but you can do it MySQL...

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,pos DECIMAL(5,2)
,neg DECIMAL(5,2)
);

INSERT INTO my_table VALUES
(1,0,0),
(2,1,-2.5),
(3,1.6,-1),
(4,1,-2);


SELECT COALESCE(id,'total') my_id,SUM(pos),SUM(neg) FROM my_table GROUP BY id WITH ROLLUP;

+-------+----------+----------+
| my_id | SUM(pos) | SUM(neg) |
+-------+----------+----------+
|     1 |     0.00 |     0.00 |
|     2 |     1.00 |    -2.50 |
|     3 |     1.60 |    -1.00 |
|     4 |     1.00 |    -2.00 |
|  total|     3.60 |    -5.50 |
+-------+----------+----------+
5 rows in set (0.02 sec)

这里是修改后的问题的骇客-虽然不是很漂亮，但我认为它可以起作用...

Here's a hack for the amended problem - it ain't pretty but I think it works...

SELECT COALESCE(id,'') my_id
     , SUM(pos)
     , SUM(neg)
     , COALESCE(string,'') n
  FROM my_table 
 GROUP 
    BY id
     , string
  WITH ROLLUP
HAVING n <> '' OR my_id = ''
;

这篇关于SQL将摘要行添加到MySQL结果集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！