时间戳

首页 > 解决方案 > 使用 HAVING 子句获取 ROLLUP Total

mysql - 使用 HAVING 子句获取 ROLLUP Total

问题描述

我正在将此 SQL 逻辑用于映射器函数，该函数将帮助网格显示有关在每个位置完成每次培训的人数的信息。

我在尝试获取使用 HAVING 子句过滤后返回的行数时遇到问题。我相信这是由于在 HAVING 之前运行了 WITH ROLLUP，但我不知道如何解决这个问题。
我的 SQL：

SELECT l.name AS Name, COUNT(IF(tst.id = 1, 1, NULL)) AS Training_1, COUNT(DISTINCT l.id) AS count 
FROM location_entity AS l
LEFT JOIN location_user AS lu 
    ON l.id = lu.location_entity_id
LEFT JOIN user_entity AS u 
    ON u.id = lu.user_entity_id
LEFT JOIN user_role AS r 
    ON r.id = u.user_role_id
LEFT JOIN 
    (SELECT ts.user_entity_id, ts.user_training_session_type_id 
        FROM user_training_session ts 
        GROUP BY ts.user_entity_id, ts.user_training_session_type_id
    ) AS ts 
    ON ts.user_entity_id = u.id
LEFT JOIN user_training_session_type AS tst 
    ON ts.user_training_session_type_id = tst.id 
GROUP BY l.name 
    WITH ROLLUP
HAVING (Training_1 >= 1)


当前结果：

+------------+------------+-------+
|    Name    | Training_1 | count |
+------------+------------+-------+
| Location_1 |          1 |     1 |
| Location_2 |          2 |     1 |
|            |          5 |    10 |
+------------+------------+-------+


期望的结果：

+------------+------------+-------+
|    Name    | Training_1 | count |
+------------+------------+-------+
| Location_1 |          1 |     1 |
| Location_2 |          2 |     1 |
|            |          3 |     2 |
+------------+------------+-------+

编辑： 1. 使用左连接重新编写 SQL。今天晚些时候将添加一个用于创建表和插入数据的脚本。

标签： mysqlsql

解决方案

尝试

SELECT ...
...
GROUP BY l.name, (tst.id = 1)
    WITH ROLLUP
HAVING (Training_1 >= 1)

推荐阅读