MySQL WITH ROLLUP 没有显示我的预期 [英] MySQL WITH ROLLUP not showing what I expected

问题描述

我正在尝试将一些总数添加到我的 SELECT 查询中，但我正在努力了解为什么这不起作用:

I'm trying to add some totals to my SELECT query but I'm struggling to see why this isn't working:

SELECT 客户端、job_type、actual_value_fee来自 jo2detailsGROUP BY 客户端，job_type WITH ROLLUP

这是我期望看到的:

+--------+----------+------------------+
| client | job_type | actual_value_fee |
+--------+----------+------------------+
| 110    | 2        | 1250             |
| 110    | 20       | 200              |
| 110    |          | 1450             |
| 228    | 27       | 1000             |
| 228    |          | 1000             |
| 229    | 32       | 0                |
| 229    |          | 0                |
|        |          | 2450             |
+--------+----------+------------------+

但这是我得到的:

+--------+----------+------------------+
| client | job_type | actual_value_fee |
+--------+----------+------------------+
| 110    | 2        | 1250             |
| 110    | 20       | 200              |
| 110    |          | 200              |
| 228    | 27       | 1000             |
| 228    |          | 1000             |
| 229    | 32       | 0                |
| 229    |          | 0                |
|        |          | 0                |
+--------+----------+------------------+

这是表:

CREATE TABLE `jo2details` (
  `ref` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `ID` int(11) unsigned NOT NULL,
  `client` int(11) unsigned NOT NULL,
  `job_type` int(11) unsigned DEFAULT NULL,
  `actual_value_fee` decimal(11,2) DEFAULT NULL,
  `last_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`ref`),
  UNIQUE KEY `ref` (`ref`,`ID`),
  UNIQUE KEY `ref_2` (`ref`,`ID`,`client`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=413 ;

非常感谢所有帮助！

推荐答案

试试这个:

SELECT client, job_type, sum(actual_value_fee)
 FROM jo2details 
 GROUP BY client, job_type WITH ROLLUP

这篇关于MySQL WITH ROLLUP 没有显示我的预期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！