我尝试使用 IFNULL 替换 ROLLUP 为小计和总计返回的 NULL 字段,但它似乎不起作用。
查询:
select IFNULL(usergroups.name, 'GROUP') AS DEALER,
IFNULL(users.name, 'TOTAL') AS SERVICE_ADVISOR,
COUNT(DISTINCT vcrs.uid) AS COMPLETED,
..........
..........
and vcrs.vcrSubStatus = 4
group by DEALER, SERVICE_ADVISOR with ROLLUP;
输出:
DEALER SERVICE_ADVISOR COMPLETED IDENTIFIED AUTHORISED
Aston Martin Chris 3 664.56 0
Aston Martin Graham 6 0 0
Aston Martin (NULL) 15 664.56 0
Bentley Sukraj 1 0 0
Bentley Terry 4 0 0
Bentley (NULL) 5 0 0
Jaguar Emma 10 2448 1224
Jaguar Paul 1 0 0
Jaguar Simon 7 2754 918
Jaguar (NULL) 18 5202 2142
(NULL) (NULL) 2611 96591.62 42130.14
期望输出:
DEALER SERVICE_ADVISOR COMPLETED IDENTIFIED AUTHORISED
Aston Martin Chris 3 664.56 0
Aston Martin Graham 6 0 0
Aston Martin TOTAL 15 664.56 0
Bentley Sukraj 1 0 0
Bentley Terry 4 0 0
Bentley TOTAL 5 0 0
Jaguar Emma 10 2448 1224
Jaguar Paul 1 0 0
Jaguar Simon 7 2754 918
Jaguar TOTAL 18 5202 2142
GROUP TOTAL 2611 96591.62 42130.14
若要解决COALESCE/IFNULL
仍返回WITH ROLLUP
占位符的NULL
的问题,需要GROUP BY
表列名称,而不是别名列表达式。
此问题是由在别名列表达式上指定的 GROUP BY
子句引起的,因为别名是在处理列表达式之后分配的。
导致WITH ROLLUP
NULL
占位符不在记录集中,由 COALESCE
评估。
这意味着GROUP BY
中的别名DEALER
、SERVICE_ADVISOR
直到IFNULL/COALESCE
被执行之后才存在。有关更多详细信息,请参阅 MySQL 处理GROUP BY
。
示例 DB-小提琴
CREATE TABLE foo (
`amount` INTEGER,
`created` INTEGER
);
INSERT INTO foo
(`amount`, `created`)
VALUES
('1', '2019'),
('2', '2019');
查询 #1(重现问题(
SELECT
SUM(amount) AS amounts,
COALESCE(created, 'Total') AS created_coalesce
FROM foo
GROUP BY created_coalesce WITH ROLLUP;
| amounts | created_coalesce |
| ------- | ---------------- |
| 3 | 2019 |
| 3 | |
查询 #2(已更正(
SELECT
SUM(amount) AS amounts,
COALESCE(created, 'Total') AS created_coalesce
FROM foo
GROUP BY foo.created WITH ROLLUP;
| amounts | created_coalesce |
| ------- | ---------------- |
| 3 | 2019 |
| 3 | Total |
<小时 />特定于用例
示例 DB-小提琴
SELECT
COALESCE(usergroups.name, 'GROUP') AS DEALER,
COALESCE(users.name, 'TOTAL') AS SERVICE_ADVISOR,
COUNT(DISTINCT vcrs.uid) AS COMPLETED,
/* ... */
GROUP BY usergroups.name, users.name WITH ROLLUP;
查询 #1(原始(
SELECT
COALESCE(usergroups.name, 'GROUP') AS DEALER,
COALESCE(users.name, 'TOTAL') AS SERVICE_ADVISOR,
COUNT(DISTINCT vcrs.uid) AS COMPLETED
/* ... */
GROUP BY DEALER, SERVICE_ADVISOR WITH ROLLUP;
| DEALER | SERVICE_ADVISOR | COMPLETED |
| ------ | --------------- | --------- |
| Foo | Jane Doe | 1 |
| Foo | John Doe | 1 |
| Foo | | 2 |
| | | 2 |
查询 #2(已更正(
SELECT
COALESCE(usergroups.name, 'GROUP') AS DEALER,
COALESCE(users.name, 'TOTAL') AS SERVICE_ADVISOR,
COUNT(DISTINCT vcrs.uid) AS COMPLETED
/* ... */
GROUP BY usergroups.name, users.name WITH ROLLUP;
| DEALER | SERVICE_ADVISOR | COMPLETED |
| ------ | --------------- | --------- |
| Foo | Jane Doe | 1 |
| Foo | John Doe | 1 |
| Foo | TOTAL | 2 |
| GROUP | TOTAL | 2 |
<小时 />考虑
启用 MySQL 5.7+ 和ONLY_FULL_GROUP_BY后,选择未在
GROUP BY
子句中指定的非聚合列将失败。 这意味着以下查询将无法按预期工作:DB-FiddleSELECT COALESCE(YEAR(foo), 'foo') /* ... */ GROUP BY YEAR(foo) WITH ROLLUP -> ER_WRONG_FIELD_WITH_GROUP: Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.foo_bar.foo' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
COALESCE
,IFNULL
,IF(... IS NULL)
和CASE WHEN ... IS NULL
将全部功能同样地。其中IFNULL
是MySQL的专有产品,并且较少COALESCE
的功能替代 .因为COALESCE
可以接受更多两个参数来检查NULL
,返回第一个non-NULL
值。mysql> SELECT COALESCE(NULL, NULL, 1, NULL); -> 1 mysql> SELECT IFNULL(NULL, 1); -> 1 mysql> SELECT IF(NULL IS NULL, 1, ''); -> 1 mysql> SELECT CASE WHEN NULL IS NULL THEN 1 END; -> 1
GROUP BY
中作为别名或列名的可为 null 的列将导致NULL
值为显示为WITH ROLLUP
占位符。这通常适用于使用WITH ROLLUP
。例如,如果users.name
可以返回NULL
.DB-小提琴| DEALER | SERVICE_ADVISOR | COMPLETED | | ------ | --------------- | --------- | | Foo | TOTAL | 1 | | Foo | Jane Doe | 1 | | Foo | John Doe | 1 | | Foo | TOTAL | 3 | | GROUP | TOTAL | 3 |
防止显示NULL
列值
若要确保不会意外包含可为 null 的列,需要在条件中指定以排除它们。
示例 DB-小提琴
SELECT
COALESCE(usergroups.name, 'GROUP') AS DEALER,
COALESCE(users.name, 'TOTAL') AS SERVICE_ADVISOR,
COUNT(DISTINCT vcrs.uid) AS COMPLETED
FROM vcrs
LEFT JOIN users
ON users.id = vcrs.uid
LEFT JOIN usergroups
ON usergroups.id = users.group_id
WHERE vcrs.vcrSubStatus = 4
AND users.name IS NOT NULL
GROUP BY usergroups.name, users.name
WITH ROLLUP;
结果
| DEALER | SERVICE_ADVISOR | COMPLETED |
| ------ | --------------- | --------- |
| Foo | Jane Doe | 1 |
| Foo | John Doe | 1 |
| Foo | TOTAL | 2 |
| GROUP | TOTAL | 2 |
由于
LEFT JOIN
用于vcrs
表,因此IS NOT NULL
必须适用于WHERE
子句,而不是ON
子句。AsLEFT JOIN
返回不符合条件的NULL
。为了规避问题,使用INNER JOIN
将结果集限制为仅具有符合ON
标准。
/* ... */
INNER JOIN users
ON users.id = vcrs.uid
AND users.name IS NOT NULL
/* ... */
WHERE vcrs.vcrSubStatus = 4
GROUP BY usergroups.name, users.name
WITH ROLLUP;
<小时 />包括NULL
列值
若要显式包含可为 null 的列值,而不复制WITH ROLLUP
占位符名称,需要利用派生表子查询将NULL
值替换为文本值。
示例 DB-小提琴
SELECT
COALESCE(v.usergroup_name, 'GROUP') AS DEALER,
COALESCE(v.user_name, 'TOTAL') AS SERVICE_ADVISOR,
COUNT(DISTINCT v.uid) AS COMPLETED
FROM (
SELECT
usergroups.name AS usergroup_name,
COALESCE(users.name, 'NULL') AS user_name,
vcrs.uid
FROM vcrs
LEFT JOIN users
ON users.id = vcrs.uid
LEFT JOIN usergroups
ON usergroups.id = users.group_id
WHERE vcrs.vcrSubStatus = 4
) AS v
GROUP BY v.usergroup_name, v.user_name
WITH ROLLUP;
结果
| DEALER | SERVICE_ADVISOR | COMPLETED |
| ------ | --------------- | --------- |
| Foo | Jane Doe | 1 |
| Foo | John Doe | 1 |
| Foo | NULL | 1 |
| Foo | TOTAL | 3 |
| GROUP | TOTAL | 3 |
<小时 />您还可以根据需要选择替换'NULL'
文本占位符,甚至将其显示为NULL
。
SELECT
COALESCE(v.usergroup_name, 'GROUP') AS DEALER,
CASE v.user_name WHEN 'NULL' THEN NULL ELSE COALESCE(v.user_name, 'TOTAL') END AS SERVICE_ADVISOR,
COUNT(DISTINCT v.uid) AS COMPLETED
FROM (
/* ... */
) AS v
GROUP BY v.usergroup_name, v.user_name
WITH ROLLUP;
我只晚了 2 年,但由于我遇到了与@the_gimlet相同的问题,我想我会发布答案。
所以不知道这是 mySQL 版本控制还是什么,但是使用 mysql 5.6 我遇到了同样的问题......ifnull 不会替换汇总"null"。
只需通过将汇总设置为子查询并在主选择中执行 ifnull 来解决此问题...重复选择很烦人,但它有效!
例如,例如上面
SELECT
IFNULL(`DEALER`, 'GROUP') AS DEALER,
IFNULL(`SERVICE_ADVISOR`, 'TOTAL') AS SERVICE_ADVISOR,
`COMPLETED`,
/* .......... */
FROM (SELECT
usergroups.name AS DEALER,
users.name AS SERVICE_ADVISOR,
COUNT(DISTINCT vcrs.uid) AS COMPLETED,
/* .......... */
AND vcrs.vcrSubStatus = 4
GROUP BY DEALER, SERVICE_ADVISOR with ROLLUP);
你想要这样的东西吗?
SELECT COALESCE(usergroups.name, 'GROUP') AS DEALER,
COALESCE(users.name, IF(usergroups.name IS NULL, 'TOTAL', 'SUBTOTAL')) AS SERVICE_ADVISOR,
COUNT(DISTINCT vcrs.uid) AS COMPLETED,
..........
..........
AND vcrs.vcrSubStatus = 4
GROUP BY DEALER, SERVICE_ADVISOR with ROLLUP;
测试:
mysql;root@localhost(playground)> select * from t;
+------+----------+-------+--------+
| id | car | state | tstamp |
+------+----------+-------+--------+
| 1 | toyota | new | 1900 |
| 2 | toyota | old | 1950 |
| 3 | toyota | scrap | 1980 |
| 4 | mercedes | new | 1990 |
| 5 | mercedes | old | 2010 |
| 6 | tesla | new | 2013 |
+------+----------+-------+--------+
6 rows in set (0.04 sec)
mysql;root@localhost(playground)> select car, sum(tstamp) from t group by car with rollup;
+----------+-------------+
| car | sum(tstamp) |
+----------+-------------+
| mercedes | 4000 |
| tesla | 2013 |
| toyota | 5830 |
| NULL | 11843 |
+----------+-------------+
4 rows in set (0.03 sec)
mysql;root@localhost(playground)> select coalesce(car, 'huhu'), sum(tstamp) from t group by car with rollup;
+-----------------------+-------------+
| coalesce(car, 'huhu') | sum(tstamp) |
+-----------------------+-------------+
| mercedes | 4000 |
| tesla | 2013 |
| toyota | 5830 |
| huhu | 11843 |
+-----------------------+-------------+
4 rows in set (0.00 sec)
你要找的是一个案例陈述。你的意思是,在某种条件下,将找到的值替换为指定的值。您可以使用多个 when/then 语句,具体取决于您希望替换的自定义程度。
select IFNULL(usergroups.name, 'GROUP') AS DEALER,
case when(users.name is null) then 'TOTAL' else users.name end AS SERVICE_ADVISOR,
COUNT(DISTINCT vcrs.uid) AS COMPLETED,
..........
..........
and vcrs.vcrSubStatus = 4
group by DEALER, SERVICE_ADVISOR with ROLLUP;