使用DISTINCT后SQL查询仍返回重复值问题求助

问题背景 #

我遇到了SQL查询结果重复显示的问题，即便使用DISTINCT也无法解决。现有5张表：

• products表：包含id、category字段（共4个分类：Hygiene、Food、Toys、Clothes）
• 4个分类专属表：hygiene、food、toys、clothes，存储product_name、price字段，均与stock表关联
• stock表：存储quantity字段

需求是每个商品仅显示一次，并携带正确的quantity和price，但以下两段分别使用UNION、UNION+DISTINCT的PHP代码均返回重复数据：

第一段代码（UNION实现） #

<tr>
    <th>Product Name</th>
    <th>Quantity</th>
    <th>Price</th>
    <th>Action</th>
</tr>
<?php
$conn = mysqli_connect("localhost:3307", "root", "", "db_login");

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

$sql = "SELECT products.id, products.category, hygiene.product_name, hygiene.price, stock.quantity
FROM products 
LEFT JOIN hygiene ON products.id = hygiene.product_id
LEFT JOIN stock ON hygiene.product_id = stock.item_id
WHERE products.category = 'Hygiene'

UNION

SELECT products.id, products.category, food.product_name, food.price, stock.quantity
FROM products 
LEFT JOIN food ON products.id = food.product_id
LEFT JOIN stock ON food.product_id = stock.item_id
WHERE products.category = 'Food'

UNION

SELECT products.id, products.category, toys.product_name, toys.price, stock.quantity
FROM products 
LEFT JOIN toys ON products.id = toys.product_id
LEFT JOIN stock ON toys.product_id = stock.item_id
WHERE products.category = 'Toys'

UNION

SELECT products.id, products.category, clothes.product_name, clothes.price, stock.quantity
FROM products 
LEFT JOIN clothes ON products.id = clothes.product_id
LEFT JOIN stock ON clothes.product_id = stock.item_id
WHERE products.category = 'Clothes'";


$result = $conn->query($sql);

while ($row = mysqli_fetch_array($result)) {
  
    $product_name = isset($row['product_name']) ? $row['product_name'] : '';
    $quantity = isset($row['quantity']) ? $row['quantity'] : '';
    $price = isset($row['price']) ? $row['price'] : '';
    
    echo "<tr>";
    echo "<form name='update' action='update_stock.php' method='post'>";
    echo "<td><input type='text' name='product_name' value='".$row ['product_name']."'></td>";
    echo "<td><input type='text' class='stock--update--num' name='quantity' value='".$row['quantity']."'></td>";
    echo "<td><input type='text' class='stock--update--num' name='price' value='".$row['price']. "€"."'></td>";
    echo "<td><input type='submit' name='update' value='Update'></td>";
    echo "</form>";
    echo "</tr>";
}
?>
</table>

第二段代码（UNION+DISTINCT实现） #

<?php
$conn = mysqli_connect("localhost:3307", "root", "", "db_login");

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 

$sql = "SELECT DISTINCT products.*, hygiene.product_id, hygiene.product_name, hygiene.price, hygiene.image_path, stock.quantity
FROM products 
LEFT JOIN hygiene ON products.id = hygiene.product_id
LEFT JOIN stock ON hygiene.product_id = stock.item_id
WHERE products.category = 'Hygiene'
UNION
SELECT DISTINCT products.*, food.product_id, food.product_name, food.price, food.image_path, stock.quantity
FROM products 
LEFT JOIN food ON products.id = food.product_id
LEFT JOIN stock ON food.product_id = stock.item_id
WHERE products.category = 'Food'
UNION
SELECT DISTINCT products.*, toys.product_id, toys.product_name, toys.price, toys.image_path, stock.quantity
FROM products 
LEFT JOIN toys ON products.id = toys.product_id
LEFT JOIN stock ON toys.product_id = stock.item_id
WHERE products.category = 'Toys'
UNION
SELECT DISTINCT products.*, clothes.product_id, clothes.product_name, clothes.price, clothes.image_path, stock.quantity
FROM products 
LEFT JOIN clothes ON products.id = clothes.product_id
LEFT JOIN stock ON clothes.product_id = stock.item_id
WHERE products.category = 'Clothes'";

$result = $conn->query($sql);
while ($row = mysqli_fetch_array($result)) {
    echo "<tr>";
    echo "<form name='update' action='update_stock.php' method='post'>";

    $product_name = isset($row['product_name']) ? $row['product_name'] : '';
    $quantity = isset($row['quantity']) ? $row['quantity'] : '';
    $price = isset($row['price']) ? $row['price'] : '';

    echo "<td><input type='text' name='product_name' value='".$product_name."'></td>";
    echo "<td><input type='text' class='stock--update--num' name='quantity' value='".$quantity."'></td>";
    echo "<td><input type='text' class='stock--update--num' name='price' value='".$price. "€"."'></td>";
    echo "<td><input type='submit' name='update' value='Update'></td>";
    echo "</form>";
    echo "</tr>";
}
?>

问题原因 #

1. UNION的去重逻辑：UNION本身会自动对结果集去重，但仅当整行数据完全一致时才会合并。如果同一个商品在stock表中有多条记录（比如多次入库），会导致quantity值不同，整行数据存在差异，UNION无法去重。
2. DISTINCT无效的原因：第二段代码中在每个SELECT后加DISTINCT毫无意义，因为UNION是对所有子查询的合并结果去重；而且如果行数据本身存在差异（比如image_path不同、quantity不同），DISTINCT也无法合并重复的商品记录。
3. LEFT JOIN的副作用：使用LEFT JOIN关联stock表时，若一个商品对应多条stock记录，会导致该商品被多次输出，每条stock记录对应一行结果。

解决方案 #

方案1：对子查询做聚合，确保单商品单记录 #

通过GROUP BY聚合每个商品的库存数据，确保每个商品只返回一条记录，再用UNION合并结果：

SELECT p.id, p.category, h.product_name, h.price, COALESCE(SUM(s.quantity), 0) AS quantity
FROM products p
LEFT JOIN hygiene h ON p.id = h.product_id
LEFT JOIN stock s ON h.product_id = s.item_id
WHERE p.category = 'Hygiene'
GROUP BY p.id, p.category, h.product_name, h.price

UNION

SELECT p.id, p.category, f.product_name, f.price, COALESCE(SUM(s.quantity), 0) AS quantity
FROM products p
LEFT JOIN food f ON p.id = f.product_id
LEFT JOIN stock s ON f.product_id = s.item_id
WHERE p.category = 'Food'
GROUP BY p.id, p.category, f.product_name, f.price

UNION

SELECT p.id, p.category, t.product_name, t.price, COALESCE(SUM(s.quantity), 0) AS quantity
FROM products p
LEFT JOIN toys t ON p.id = t.product_id
LEFT JOIN stock s ON t.product_id = s.item_id
WHERE p.category = 'Toys'
GROUP BY p.id, p.category, t.product_name, t.price

UNION

SELECT p.id, p.category, c.product_name, c.price, COALESCE(SUM(s.quantity), 0) AS quantity
FROM products p
LEFT JOIN clothes c ON p.id = c.product_id
LEFT JOIN stock s ON c.product_id = s.item_id
WHERE p.category = 'Clothes'
GROUP BY p.id, p.category, c.product_name, c.price

• SUM(s.quantity)：统计该商品的总库存，若只需单条库存记录，可替换为MAX(s.quantity)或MIN(s.quantity)（根据业务需求选择）。
• COALESCE(..., 0)：处理库存为NULL的情况，替换为默认值0，避免显示空值。
• 若不需要products表中无对应分类商品的记录，可将LEFT JOIN改为INNER JOIN，减少无效数据。

方案2：优化表结构（推荐） #

将4个分类专属表合并为一张products_detail表，新增category字段，减少冗余表，简化查询逻辑：

• products_detail表字段：id、product_id、product_name、price、category、image_path

优化后的查询SQL：

SELECT p.id, p.category, pd.product_name, pd.price, COALESCE(SUM(s.quantity), 0) AS quantity
FROM products p
JOIN products_detail pd ON p.id = pd.product_id AND p.category = pd.category
LEFT JOIN stock s ON pd.product_id = s.item_id
GROUP BY p.id, p.category, pd.product_name, pd.price

这种结构更符合数据库设计的归一化原则，避免重复创建相似表，后续维护和扩展更便捷。

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