删除SELECT中的重复项Mysql结果如果column1 = column2中的任何一个，那么它是重复的

Okay so I have a table called posts and I am trying to remove the duplicates on the condition that if one of the post_id is equal to the post_dat_id and only remove it if post_data_id == post_id has a post_type of 2.

好的所以我有一个名为posts的表,我试图删除重复项,条件是如果post_id之一等于post_dat_id,只有当post_data_id == post_id的post_type为2时才删除它。

Posts Structure

post_id || post_data_id || post_type || post_user_id
==================================
1 || NULL || 0 || 10210

2 || 1 || 2 || 201020 <-- it shouldn't show this one because it meets the condition that post_type == 2 && the post_data_id is equal to one of the post_id. EDIT: also notice how this id = 2 but the post_data_id = 1. It is impossible for a row ot have the same post_id & post_data_id

2 || 1 || 0 || 202020

3 || 6 || 2 || 202020

My mysql:

 SELECT p.*
 FROM posts p
 LEFT JOIN following f ON f.user_id =1
 AND p.post_user_id = f.follower_id 
 WHERE post_user_id =1
 OR f.user_id IS NOT NULL 
 ORDER BY  `p`.`post_time` DESC 
 LIMIT 0 , 10

EDIT: I do not want to delete the code all I want to do is not show that result if the meet the criteria in my select. Also I am already using a left join because my sql code needs to check the following table to get the user id's

编辑:我不想删除所有我想要做的代码,如果满足我的选择标准,则不显示结果。此外,我已经在使用左连接,因为我的sql代码需要检查下表以获取用户ID

EDIT 2: I also changed the post_data to post_data_id so its a pure int now

编辑2:我也将post_data更改为post_data_id,因此它现在是一个纯int

2 个解决方案

#1

Haven't tested this code but I would do something like this:

没有测试过这段代码,但我会这样做:

select from posts p where p.id not in
(select q.id from posts q
where q.post_data = q.post_id and q.post_type = 2);

#2

I am not sure that I have fully understood your question but it sounds like you need to use a LEFT JOIN to remove the rows matching the given criteria -

我不确定我是否已完全理解您的问题,但听起来您需要使用LEFT JOIN来删除符合给定条件的行 -

SELECT p1.*
FROM posts p1
LEFT JOIN posts p2
    ON p1.post_data_id = p2.post_id
    AND p1.post_type = 2
LEFT JOIN following f
    ON f.follower_id = 1
    AND p1.post_user_id = f.user_id 
WHERE p2.post_id IS NULL
AND (p1.post_user_id = 1 OR f.user_id IS NOT NULL)
ORDER BY  p1.post_time DESC 
LIMIT 0, 10

This will have quite a performance hit as your dataset grows and it looks like you have some issues with the structure of the data. You might want to look at moving the self referencing foreign key to its own field.

随着数据集的增长,这将对性能产生很大的影响,看起来您对数据结构存在一些问题。您可能希望将自引用外键移动到其自己的字段。

#1