如何编写Microsoft Access SQL按GROUP_ID分组保留最小Distance的相似PID记录？

现有Bolt_Table数据表 #

字段包含：PID、UNIQ ID、GROUP_ID、Distance，具体数据如下：

期望结果 #

按GROUP_ID分组，在每组的相似PID组（指PID前缀相同，如PID_5_开头的为一组）中，保留对应Distance最小的记录；仅保留同一GROUP_ID下存在多个相似PID的组的结果，具体如下：

已尝试的代码 #

仅能硬编码单个GROUP_ID实现需求，代码如下：

SELECT TOP 1 PERCENT PID, UNIQ_ID, GROUP_ID, Distance
FROM
(
SELECT
a.PID, a.UNIQ_ID, a.GROUP_ID, ID, a.Distance,
(select count(PID) as counter from Bolt_Table where GROUP_ID = a.GROUP_ID and LEFT(PID, 9) = LEFT(a.PID, 9)) as counter from Bolt_Table a WHERE a.GROUP_ID = 13
)
where counter > 1 
order by Distance

解决方案 #

要实现对所有GROUP_ID生效的逻辑，对应的Microsoft Access SQL语句如下：

SELECT b.PID, b.[UNIQ ID] AS UNIQ_ID, b.GROUP_ID, b.Distance
FROM Bolt_Table b
INNER JOIN (
    -- 按GROUP_ID和PID前缀分组，筛选出有多个相似PID的组，并计算每组最小Distance
    SELECT 
        GROUP_ID, 
        LEFT(PID, 5) AS PID_Prefix, -- 按PID前缀分组，此处取前5位适配`PID_x_`格式，可按需调整
        MIN(Distance) AS Min_Distance,
        COUNT(*) AS PID_Count
    FROM Bolt_Table
    GROUP BY GROUP_ID, LEFT(PID, 5)
    HAVING COUNT(*) > 1 -- 仅保留同一GROUP_ID下该前缀有多个记录的组
) AS grp 
ON b.GROUP_ID = grp.GROUP_ID 
   AND LEFT(b.PID, 5) = grp.PID_Prefix 
   AND b.Distance = grp.Min_Distance;

说明 #

• LEFT(PID, 5)：根据PID格式提取相似判断的前缀，若你的相似规则是前9位，直接修改数字即可。
• 子查询grp先完成分组统计，筛选出符合条件的组并计算最小Distance，主查询再关联原表获取完整的目标记录。

内容的提问来源于stack exchange，提问作者Jose Jaime Felix Garcia