如何在SQL中实现Excel INDEX-MATCH等效功能并修正分组统计错误

问题分析 #

你的查询错误原因是直接对ShipFrom取MAX会返回字典序最大的值（如FB比FA字典序大），而非按ShipFromCount排序后的最高计数对应ShipFrom。要实现需求，需先按前缀分组并对计数排序，再筛选出每组的最高计数记录。

假设示例数据表 #

假设你的原始数据表为ShipRecords，结构如下：

统计后各ShipFrom的计数：FA=3，FB=2，KA=1，KB=2，预期输出F_MostFreq=FA、K_MostFreq=KB。

修正后的SQL查询 #

使用窗口函数ROW_NUMBER()按前缀分组，对计数降序排名，再提取每组排名第一的记录：

WITH ShipCounts AS (
  -- 第一步：统计每个ShipFrom的出现次数，并提取前缀
  SELECT
    ShipFrom,
    COUNT(*) AS ShipFromCount,
    LEFT(ShipFrom, 1) AS Prefix
  FROM ShipRecords
  WHERE LEFT(ShipFrom, 1) IN ('F', 'K') -- 仅筛选目标前缀
  GROUP BY ShipFrom
),
RankedShips AS (
  -- 第二步：按前缀分组，对计数降序排名（计数相同时按ShipFrom升序处理并列）
  SELECT
    ShipFrom,
    Prefix,
    ROW_NUMBER() OVER (
      PARTITION BY Prefix 
      ORDER BY ShipFromCount DESC, ShipFrom ASC
    ) AS rn
  FROM ShipCounts
)
-- 第三步：转置结果为指定格式
SELECT
  MAX(CASE WHEN Prefix = 'F' THEN ShipFrom END) AS F_MostFreq,
  MAX(CASE WHEN Prefix = 'K' THEN ShipFrom END) AS K_MostFreq
FROM RankedShips
WHERE rn = 1; -- 仅取每组排名第一的记录

关键说明 #

1. ShipCounts CTE：先统计每个ShipFrom的出现次数，同时提取前缀用于后续分组。
2. RankedShips CTE：使用ROW_NUMBER()为每个前缀组内的记录排序，确保计数最高的ShipFrom排名为1；若存在计数并列，可将ROW_NUMBER()替换为RANK()保留所有并列项，再用STRING_AGG(ShipFrom, ', ')汇总结果。
3. 最终查询：通过条件聚合将两个前缀的结果合并为一行，符合预期输出格式。

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