代码之家 › 专栏 › 技术社区 › ptrcao

请更正此唯一行选择的实现(删除重复项)

mysql-5.7 mysql

-1

ptrcao · 技术社区 · 7 年前

我正在尝试从联接表中选择唯一的行

其中,duplicate由列中具有相同值的记录定义 post_title

中具有最低值的行 _meta_value 要选择每个重复集合中的列。如果两排之间是平局,那么就选最低的那一排 post_ID 或 ID (独一无二)。

SELECT * FROM (

select wp_posts.ID, wp_posts.post_title, wp_postmeta.post_id, wp_posts.post_type, wp_postmeta.meta_key, wp_postmeta.meta_value
      from wp_postmeta JOIN wp_posts 
ON wp_postmeta.post_ID=wp_posts.ID

WHERE post_type = 'product' AND meta_key = '_regular_price'
    GROUP BY post_title
 ) as alias1
HAVING MIN(meta_value)

ORDER BY post_title

以下是测试后要求的最低数据样本 JOIN 在这两个表格中:

+-------------+---------------------+---------------------+--------------------+----------------------+------------------------+
| wp_posts.ID | wp_posts.post_title | wp_postmeta.post_id | wp_posts.post_type | wp_postmeta.meta_key | wp_postmeta.meta_value |
+-------------+---------------------+---------------------+--------------------+----------------------+------------------------+
|           1 | Apple Pie           |                   1 | Product            | _regular_price       |                     10 |
|           2 | French Toast        |                   2 | Product            | _regular_price       |                      5 |
|           3 | Shepards Pie        |                   3 | Product            | _regular_price       |                      9 |
|           4 | Jam Pie             |                   4 | Product            | _regular_price       |                      8 |
|           5 | Jam Pie             |                   5 | Product            | _regular_price       |                     11 |
|           9 | French Toast        |                   9 | Product            | _regular_price       |                     12 |
|          10 | French Toast        |                  10 | Product            | _regular_price       |                     12 |
+-------------+---------------------+---------------------+--------------------+----------------------+------------------------+

查询应返回:

+-------------+---------------------+---------------------+--------------------+----------------------+------------------------+
| wp_posts.ID | wp_posts.post_title | wp_postmeta.post_id | wp_posts.post_type | wp_postmeta.meta_key | wp_postmeta.meta_value |
+-------------+---------------------+---------------------+--------------------+----------------------+------------------------+
|           1 | Apple Pie           |                   1 | Product            | _regular_price       |                     10 |
|           2 | French Toast        |                   2 | Product            | _regular_price       |                      5 |
|           3 | Shepards Pie        |                   3 | Product            | _regular_price       |                      9 |
|           4 | Jam Pie             |                   4 | Product            | _regular_price       |                      8 |
+-------------+---------------------+---------------------+--------------------+----------------------+------------------------+

2 回复 | 直到 7 年前

ScaisEdge 7 年前

您可以使用group by和having count(*)根据帖子标题的过滤器(其中)获取副本>1.

select wp_posts.post_title
from wp_postmeta 
JOIN wp_posts ON wp_postmeta.post_ID=wp_posts.ID
    WHERE post_type = 'product' 
    AND meta_key = '_regular_price'
Group by wp_posts.post_title 
having count(*) > 1

select wp_posts.post_title, min(wp_posts.ID)
from wp_postmeta 
JOIN wp_posts ON wp_postmeta.post_ID=wp_posts.ID
    WHERE post_type = 'product' 
    AND meta_key = '_regular_price'
Group by wp_posts.post_title 
having count(*) > 1

相反,您应该使用带有min(id)的聚合结果作为联接所需值的子查询

select wp_posts.ID
    , wp_posts.post_title
    , wp_postmeta.post_id
    , wp_posts.post_type
    , wp_postmeta.meta_key
    , wp_postmeta.meta_value
from wp_postmeta 
JOIN wp_posts ON wp_postmeta.post_ID=wp_posts.ID 
inner join  (
 select wp_posts.post_title, min(wp_posts.ID) min_id 
    from wp_postmeta 
    JOIN wp_posts ON wp_postmeta.post_ID=wp_posts.ID
        WHERE post_type = 'product' 
        AND meta_key = '_regular_price'
    Group by wp_posts.post_title 
    having count(*) > 1

) t ON t.min_id  = wp_posts.ID  
        and  t.post_title = wp_posts.post_title

但是,如果您希望post_title的所有第一个值独立于标题是否重复的事实(如在您的示例中),那么请避免包含count(*)的子句>1.

select wp_posts.ID
, wp_posts.post_title
, wp_postmeta.post_id
, wp_posts.post_type
, wp_postmeta.meta_key
, wp_postmeta.meta_value
from wp_postmeta 
JOIN wp_posts ON wp_postmeta.post_ID=wp_posts.ID 
inner join  (
  select wp_posts.post_title, min(wp_posts.ID) min_id 
  from wp_postmeta 
  JOIN wp_posts ON wp_postmeta.post_ID=wp_posts.ID
    WHERE post_type = 'product' 
    AND meta_key = '_regular_price'
  Group by wp_posts.post_title 


 ) t ON t.min_id  = wp_posts.ID  
    and  t.post_title = wp_posts.post_title

问题似乎是将尊重最小值(wp_posts.ID)更改为p_posttea.meta_值

select wp_posts.ID
, wp_posts.post_title
, wp_postmeta.post_id
, wp_posts.post_type
, wp_postmeta.meta_key
, wp_postmeta.meta_value
from wp_postmeta 
JOIN wp_posts ON wp_postmeta.post_ID=wp_posts.ID 
inner join  (
  select wp_posts.post_title, min(wp_postmeta.meta_value) min_val
  from wp_postmeta 
  JOIN wp_posts ON wp_postmeta.post_ID=wp_posts.ID
    WHERE post_type = 'product' 
    AND meta_key = '_regular_price'
  Group by wp_posts.post_title 


 ) t ON t.min_val  = wp_postmeta.meta_value
    and  t.post_title = wp_posts.post_title

对于相同的meta_值,排除第二行使用最小值(id)

select wp_posts.ID
, wp_posts.post_title
, min( wp_postmeta.post_id)
, wp_posts.post_type
, wp_postmeta.meta_key
, wp_postmeta.meta_value
from wp_postmeta 
JOIN wp_posts ON wp_postmeta.post_ID=wp_posts.ID 
inner join  (
  select wp_posts.post_title, min(wp_postmeta.meta_value) min_val
  from wp_postmeta 
  JOIN wp_posts ON wp_postmeta.post_ID=wp_posts.ID
    WHERE post_type = 'product' 
    AND meta_key = '_regular_price'
  Group by wp_posts.post_title 


 ) t ON t.min_val  = wp_postmeta.meta_value
    and  t.post_title = wp_posts.post_title 
 group by  wp_posts.ID
, wp_posts.post_title
, wp_posts.post_type
, wp_postmeta.meta_key
, wp_postmeta.meta_value

LukStorms 7 年前

你可以通过一个简单的方法得到这些结果 GROUP BY 和使用 MIN .

SELECT 
 MIN(p.ID) AS ID, 
 p.post_title, 
 MIN(pm.post_id) AS post_id, 
 p.post_type, 
 pm.meta_key, 
 MIN(pm.meta_value) AS meta_value
FROM wp_posts p 
JOIN wp_postmeta pm ON (pm.post_ID = p.ID AND pm.meta_key = '_regular_price')
WHERE p.post_type = 'product'
GROUP BY p.post_title, p.post_type, pm.meta_key
ORDER BY ID;

here