mysql - MySQL - 通过 Group By 和 Rollup 收到空值
问题描述
我有一张桌子叫fund
. 它包含一个Id
、source
和。请参阅下面的 DDL、示例查询和相同的小提琴:received_date
value
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) |
+-----------------------+------------+-------------+---------------+--------------------+
解决方案
我想这有点长评论。但是在第二个查询中使用 ROLLUP 会导致问题。您只需要从那里删除 ROLLUP 子句 -
GROUP BY
YEAR (m_date),
MONTH (m_date),
source
WITH ROLLUP -- Here is the issue.
这是更新的小提琴。
推荐阅读
- node.js - 方法 .save() 在 adonis/node.js 中插入而不是更新我的模型
- javascript - 当翻译在另一个集合中时,如何在 MongoDB 上处理 i18n(带回退)?
- twig - Twig:如何防止在 GET 参数中编码方括号
- python - python中的正则表达式,需要从字符串中打印网站名称
- vba - 如何在 Word 中的 VBA 中的一个函数中搜索多个单词?
- python - 在 if/then 语句中使用错误作为条件
- javascript - Electron 应用程序显示白屏但在 CentOS 8 上呈现
- python - 如何在熊猫中删除十进制数字
- sql - 表别名到子查询
- android - 使用Android在Kotlin的try catch块中获取值而不是单位