MSSQL技术需求：按规则获取各areaid出现次数最多的rank值

先明确你的需求和规则，直接上可执行的SQL方案，再拆解逻辑：

问题回顾 #

原始数据 #

areaid rank
1 Positive
1 Neutral
1 Positive
1 Positive
1 Positive
1 Negative
2 Positive 2
2 Positive 1
1 Positive
1 Positive 1
2 Negative
2 Positive

期望输出 #

areaid rank
1 positive
2 positive

核心规则 #

• 每个areaid取出现次数最多的rank值
• 次数相同时，优先级：Negative > Neutral > Positive
• 特殊情况：若Positive与Negative次数相等且都多于Neutral，取Negative（这条已被优先级规则覆盖）

SQL实现（通用窗口函数写法，支持MySQL 8+/PostgreSQL/SQL Server等） #

-- 步骤1：清洗rank字段，统一格式（去掉多余数字、转小写）
WITH cleaned_rank_data AS (
    SELECT 
        areaid,
        -- 去除rank中的数字，转小写，确保格式统一
        LOWER(TRIM(REGEXP_REPLACE(rank, '[0-9]', ''))) AS clean_rank
    FROM your_table_name -- 替换成你的实际表名
),
-- 步骤2：统计每个区域各rank的出现次数，并分配优先级分数
rank_statistics AS (
    SELECT 
        areaid,
        clean_rank,
        COUNT(*) AS occurrence_count,
        -- 按规则分配优先级：Negative(3) > Neutral(2) > Positive(1)
        CASE clean_rank
            WHEN 'negative' THEN 3
            WHEN 'neutral' THEN 2
            WHEN 'positive' THEN 1
        END AS priority_score
    FROM cleaned_rank_data
    GROUP BY areaid, clean_rank
),
-- 步骤3：对每个区域的rank按次数+优先级排序，取Top1
ranked_results AS (
    SELECT 
        areaid,
        clean_rank AS rank,
        ROW_NUMBER() OVER (
            PARTITION BY areaid 
            ORDER BY occurrence_count DESC, priority_score DESC
        ) AS row_num
    FROM rank_statistics
)
-- 最终取每个区域的第一条记录
SELECT areaid, rank
FROM ranked_results
WHERE row_num = 1
ORDER BY areaid;

逻辑拆解 #

1. 数据清洗：处理原始数据中Positive 1、Positive 2这类带数字的rank值，统一转换成小写的纯文本格式，避免统计偏差。
2. 统计与优先级赋值：按区域和清洗后的rank分组计数，同时给每个rank分配优先级分数，确保次数相同时Negative优先被选中。
3. 窗口函数排序取Top：用ROW_NUMBER()窗口函数对每个区域内的rank按「次数降序→优先级降序」排序，取排名第一的就是符合要求的结果。

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