MySQL mysql查询两张表，UNION和where子句

mysql query two tables, UNION and where clause

提问by stefanosn

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.

任何帮助表示赞赏。

回答by Eugen Rieck

There are two problems with your SQL:

你的 SQL 有两个问题：

1. (THis is notthe question, but should be considered) by using WHEREover the UNIONinstead 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.

2. The reason you get two rows is, that UNION DISTINCTwill only suppress real duplicates, so the tuple (someuser,peter)and the tuple (someotheruser, peter)will result in duplication.

1. （这不是问题，但应该考虑）通过使用WHEREoverUNION而不是表，会造成性能噩梦：MySQL 将创建一个包含 的临时表UNION，然后通过WHERE. 在字段 ( LOWER(requester_name))上使用计算会使情况变得更糟。

2. 你得到两行的原因是，这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 someuserwhich you already know : peter

所以你只选择someuser你已经知道的：peter

回答by Bohemian

You need the where clause on bothselects:

两个选择都需要 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.

这两个查询是相互独立的，所以除了 by 之外，你不应该尝试连接它们union。

回答by Bijon Krishna Bairagi

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

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

回答by James Oravec

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

在 where 语句中，为 where 语句中的每个字段引用引用别名“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 语句？

回答by amit_g

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'))

回答by Jun Wei Lee

You should be able to use the INTERSECTkeyword 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

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