Mysql left join 从结果中删除重复值 [英] Mysql left join removing duplicate values from the result

问题描述

我有两张桌子.用户表和评论表.

请提供架构和演示

在第一行中，您可以看到查询返回 user_1_id = 2 和 user_2_id = 2 这里我只想获取两个最大用户，但它们必须是唯一的.所以在这种情况下，user_2_id 应该为空，user_2 的其他字段也是如此.

1. 如果没有找到两个唯一的 id，那么只返回一个，其余的可以为空，就像你在第 4 行看到的那样.
2. 如果在第一行中多次找到相同的 id，则只应检索一次.

非常感谢.它是来自原始问题的第二个问题 MySQL group by with左连接

解决方案

left join users u2在 t.user_2 = u2.id AND u2.id <>u1.id

在 u2 的左连接中，只需告诉查询检查 u2.id != u1.id.

我认为它应该可以解决您的问题.

I have two tables. users table and a comments table.

Please the schema and demo http://sqlfiddle.com/#!9/e3269c/5

Please see the image

In the first row, you can see that the query returns user_1_id = 2 and user_2_id = 2 Here I want to get only two maximum users but they have to be unique. So in this case, user_2_id should be null and other field of user_2 as well.

1. If two unique ids is not found, then return only one, and rest can be null like you see in row 4.
2. If same ids are found multiple time like in the first row, then only once they should be retrieved.

Thank you so much. Its a second question from the original question MySQL group by with left join

解决方案

left join users u2 on t.user_2 = u2.id AND u2.id <> u1.id

In the left join of u2 just tell the query to check that u2.id != u1.id.

I think it should solve your provlem.

这篇关于Mysql left join 从结果中删除重复值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！