MariaDB/MySQL中使用MAX()函数进行三表关联查询时结果被截断的问题排查

首先先明确你的表结构（方便参考）：

-- nodes表
DESCRIBE nodes;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(256) | NO   |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+

-- attribs表
DESCRIBE attribs;
+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | int(11)      | NO   | PRI | NULL    | auto_increment |
| name       | varchar(256) | NO   |     | NULL    |                |
| persistent | int(11)      | YES  |     | 0       |                |
| parent     | varchar(256) | YES  |     | NODE    |                |
+------------+--------------+------+-----+---------+----------------+

-- entries表
DESCRIBE entries;
+-----------+--------------+------+-----+---------------------+----------------+
| Field     | Type         | Null | Key | Default             | Extra          |
+-----------+--------------+------+-----+---------------------+----------------+
| id        | int(11)      | NO   | PRI | NULL                | auto_increment |
| node_id   | int(11)      | NO   | MUL | NULL                |                |
| attrib_id | int(11)      | NO   | MUL | NULL                |                |
| value     | varchar(256) | NO   |     | NULL                |                |
| ts        | timestamp    | NO   |     | current_timestamp() |                |
+-----------+--------------+------+-----+---------------------+----------------+

问题根源分析 #

你最初用MAX(CASE ...)做行转列时，MAX()函数的特性是只返回分组内的单个最大值（字符串类型按字典序比较），但你的场景中单个节点对应多条IP_LONG记录，所以它只会留下其中一条，导致其他值丢失。而去掉MAX()和GROUP BY后，虽然能看到所有记录，但会产生大量NULL值，格式混乱。

解决方案：用GROUP_CONCAT()拼接多值 #

我们可以用GROUP_CONCAT()替代MAX()来处理IP_LONG字段，它会把同一节点下所有符合条件的IP_LONG值拼接成一个字符串，同时保留其他字段的聚合逻辑。调整后的查询如下：

SELECT 
    nodes.id AS NODE_ID, 
    nodes.name AS NODE, 
    -- 拼接所有IP_LONG值，用分号分隔
    GROUP_CONCAT(CASE WHEN attribs.name = 'IP_LONG' THEN value END SEPARATOR '; ') AS IP_LONG,
    -- 对于IP和LOCATION，若单个节点只有一个有效值，MAX()能正确提取非NULL值
    MAX(CASE WHEN attribs.name = 'IP' THEN value END) AS IP,
    MAX(CASE WHEN attribs.name = 'LOCATION' THEN value END) AS LOCATION 
FROM entries 
LEFT JOIN nodes ON nodes.id = entries.node_id 
LEFT JOIN attribs ON attribs.id = entries.attrib_id 
WHERE entries.ts > DATE_SUB(NOW(), INTERVAL 1 DAY) 
-- 严格模式下需分组所有非聚合列，这里加入nodes.id更严谨
GROUP BY nodes.id, nodes.name 
ORDER BY nodes.id;

额外优化选项 #

如果你的IP_LONG存在重复值，可以加上DISTINCT去重；如果需要按时间顺序拼接记录，还能加入排序：

GROUP_CONCAT(
    DISTINCT CASE WHEN attribs.name = 'IP_LONG' THEN value END 
    ORDER BY entries.ts 
    SEPARATOR '; '
) AS IP_LONG

关于单独提取IP_LONG的查询整合 #

你提到的那个能正确获取所有IP_LONG的查询，本质上就是GROUP_CONCAT的基础场景，把它整合到行转列查询里就是上面的方案，不需要单独拆分处理。

内容的提问来源于stack exchange，提问作者Lucifers Friend