MySQL-接收到具有Group By和Rollup的空值

Question

我有一个名为fund的表。它包含一个Id、source、received_date和value。请参阅下面的DDL、示例查询和相同的小提琴：

CREATE TABLE `fund` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`source` varchar(100) NOT NULL,
`value` decimal(8,2) NOT NULL,
`received_date` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
`created_by` varchar(5) NOT NULL,
`updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`modified_by` varchar(100) NOT NULL,
PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;
INSERT INTO `fund` (`source`, `value`, `received_date`, `created_at`, `created_by`, `updated_at`, `modified_by`) 
VALUES 
('rsp - hibah', 5000.00, '2019-01-03', CURRENT_TIMESTAMP, 'admin', CURRENT_TIMESTAMP, 'admin'),
('rsp - takaful', 7500.00, '2019-01-03', CURRENT_TIMESTAMP, 'admin', CURRENT_TIMESTAMP, 'admin'),
('rsp - takaful', 100000.00, '2019-01-30', CURRENT_TIMESTAMP, 'admin', CURRENT_TIMESTAMP, 'admin'),
('rsp - hibah', 1200.00, '2019-05-07', CURRENT_TIMESTAMP, 'admin', CURRENT_TIMESTAMP, 'admin'),
('rsp - hibah', 5022.00, '2019-05-23', CURRENT_TIMESTAMP, 'admin', CURRENT_TIMESTAMP, 'admin'),
('rsp - takaful', 2000.00, '2019-11-21', CURRENT_TIMESTAMP, 'admin', CURRENT_TIMESTAMP, 'admin')
SELECT
CONCAT('Additional Amount ') AS NAME,
date_format( received_date, '%Y-%m-%d') AS m_date,
( CASE WHEN source = 'rsp - hibah' THEN SUM(value) ELSE NULL END ) AS total_hibah,
( CASE WHEN source = 'rsp - takaful' THEN SUM(value) ELSE NULL END ) AS total_takaful,
SUM(value) AS total_contribution
FROM
fund 
WHERE
source LIKE 'rsp -%' 
GROUP BY
YEAR(m_date),
MONTH(m_date),
source
UNION ALL
SELECT
CONCAT( 'Balance ', source, ' ' ) AS NAME,
-- ADDTIME( LAST_DAY( date_format( received_date, '%Y-%m-%d' )), 2 ) AS m_date,
LAST_DAY( date_format( received_date, '%Y-%m-%d' )) AS m_date,
( CASE WHEN source = 'rsp - hibah' THEN @sum_hibah := @sum_hibah + SUM( VALUE ) ELSE NULL END ) AS total_hibah,
( CASE WHEN source = 'rsp - takaful' THEN @sum_takaful := @sum_takaful + SUM( VALUE ) ELSE NULL END ) AS total_takaful,
NULL AS total_contribution 
FROM
fund,
(SELECT @sum_takaful := 0) AS col_takaful,
(SELECT @sum_hibah := 0) AS col_hibah 
WHERE
source LIKE 'rsp -%' 
GROUP BY
YEAR (m_date),
MONTH (m_date),
source
WITH ROLLUP 
UNION ALL
SELECT
CONCAT('Total') AS NAME,
LAST_DAY( date_format( received_date, '%Y-%m-%d' )) AS m_date,
NULL AS total_hibah,
NULL AS total_takaful, 
(@sum_contribution := SUM(value) + @sum_contribution) AS total_contribution
FROM
fund,
(SELECT @sum_contribution:=0) AS col_contribution
WHERE
source LIKE 'rsp -%' 
GROUP BY
YEAR(received_date),
MONTH(received_date)
WITH ROLLUP
ORDER BY
m_date, total_hibah, total_takaful, total_contribution 

http://sqlfiddle.com/#！9/1fb70e/23/0

---

问题

为什么表上有一个没有值的空行？

有没有办法防止这种情况或我的查询不正确。请告知

结果

+-----------------------+------------+-------------+---------------+--------------------+
| ME                    | m_date     | total_hibah | total_takaful | total_contribution |
+-----------------------+------------+-------------+---------------+--------------------+
| Additional Amount     | 2019-01-03 | (null)      | 107500        | 107500             |
| Additional Amount     | 2019-01-03 | 5000        | (null)        | 5000               |
| (null)                | 2019-01-31 | (null)      | (null)        | (null)             | //empty row
| Total                 | 2019-01-31 | (null)      | (null)        | 112500             |
| Balance rsp - takaful | 2019-01-31 | (null)      | 107500        | (null)             |
| Balance rsp - hibah   | 2019-01-31 | 5000        | (null)        | (null)             |
| Additional Amount     | 2019-05-07 | 6222        | (null)        | 6222               |
| (null)                | 2019-05-31 | (null)      | (null)        | (null)             | //empty row
| Total                 | 2019-05-31 | (null)      | (null)        | 118722             |
| Balance rsp - hibah   | 2019-05-31 | 11222       | (null)        | (null)             |
| Additional Amount     | 2019-11-21 | (null)      | 2000          | 2000               |
| (null)                | 2019-11-30 | (null)      | (null)        | (null)             | //empty row
| (null)                | 2019-11-30 | (null)      | (null)        | (null)             | //empty row
| (null)                | 2019-11-30 | (null)      | (null)        | (null)             | //empty row
| Total                 | 2019-11-30 | (null)      | (null)        | 120722             |
| Total                 | 2019-11-30 | (null)      | (null)        | 239444             |
| Total                 | 2019-11-30 | (null)      | (null)        | 239444             |
| Balance rsp - takaful | 2019-11-30 | (null)      | 109500        | (null)             |
+-----------------------+------------+-------------+---------------+--------------------+

Answer 1

我想这条注释有点长。但在第二个查询中使用ROLLUP导致了这个问题。你只需要从那里删除ROLLUP子句-

GROUP BY
YEAR (m_date),
MONTH (m_date),
source
WITH ROLLUP  --  Here is the issue.

这是最新的小提琴。