Mysql联合查询重复行 [英] Mysql union query duplicate rows

问题描述

我在一个约会网站上工作，多亏了很好的答案，我才设法提出这个问题.我完全是个新手:)

I am working on a dating site and thanks to the great answers I managed to make this query. I am totally a newb:)

这个联合查询的问题是它似乎没有删除重复的条目，我不知道为什么.

用户名 - 是加入网站的人的名字；

username - is the name of people who join the site;

意愿 - 是关于他们愿意做什么，是他们个人资料页面的一部分.

willingness - is about what they are willing to do, part of their profile page.

例如，如果我搜索词 "lunch"(就像在下面的查询中一样)，查询会找到在他们的词中包含字符 "lunch" 的人用户名或在他们的意愿个人资料中.

For example, if I search for the word "lunch" (like in the query bellow), the query finds people that have the chars "lunch" in their username or in their willingness profile.

但有时，某人的用户名和其意愿资料中都包含午餐"这个词.

But sometimes, someone has both the word "lunch" in his username and in it's willingness profile.

如何删除这些重复的行?

How can i remove these duplicate rows?

我将此查询用于 php 导航代码.

I use this query for a php navigation code.

你能帮忙吗?

result = mysql_query("

(SELECT 1 as sort_col,username,pic0 FROM ".$table." WHERE username IS NOT NULL AND username LIKE '%lunch%') 

UNION 

(SELECT 2 as sort_col,username,pic0 FROM ".$table." WHERE username IS NOT NULL AND age BETWEEN '28' AND '45' AND willingness LIKE '%lunch%' )

ORDER BY sort_col 

LIMIT ".$offset.", ".$rowsperpage."");

有什么想法吗?

谢谢！

推荐答案

解决方法.您可以使用:

Workaround. You could use:

SELECT username, pic0 
FROM ".$table." 
WHERE ( username LIKE '%lunch%' ) 
   OR ( username IS NOT NULL 
      AND age BETWEEN 28 AND 45 
      AND willingness LIKE '%lunch%' 
      )
ORDER BY ( username LIKE '%lunch%' ) DESC

这篇关于Mysql联合查询重复行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！