mysql查询两个表，UNION和where子句

I have two tables.

我有两张桌子。

I query like this:

我这样查询:

SELECT * FROM (
   Select requester_name,receiver_name from poem_authors_follow_requests  as one 
UNION 
Select requester_name,receiver_name from poem_authors_friend_requests as two 
) as u 
where (LOWER(requester_name)=LOWER('user1') or LOWER(receiver_name)=LOWER('user1'))

I am using UNION because i want to get distinct values for each user if a user exists in the first table and in the second.

我正在使用UNION,因为如果第一个表和第二个表中存在用户,我想为每个用户获取不同的值。

For example:

table1

nameofuser
peter

table2

nameofuser
peter

if peter is on either table i should get the name one time because it exists on both tables.

如果peter在任一个表上,我应该得到一次名称,因为它存在于两个表中。

Still i get one row from first table and a second from table number two. What is wrong?

我仍然从第一张桌子获得一排,从第二张桌子获得第二排。哪里不对?

Any help appreciated.

任何帮助赞赏。

6 个解决方案

#1

There are two problems with your SQL:

您的SQL有两个问题:

(THis is not the question, but should be considered) by using WHERE over the UNION instead of the tables, you create a performance nightmare: MySQL will create a temporary table containing the UNION, then query it over the WHERE. Using a calculation on a field (LOWER(requester_name)) makes this even worse.

(这不是问题,但应该考虑)通过在UNION而不是表上使用WHERE,您创建了一个性能噩梦:MySQL将创建一个包含UNION的临时表,然后通过WHERE查询它。在字段上使用计算(LOWER(requester_name))会使情况更糟。
The reason you get two rows is, that UNION DISTINCT will only suppress real duplicates, so the tuple (someuser,peter) and the tuple (someotheruser, peter) will result in duplication.

你得到两行的原因是,UNION DISTINCT只会抑制真正的重复,因此元组(someuser,peter)和元组(someotheruser,peter)将导致重复。

Edit

To make (someuser, peter) a duplicate of (peter, someuser) you could use:

要使(someuser,peter)复制(peter,someuser),你可以使用:

SELECT
  IF(requester_name='peter', receiver_name, requester_name) AS otheruser
FROM
  ...
UNION
SELECT
  IF(requester_name='peter', receiver_name, requester_name) AS otheruser
FROM
  ...

So you only select someuser which you already know : peter

所以你只选择一些你已经知道的用户:彼得

#2

You need the where clause on both selects:

你需要两个选择的where子句:

select requester_name, receiver_name
from poem_authors_follow_requests
where LOWER(requester_name) = LOWER('user1') or LOWER(receiver_name) = LOWER('user1')
union
select requester_name, receiver_name
from poem_authors_friend_requests
where LOWER(requester_name) = LOWER('user1') or LOWER(receiver_name) = LOWER('user1')

The two queries are independent of each other, so you shouldn't try to connect them other than by union.

这两个查询是相互独立的,因此除了通过union之外,不应该尝试连接它们。

#3

You can use UNION if you want to select rows one after the other from several tables or several sets of rows from a single table all as a single result set. UNION is available as of MySQL 4.0. This section illustrates how to use it. Suppose you have two tables that list prospective and actual customers, a third that lists vendors from whom you purchase supplies, and you want to create a single mailing list by merging names and addresses from all three tables. UNION provides a way to do this. Assume the three tables have the following contents:

如果要从多个表中一个接一个地选择行,或者从单个表中选择几组行作为单个结果集,则可以使用UNION。 UNION从MySQL 4.0开始提供。本节说明如何使用它。假设您有两个列出潜在客户和实际客户的表,第三个表列出了您从中购买耗材的供应商,并且您希望通过合并来自所有三个表的名称和地址来创建单个邮件列表。 UNION提供了一种方法。假设这三个表具有以下内容:

http://w3webtutorial.blogspot.com/2013/11/union-in-mysql.html

#4

You are doing the union before and then applying the where clause. So you would get a unique combination of "requester_name,receiver_name" and then the where clause would apply. Apply the where clause in each select...

您之前正在进行联合,然后应用where子句。因此,您将获得“requester_name,receiver_name”的唯一组合,然后将应用where子句。在每个选择中应用where子句......

Select requester_name,receiver_name from poem_authors_follow_requests
where (LOWER(requester_name)=LOWER('user1')
        or LOWER(receiver_name)=LOWER('user1'))
UNION 
Select requester_name,receiver_name from poem_authors_friend_requests 
where (LOWER(requester_name)=LOWER('user1')
        or LOWER(receiver_name)=LOWER('user1'))

#5

In your where statement, reference the alias "u" for each field refence in your where statement.

在where语句中,在where语句中引用每个字段refence的别名“u”。

So the beginning of your where statement would be like: where (LOWER(u.requester_name) = ...

所以你的where语句的开头就像:where(LOWER(u.requester_name)= ...

This is simlar to the answer you can see in: WHERE statement after a UNION in SQL?

这是你可以看到的答案的相似之处:在SQL中的UNION之后的WHERE语句?

#6

-1

You should be able to use the INTERSECT keyword instead of doing a nested query on a UNION.

您应该能够使用INTERSECT关键字而不是在UNION上执行嵌套查询。

SELECT member_id, name FROM a
INTERSECT
SELECT member_id, name FROM b

can simply be rewritten to

可以简单地改写成

SELECT a.member_id, a.name
FROM a INNER JOIN b
USING (member_id, name)

http://www.bitbybit.dk/carsten/blog/?p=71

#1