如何编写无重复结果的正确SQL查询？多表关联取首图去重

你的问题出在直接关联Image表时，每个Item对应多少张图片，就会生成多少条重复的Item记录——因为每条图片都会和Item做一次匹配关联。要实现每个Item只取第一张图片，得先筛选出每个Item对应的目标图片，再和主查询关联，下面给两种实用方法：

方法一：子查询筛选每个Item的首张图片 #

假设Image表用Image_Id来判定“第一张”（如果你的图片有自定义排序字段比如SortOrder，替换成对应字段即可），通过子查询找到每个Item对应的最小Image_Id，只关联这张图片：

SELECT Item.*, Category.*, SubCategory.*, Location.*, Brand.*, ItmCondition.*, User.*, Image.*
FROM Item
JOIN Category ON Item.Category_Id = Category.Category_Id
JOIN SubCategory ON Item.SubCategory_Id = SubCategory.SubCategory_Id
JOIN Location ON Item.Location_Id = Location.Location_Id
JOIN Brand ON Item.Brand_Id = Brand.Brand_Id
JOIN ItmCondition ON Item.Condition_Id = ItmCondition.Condition_Id
JOIN User ON Item.User_Id = User.User_Id
JOIN Image ON Image.Item_Id = Item.Item_Id 
    AND Image.Image_Id = (
        SELECT MIN(Image_Id) 
        FROM Image 
        WHERE Item_Id = Item.Item_Id
    )
WHERE Item.Category_Id = ".$_GET['ctgId'].";

如果要按上传时间取最早的图片，把子查询里的MIN(Image_Id)改成MIN(UploadTime)，同时确保Image表有UploadTime字段。

方法二：用窗口函数标记并筛选（适合MySQL 8+、PostgreSQL等） #

用ROW_NUMBER()窗口函数给每个Item下的图片排序，然后只保留序号为1的记录：

SELECT *
FROM (
    SELECT 
        Item.*, Category.*, SubCategory.*, Location.*, Brand.*, ItmCondition.*, User.*, Image.*,
        ROW_NUMBER() OVER (PARTITION BY Item.Item_Id ORDER BY Image.Image_Id) AS rn
    FROM Item
    JOIN Category ON Item.Category_Id = Category.Category_Id
    JOIN SubCategory ON Item.SubCategory_Id = SubCategory.SubCategory_Id
    JOIN Location ON Item.Location_Id = Location.Location_Id
    JOIN Brand ON Item.Brand_Id = Brand.Brand_Id
    JOIN ItmCondition ON Item.Condition_Id = ItmCondition.Condition_Id
    JOIN User ON Item.User_Id = User.User_Id
    JOIN Image ON Item.Item_Id = Image.Item_Id
    WHERE Item.Category_Id = ".$_GET['ctgId']."
) AS temp
WHERE rn = 1;

这里PARTITION BY Item.Item_Id是按Item分组，ORDER BY Image.Image_Id是给组内图片排序，你可以把排序字段换成实际需要的（比如UploadTime）来定义“第一张”的规则。

额外提醒 #

你直接把$_GET['ctgId']拼进SQL里，存在严重的SQL注入风险，新手建议用预处理语句，比如PHP里用PDO的prepare和execute方法来传参，不要直接拼接变量。

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