如何用SQL或PHP实现两列关键词去重合并并按频次排序分组？

嘿，我来帮你搞定这个下拉菜单的需求！你手里有两列逗号分隔的关键词数据，想要把相似活动归组或者按出现频次排序，对吧？下面我分别用SQL和PHP两种方案给你详细拆解，保证能直接用：

核心思路是先把两列里的逗号分隔字符串拆成单独的关键词行，合并后统计每个关键词的出现频次，最后按频次排序。不同数据库的字符串拆分函数略有不同，这里以常用的MySQL为例：

方法1：兼容MySQL 5.x及以上版本 #

如果你的MySQL版本比较老，没有JSON_TABLE函数，可以用数字序列拼接的方式拆分字符串：

SELECT keyword, COUNT(*) AS frequency
FROM (
    -- 拆分Hobbies列的关键词
    SELECT TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(h.Hobbies, ',', n.n), ',', -1)) AS keyword
    FROM your_table h
    JOIN (
        SELECT 1 n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5
    ) n ON CHAR_LENGTH(h.Hobbies) - CHAR_LENGTH(REPLACE(h.Hobbies, ',', '')) >= n.n - 1
    WHERE h.Hobbies IS NOT NULL AND h.Hobbies != ''
    
    UNION ALL
    
    -- 拆分Activities列的关键词
    SELECT TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(a.Activities, ',', n.n), ',', -1)) AS keyword
    FROM your_table a
    JOIN (
        SELECT 1 n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5
    ) n ON CHAR_LENGTH(a.Activities) - CHAR_LENGTH(REPLACE(a.Activities, ',', '')) >= n.n - 1
    WHERE a.Activities IS NOT NULL AND a.Activities != ''
) AS combined_keywords
GROUP BY keyword
ORDER BY frequency DESC, keyword ASC;

说明：

• 子查询n生成的数字序列要覆盖你数据中最多的关键词数量（比如示例里最多4个关键词，所以写到5足够），如果有更多关键词，继续加UNION ALL SELECT 6这类语句即可。
• TRIM用来去掉关键词前后的空格，避免出现" Gardening"和"Gardening"被当成不同关键词的情况。
• UNION ALL保留所有重复的关键词，这样COUNT(*)才能统计准确的出现频次。
• 最后按frequency DESC降序排序，相同频次的按关键词升序排列，方便下拉菜单展示。

方法2：MySQL 8.0+ 简洁版 #

如果你的MySQL是8.0及以上版本，可以用JSON_TABLE函数更灵活地拆分字符串，不用关心关键词数量：

SELECT keyword, COUNT(*) AS frequency
FROM (
    SELECT TRIM(j.keyword) AS keyword
    FROM your_table,
         JSON_TABLE(
             CONCAT('["', REPLACE(Hobbies, ',', '","'), '"]'),
             '$[*]' COLUMNS (keyword VARCHAR(255) PATH '$')
         ) j
    WHERE Hobbies IS NOT NULL AND Hobbies != ''
    
    UNION ALL
    
    SELECT TRIM(j.keyword) AS keyword
    FROM your_table,
         JSON_TABLE(
             CONCAT('["', REPLACE(Activities, ',', '","'), '"]'),
             '$[*]' COLUMNS (keyword VARCHAR(255) PATH '$')
         ) j
    WHERE Activities IS NOT NULL AND Activities != ''
) AS combined_keywords
GROUP BY keyword
ORDER BY frequency DESC, keyword ASC;

如果数据库拆分不太方便，或者你需要在代码里做更多自定义处理，可以用PHP来完成：

步骤1：从数据库获取数据并提取所有关键词 #

// 假设已经通过PDO连接到数据库，替换成你的数据库信息
$pdo = new PDO('mysql:host=localhost;dbname=your_database', 'your_username', 'your_password');
$stmt = $pdo->query("SELECT Hobbies, Activities FROM your_table");

$allKeywords = [];

while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    // 处理Hobbies列
    if (!empty($row['Hobbies'])) {
        $hobbies = explode(',', $row['Hobbies']);
        foreach ($hobbies as $hobby) {
            $keyword = trim($hobby);
            if (!empty($keyword)) {
                $allKeywords[] = $keyword;
            }
        }
    }
    
    // 处理Activities列
    if (!empty($row['Activities'])) {
        $activities = explode(',', $row['Activities']);
        foreach ($activities as $activity) {
            $keyword = trim($activity);
            if (!empty($keyword)) {
                $allKeywords[] = $keyword;
            }
        }
    }
}

步骤2：统计频次并排序 #

// 统计每个关键词的出现频次
$keywordCounts = array_count_values($allKeywords);

// 排序：先按频次降序，频次相同则按关键词升序
uasort($keywordCounts, function($a, $b) use ($keywordCounts) {
    if ($a === $b) {
        return strcmp(array_search($a, $keywordCounts), array_search($b, $keywordCounts));
    }
    return $b - $a;
});

步骤3：生成下拉菜单（带频次显示） #

echo '<select name="activity_selector">';
foreach ($keywordCounts as $keyword => $count) {
    // 转义关键词防止XSS攻击
    $safeKeyword = htmlspecialchars($keyword);
    echo "<option value=\"{$safeKeyword}\">{$safeKeyword} (出现{$count}次)</option>";
}
echo '</select>';

额外：按频次分组的下拉菜单（用optgroup） #

如果想要把相同频次的关键词归为一组，可以用HTML的optgroup标签：

// 按频次分组
$groupedByFreq = [];
foreach ($keywordCounts as $keyword => $count) {
    $groupedByFreq[$count][] = $keyword;
}

// 按频次降序排序分组
krsort($groupedByFreq);

// 生成带分组的下拉菜单
echo '<select name="activity_selector">';
foreach ($groupedByFreq as $freq => $keywords) {
    sort($keywords); // 组内关键词按字母升序排列
    echo "<optgroup label=\"出现{$freq}次\">";
    foreach ($keywords as $keyword) {
        $safeKeyword = htmlspecialchars($keyword);
        echo "<option value=\"{$safeKeyword}\">{$safeKeyword}</option>";
    }
    echo '</optgroup>';
}
echo '</select>';

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