请求编写T-SQL查询：按优先级返回特定经销商的唯一关联资产

核心解决方案 #

以下T-SQL查询直接在数据库内完成优先级筛选，仅返回目标经销商有权关联的资产记录：

DECLARE @DealerNumber NVARCHAR(15) = '经销商A'; -- 替换为你要查询的经销商编号

WITH AssetPriority AS (
    SELECT 
        ad.AssetId,
        ad.DealerNumber,
        rt.PriorityRank,
        -- 按资产分组，优先级从高到低排序，最高优先级记录标记为1
        ROW_NUMBER() OVER (PARTITION BY ad.AssetId ORDER BY rt.PriorityRank ASC) AS PriorityRankOrder
    FROM AssetDealer ad
    INNER JOIN RelationshipType rt 
        ON ad.RelationshipTypeId = rt.RelationshipTypeId
)
SELECT 
    a.AssetId,
    a.SerialNumber,
    a.Description
FROM Asset a
INNER JOIN AssetPriority ap 
    ON a.AssetId = ap.AssetId
WHERE ap.PriorityRankOrder = 1 -- 仅保留该资产的最高优先级经销商关联
  AND ap.DealerNumber = @DealerNumber;

方案说明 #

1. CTE 优先级计算：AssetPriority 公共表表达式负责给每个资产的所有经销商关联记录排序：
   • 按 AssetId 分组，确保每个资产的关联记录单独排序
   • 按 PriorityRank 升序排序（数值越小优先级越高，对应服务经销商>安装商>销售经销商）
   • 用 ROW_NUMBER() 生成组内排序编号，最高优先级的记录编号为1
2. 筛选目标经销商：关联 Asset 表后，仅保留编号为1的记录（即该资产的最高优先级关联），再匹配目标经销商编号，得到最终结果。

特殊场景处理 #

如果存在同一资产多个经销商拥有相同最高优先级的情况（比如两个服务经销商关联同一资产）：

• 若只需返回其中任意一个经销商的关联记录，保持 ROW_NUMBER() 即可
• 若需返回所有同最高优先级的经销商关联记录，将 ROW_NUMBER() 替换为 RANK()，此时 WHERE ap.PriorityRankOrder = 1 会返回所有同优先级的记录

性能优势 #

该查询在数据库端完成全量筛选，无需将80万+原始数据返回至C#端处理，大幅降低数据传输开销，提升查询效率。

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