SQL Server JOIN查询返回多行重复记录问题：需仅保留左表单条产品记录

嘿，我一眼就揪出问题所在了——你遇到的重复记录是因为JOIN操作和窗口函数的计算时机冲突，再加上DISTINCT没能发挥作用。具体拆解下：

1. 当你用LEFT JOIN Products_Colours时，一个产品如果有多个符合条件的颜色记录（比如你的ProdID 500同时有Blue和Yellow，当前筛选Blue时，哪怕只有一条匹配的颜色行，但如果JOIN逻辑处理不当，也会生成重复条目），每一条匹配的颜色行都会和产品行组合，产生重复的产品记录。
2. 你在SELECT中加入了ROW_NUMBER()窗口函数，它会给每一行（包括重复的产品行）分配唯一的行号。哪怕ProdID和Category完全相同，RowNum的值也是不同的，这就导致DISTINCT无法对这些行去重——因为DISTINCT是对整个SELECT列的组合去重，而RowNum的唯一性破坏了重复行的列组合一致性。

要解决这个问题，我们需要先筛选出唯一的产品条目，再在这个基础上计算分页所需的窗口函数。这里用EXISTS子查询替代JOIN，从根源避免重复行：

DECLARE @OrderBy varchar(1)
SET @OrderBy = 'D'
DECLARE @Row int
SET @Row = 1
DECLARE @ControlID int
SET @ControlID = 1 -- 修正了变量名的笔误

/* 获取网页的控制信息 */
SELECT c.ID, c.Title FROM dbo.Control c WHERE c.ID = @ControlID;

/* 获取搜索条件 */
WITH ControlSearch AS (
 SELECT ID, Category, Colour FROM Control WHERE ID = @ControlID
),
/* 先筛选出符合条件的唯一产品 */
ProductFilter AS (
 SELECT p.pk_ProdID AS ProdID, p.Category, p.Price
 FROM dbo.Products p
 CROSS JOIN ControlSearch l
 WHERE p.Category = l.Category
 AND (
   -- 如果Control的Colour为null，直接匹配所有该分类产品
   l.Colour IS NULL 
   -- 否则检查产品是否有对应的可售颜色
   OR EXISTS (
     SELECT 1 
     FROM dbo.Products_Colours co 
     WHERE co.fk_ProdID = p.pk_ProdID 
     AND co.Colour = l.Colour
   )
 )
 GROUP BY p.pk_ProdID, p.Category, p.Price -- 确保产品唯一
),
/* 计算分页所需的总数和行号 */
ProductSearch AS (
 SELECT ProdID, Category, Price,
 Count(1) OVER() As Total_Records,
 ROW_NUMBER() OVER(
 ORDER BY CASE WHEN @OrderBy = 'D' THEN Price END DESC,
 CASE WHEN @OrderBy = 'U' THEN Price END ASC
 ) As RowNum
 FROM ProductFilter
)
/* 返回结果集 */
SELECT * FROM ProductSearch WHERE RowNum >= @Row AND RowNum < (@Row + 50)

• 用EXISTS替代LEFT JOIN：只检查产品是否存在符合条件的颜色，不会生成重复行，确保每个产品只出现一次。
• 新增ProductFilter CTE：先筛选出唯一的产品列表，再基于这个列表计算窗口函数，避免RowNum破坏去重逻辑。
• 修正了变量名笔误：原来的SET @Control = 1改为SET @ControlID = 1，确保参数正确传递。

这样修改后，你就能得到预期的无重复结果，同时保留分页所需的Total_Records和RowNum功能。

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