如何使用 MySQL 查询去除具有相同颜色或大小的重复记录 [英] How to get rid off duplicate records with same color or size using MySQL query

问题描述

初始 SQL 查询结果

pkid    catpkid subcatpkid  title       description     createdatetime          visible     listprice   ourprice    memberprice     subcattitle largephotofilename  cattitle    colortitle  sizetitle   
132     5       118         Title01     Desc01          2020-07-15 14:44:51     YES         1599.00     570.00      470.00          SubCat01    image001.jpeg       cat001      Gray        L
132     5       118         Title01     Desc01          2020-07-15 14:44:51     YES         1599.00     570.00      470.00          SubCat01    image001.jpeg       cat001      Gray        XL
132     5       118         Title01     Desc01          2020-07-15 14:44:51     YES         1599.00     570.00      470.00          SubCat01    image001.jpeg       cat001      Gray        XXL
135     5       118         Title02     Desc02          2020-07-15 16:26:52     YES         1599.00     570.00      470.00          SubCat01    image002.jpeg       cat001      Pink        L
135     5       118         Title02     Desc02          2020-07-15 16:26:52     YES         1599.00     570.00      470.00          SubCat01    image002.jpeg       cat001      Pink        XL
135     5       118         Title02     Desc02          2020-07-15 16:26:52     YES         1599.00     570.00      470.00          SubCat01    image002.jpeg       cat001      Pink        XXL
135     5       118         Title02     Desc02          2020-07-15 16:26:52     YES         1599.00     570.00      470.00          SubCat01    image002.jpeg       cat001      Pink        XXXL

最初我得到了高于 SQL 结果的结果，由于相同的产品颜色和不同的尺寸，重复的产品显示在 SQL 结果中.然后我在这里得到了帮助，我使用了下面的查询，现在工作正常.我现在得到的结果低于 SQL 结果，其中 SQL 结果中仅显示具有相同 pkid 和颜色的第一个产品.

Initially I was getting above SQL result where duplicate products were displaying in SQL result due to same product color and different sizes. Then I got help here and I used below query which is working fine now. I am getting below SQL result now where only first product with same pkid and color is displaying in SQL result.

我正在使用的当前 SQL 查询

SELECT a.pkid, 
b.subcattitle, 
c.largephotofilename,  
d.title AS cattitle, 
f.title AS colortitle, 
min(n.title) AS sizetitle 
FROM t_store a 
LEFT JOIN t_store_cat d ON a.catpkid=d.catpkid AND d.visible='YES' 
LEFT JOIN t_store_subcat b ON a.subcatpkid=b.subcatpkid 
LEFT JOIN tr_store_photo c ON a.pkid=c.masterpkid 
LEFT JOIN tr_store_color e ON a.pkid=e.itempkid 
LEFT JOIN t_store_color f ON e.masterpkid=f.pkid 
LEFT JOIN tr_store_size m ON a.pkid=m.itempkid 
LEFT JOIN t_store_size n ON m.masterpkid=n.pkid 
WHERE a.visible='YES' AND b.visible='YES' AND c.visible='YES' 
AND a.catpkid = 5 
GROUP BY a.pkid, b.subcattitle, c.largephotofilename,  d.title , f.title 
ORDER BY a.createdatetime DESC

我想要的上述查询的SQL查询结果

pkid    catpkid subcatpkid  title       description     createdatetime          visible     listprice   ourprice    memberprice     subcattitle largephotofilename  cattitle    colortitle  sizetitle   
132     5       118         Title01     Desc01          2020-07-15 14:44:51     YES         1599.00     570.00      470.00          SubCat01    image001.jpeg       cat001      Gray        L
135     5       118         Title02     Desc02          2020-07-15 16:26:52     YES         1599.00     570.00      470.00          SubCat01    image002.jpeg       cat001      Pink        L

但是现在，当有不同颜色和相同尺寸的产品时，SQL 结果中会再次显示重复的产品，如下所示.

But now, when there is a product with different color and same size then again duplicate products are displaying in SQL result like below.

不同颜色相同大小的SQL查询结果

pkid    catpkid subcatpkid  title       description     createdatetime          visible     listprice   ourprice    memberprice     subcattitle largephotofilename  cattitle    colortitle  sizetitle   
76      3       101         Title76     Desc76          2020-07-15 14:44:51     YES         1599.00     570.00      470.00          SubCat101   image076.jpeg       cat003      Red         FREESIZE
76      3       101         Title76     Desc76          2020-07-15 16:26:52     YES         1599.00     570.00      470.00          SubCat101   image076.jpeg       cat003      Maroon      FREESIZE

现在我想让 SQL 结果中只显示具有相同 pkid 和大小的第一个产品.目前它们显示为 2 种不同的产品，因为它们具有相同的 pkid 和尺寸但颜色不同.

Now I want to make so only first product with same pkid and size should display in SQL result. Currently they are displaying as 2 different products because they have same pkid and size but different colors.

我仍然需要上面的查询，其中只有第一个具有相同 pkid 和颜色的产品显示在 SQL 结果中.

Still I will need above query where only first product with same pkid and color is displaying in SQL result.

所以我想让查询在两种情况下都可以使用.

So I want to make so query should work in both cases.

谢谢，

推荐答案

您可以尝试使用如下子查询，

You can try using sub-query like below,

SELECT 
    temp.pkid,
    temp.subcattitle,
    temp.largephotofilename,
    temp.cattitle,
    temp.colortitle,
    temp.sizetitle
FROM
    (SELECT 
        a.pkid,
            b.subcattitle,
            c.largephotofilename,
            d.title AS cattitle,
            f.title AS colortitle,
            MIN(n.title) AS sizetitle,
            a.createdatetime
    FROM
        t_store a
    INNER JOIN t_store_cat d ON a.catpkid = d.catpkid
        AND d.visible = 'YES'
    INNER JOIN t_store_subcat b ON a.subcatpkid = b.subcatpkid
    LEFT JOIN tr_store_photo c ON a.pkid = c.masterpkid
    LEFT JOIN tr_store_color e ON a.pkid = e.itempkid
    LEFT JOIN t_store_color f ON e.masterpkid = f.pkid
    LEFT JOIN tr_store_size m ON a.pkid = m.itempkid
    LEFT JOIN t_store_size n ON m.masterpkid = n.pkid
    WHERE
        a.visible = 'YES' AND b.visible = 'YES'
            AND c.visible = 'YES'
            AND a.catpkid = 5
    GROUP BY a.pkid , b.subcattitle , c.largephotofilename , d.title , f.title
    ORDER BY a.createdatetime DESC) AS temp
GROUP BY temp.pkid , temp.subcattitle , temp.largephotofilename , temp.cattitle
HAVING MAX(temp.createdatetime)
ORDER BY temp.createdatetime DESC

此查询将首先根据您的条件过滤数据，然后将删除您提到的重复数据.为了获取第一行，我添加了 have 子句 并从外部查询的 group by 中删除了 colortitle.

This query will first filter data based on your criteria and then it will remove duplicate data as you mentioned. To fetch the first row I have added having clause and removed colortitle from the group by of the outer query.

这篇关于如何使用 MySQL 查询去除具有相同颜色或大小的重复记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！