如何统计数据库tileused列中各瓷砖名称的出现次数并降序排列

嘿，这个需求很常见——把逗号分隔的列拆成独立条目再统计出现次数对吧？我给你分几种主流数据库写好可直接运行的SQL方案，顺便提几个关键注意点：

MySQL 方案 #

如果你的MySQL版本是8.0及以上，推荐用JSON_TABLE的写法，简洁高效：

SELECT tile_name, COUNT(*) AS occurrence_count
FROM your_table,
     JSON_TABLE(
         CONCAT('["', REPLACE(tileused, ',', '","'), '"]'),
         '$[*]' COLUMNS(tile_name VARCHAR(255) PATH '$')
     ) AS tiles
WHERE tileused IS NOT NULL AND tileused != ''
GROUP BY tile_name
ORDER BY occurrence_count DESC;

要是用的是更早的MySQL版本，递归CTE也能搞定：

WITH RECURSIVE split_tiles AS (
    SELECT 
        TRIM(SUBSTRING_INDEX(tileused, ',', 1)) AS tile_name,
        SUBSTRING(tileused, LENGTH(SUBSTRING_INDEX(tileused, ',', 1)) + 2) AS remaining_tiles
    FROM your_table
    WHERE tileused IS NOT NULL AND tileused != ''
    UNION ALL
    SELECT 
        TRIM(SUBSTRING_INDEX(remaining_tiles, ',', 1)) AS tile_name,
        SUBSTRING(remaining_tiles, LENGTH(SUBSTRING_INDEX(remaining_tiles, ',', 1)) + 2) AS remaining_tiles
    FROM split_tiles
    WHERE remaining_tiles IS NOT NULL AND remaining_tiles != ''
)
SELECT tile_name, COUNT(*) AS occurrence_count
FROM split_tiles
GROUP BY tile_name
ORDER BY occurrence_count DESC;

PostgreSQL 方案 #

PostgreSQL处理字符串拆分简直是天生强项，用string_to_array+unnest一步到位：

SELECT 
    TRIM(unnest(string_to_array(tileused, ','))) AS tile_name,
    COUNT(*) AS occurrence_count
FROM your_table
WHERE tileused IS NOT NULL AND tileused != ''
GROUP BY tile_name
ORDER BY occurrence_count DESC;

嫌麻烦的话，regexp_split_to_table也能达到同样效果，写法更直观：

SELECT 
    TRIM(regexp_split_to_table(tileused, ',')) AS tile_name,
    COUNT(*) AS occurrence_count
FROM your_table
WHERE tileused IS NOT NULL AND tileused != ''
GROUP BY tile_name
ORDER BY occurrence_count DESC;

SQL Server 方案 #

SQL Server 2016及以上版本自带STRING_SPLIT函数，直接用就行：

SELECT 
    TRIM(value) AS tile_name,
    COUNT(*) AS occurrence_count
FROM your_table
CROSS APPLY STRING_SPLIT(tileused, ',')
WHERE tileused IS NOT NULL AND tileused != ''
GROUP BY TRIM(value)
ORDER BY occurrence_count DESC;

重要注意事项 #

• 记得把SQL里的your_table替换成你实际的表名
• 我在所有方案里都加了TRIM()函数，防止瓷砖名前后带空格导致统计重复（比如"Antarca White..."和" Antarca White..."会被当成不同条目）
• WHERE条件过滤了NULL和空字符串的行，避免这些无效数据干扰统计结果

内容的提问来源于stack exchange，提问作者Shubham Maheshwari