如何在SQL查询中为每个ItemID保留最长的ItemPath记录

你需要从返回的多条记录里，给每个ItemID保留对应的最长（最深嵌套）ItemPath，这里提供几种不同数据库的实现方案：

通用逻辑思路 #

先算出每个ItemID对应的路径长度最大值，再关联原表，匹配ItemID和对应长度的路径。

MySQL/MariaDB 写法 #

SELECT t.itemID, t.itemPath
FROM table_name t
INNER JOIN (
    SELECT itemID, MAX(LENGTH(itemPath)) AS max_len
    FROM table_name
    GROUP BY itemID
) t_max ON t.itemID = t_max.itemID AND LENGTH(t.itemPath) = t_max.max_len

如果同一个ItemID下有多个长度相同的最长路径（比如同一层级的不同分支），可以在SELECT后面加DISTINCT去重，或者根据实际业务加额外过滤条件。

SQL Server 写法 #

用LEN函数替代MySQL的LENGTH，逻辑完全一致：

SELECT t.itemID, t.itemPath
FROM table_name t
INNER JOIN (
    SELECT itemID, MAX(LEN(itemPath)) AS max_len
    FROM table_name
    GROUP BY itemID
) t_max ON t.itemID = t_max.itemID AND LEN(t.itemPath) = t_max.max_len

PostgreSQL 写法 #

用CHAR_LENGTH函数计算字符长度：

SELECT t.itemID, t.itemPath
FROM table_name t
INNER JOIN (
    SELECT itemID, MAX(CHAR_LENGTH(itemPath)) AS max_len
    FROM table_name
    GROUP BY itemID
) t_max ON t.itemID = t_max.itemID AND CHAR_LENGTH(t.itemPath) = t_max.max_len

更精准的层级判断方案（按分隔符数量） #

如果路径的层级是用/这类分隔符区分的，比如root/是1层、root/subroot/是2层，用字符长度判断可能不准（比如不同路径字符数相同但层级不同），可以统计分隔符的数量来确定层级：

以MySQL为例：

SELECT t.itemID, t.itemPath
FROM table_name t
INNER JOIN (
    SELECT itemID, MAX((LENGTH(itemPath) - LENGTH(REPLACE(itemPath, '/', '')))) AS max_level
    FROM table_name
    GROUP BY itemID
) t_max ON t.itemID = t_max.itemID 
AND (LENGTH(t.itemPath) - LENGTH(REPLACE(t.itemPath, '/', ''))) = t_max.max_level

其他数据库只需要替换对应的长度函数即可，核心逻辑是用总长度减去替换掉分隔符后的长度，得到分隔符的数量，也就是路径的层级数。

内容的提问来源于stack exchange，提问作者Phil Reynolds