mysql - 在 ROLLUP 中用 SUBTOTAL 和 TOTAL 替换 NULL

Question

我曾尝试使用 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

Answer 1

要解决 COALESCE/IFNULL 仍然为 WITH ROLLUP 占位符返回 NULL 的问题，您需要 GROUP BY 表列名，而不是别名列表达式。

问题是由在别名列表达式上指定的 GROUP BY 子句引起的，因为别名是在列表达式之后 分配的已处理。
导致 WITH ROLLUP NULL 占位符不在要由 COALESCE 评估的记录集中。
这意味着 GROUP BY 中的别名 DEALER、SERVICE_ADVISOR 在 之前不存在IFNULL/COALESCE 已被执行。查看 MySQL Handling of GROUP BY了解更多详情。

示例 DB-Fiddle

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-Fiddle

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         |

---

注意事项

1. >

   With MySQL 5.7+ and ONLY_FULL_GROUP_BY enabled, selectednon-aggregate columns that are not specified in the GROUP BY clausewill fail. Meaning the following query will not work as expected: DB-Fiddle

   SELECT 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
   

2. >

   COALESCE,IFNULL,IF(... IS NULL)and CASE WHEN ... IS NULLwill all functionsimilarly. Where IFNULL is the proprietary to MySQL and is a lessfunctional replacement of COALESCE. As COALESCE can accept morethan two parameters to check against NULL, returning the first non-NULL value.

   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
   

3. >

   nullable columns in the GROUP BY as either aliases or column names, will result in the NULL values beingdisplayed as the WITH ROLLUP placeholder. This applies to using WITH ROLLUP in general. For example ifusers.name can return NULL. DB-Fiddle

   | DEALER | SERVICE_ADVISOR | COMPLETED | 
   | ------ | --------------- | --------- | 
   | Foo    | TOTAL           | 1         |
   | Foo    | Jane Doe        | 1         |
   | Foo    | John Doe        | 1         |
   | Foo    | TOTAL           | 3         |
   | GROUP  | TOTAL           | 3         |
   

---

防止显示 NULL 列值

为确保不会意外包含可为空的列，您需要在条件中指定排除它们。

示例 DB-Fiddle

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         |

Since LEFT JOIN is used on the vcrs table, IS NOT NULL must beapplied to the WHERE clause, instead of the ON clause. As LEFT JOIN returns NULL for non-matching criteria. To circumvent theissue, use an INNER JOIN to limit the resultset to only those withmatching ON criteria.

/* ... */
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 列值

要显式包含可为空的列值，而不复制 WITH ROLLUP 占位符名称，您需要使用派生表子查询将 NULL 值替换为文本值.

示例 DB-Fiddle

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;