SQL中如何展示唯一行与排名？含百分比排名及零值处理需求

咱们先来拆解你的核心需求，确保每个点都覆盖到：

• 用INNER JOIN关联数据
• 按(已交付总数/目标值)*100的达成率降序排名，最高的排第一
• 当目标值或已交付数为0时，该记录直接排最后
• 最终结果展示唯一行（每个主体只出现一次）

关键问题处理思路 #

1. 零值的特殊处理

直接计算的话，目标值为0会触发除零错误，而且按照需求，只要目标值或已交付数任一为0，这条记录就得排最后。咱们可以用CASE WHEN把这类情况的达成率强制设为0（或者极小负数，确保绝对排在最后）：

CASE 
  WHEN TargetValue = 0 OR DeliveredCount = 0 THEN 0
  ELSE (DeliveredCount * 100.0 / TargetValue) 
END AS AchievementRate

这里用100.0而非100是为了避免整数除法陷阱——比如5/2用整数除法会得到2，但用100.0就能得到250.0的正确百分比。

2. 确保唯一行

如果INNER JOIN后出现重复行，大概率是关联键不唯一，或者需要先聚合数据。比如交付表按日期记录的话，得先按主体分组统计总交付数，再和目标表关联：

-- 先聚合交付表的总交付数
WITH AggregatedDeliveries AS (
  SELECT 
    ProjectID,
    SUM(DeliveredQty) AS DeliveredCount
  FROM Deliveries
  GROUP BY ProjectID
)
SELECT 
  t.ProjectID,
  t.ProjectName,
  ad.DeliveredCount,
  t.TargetValue,
  -- 计算达成率
  CASE 
    WHEN t.TargetValue = 0 OR ad.DeliveredCount = 0 THEN 0
    ELSE (ad.DeliveredCount * 100.0 / t.TargetValue) 
  END AS AchievementRate,
  -- 生成排名
  RANK() OVER(ORDER BY 
    CASE 
      WHEN t.TargetValue = 0 OR ad.DeliveredCount = 0 THEN 0
      ELSE (ad.DeliveredCount * 100.0 / t.TargetValue) 
    END DESC
  ) AS RankNum
FROM Targets t
INNER JOIN AggregatedDeliveries ad 
  ON t.ProjectID = ad.ProjectID;

3. 排名函数的选择

• RANK()：相同达成率的记录会得到相同排名，后续排名会跳过（比如两个第1，下一个是第3）
• DENSE_RANK()：相同达成率的记录排名相同，后续排名不跳过（两个第1，下一个是第2）
• ROW_NUMBER()：即使达成率相同，每个记录的排名也唯一（适合需要绝对唯一排名的场景）
  根据你的“展示唯一行”需求，只要JOIN后的数据集已经是唯一的，选最贴合业务逻辑的函数即可。

常见错误排查（针对你之前结果不符合预期的情况） #

• 整数除法问题：没加100.0的话，比如写(DeliveredCount * 100 / TargetValue)，当交付数小于目标值时，结果会被截断为0，导致排名完全错误
• 零值未处理：没加CASE WHEN的话，目标值为0会抛出除零异常，或者得到NULL，虽然NULL默认排最后，但不符合需求的明确逻辑
• 排序方向反了：如果ORDER BY用了ASC而非DESC，会导致达成率最低的排第一
• JOIN后重复行：如果交付表没做聚合，JOIN后会出现多条同主体的记录，需要先分组聚合或者加DISTINCT

完整示例（假设表结构） #

假设你有两个表：

• Targets：存储目标值，字段ProjectID, ProjectName, TargetValue
• Deliveries：存储交付记录，字段ProjectID, DeliveredQty, DeliveryDate

完整查询如下：

WITH AggregatedDeliveries AS (
  SELECT 
    ProjectID,
    SUM(DeliveredQty) AS DeliveredCount
  FROM Deliveries
  GROUP BY ProjectID
)
SELECT 
  t.ProjectID,
  t.ProjectName,
  ad.DeliveredCount,
  t.TargetValue,
  CASE 
    WHEN t.TargetValue = 0 OR ad.DeliveredCount = 0 THEN 0
    ELSE ROUND((ad.DeliveredCount * 100.0 / t.TargetValue), 2) 
  END AS AchievementRate,
  ROW_NUMBER() OVER(
    ORDER BY 
      CASE 
        WHEN t.TargetValue = 0 OR ad.DeliveredCount = 0 THEN 0
        ELSE (ad.DeliveredCount * 100.0 / t.TargetValue) 
      END DESC,
      t.ProjectID -- 达成率相同时，按ProjectID排序保证唯一排名
  ) AS RankNum
FROM Targets t
INNER JOIN AggregatedDeliveries ad 
  ON t.ProjectID = ad.ProjectID
ORDER BY RankNum;

这个查询会：

1. 先聚合每个项目的总交付数
2. 通过INNER JOIN关联目标表和聚合后的交付表
3. 正确计算达成率，零值情况设为0
4. 生成唯一的排名，达成率最高的排第一，零值记录排最后
5. 最终结果按排名升序展示，每个项目只出现一次

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