mysql查询两个表，UNION和where子句 [英] mysql query two tables, UNION and where clause

问题描述

我有两个桌子.

我这样查询:

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

我之所以使用UNION，是因为如果用户存在于第一个表和第二个表中，那么我想为每个用户获取不同的值.

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.

例如:

table1

nameofuser
peter

table2

nameofuser
peter

如果peter在任何一张桌子上，我都应该一次命名，因为它在两个桌子上都存在.

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

我仍然从第一张表中获得一行，而从第二张表中获得第二行.怎么了?

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

任何帮助表示赞赏.

推荐答案

SQL有两个问题:

1. (不是不是问题，但是应该考虑)，通过在UNION而不是表上使用WHERE，会造成性能方面的噩梦:MySQL将创建一个包含UNION的临时表，然后通过WHERE查询它.在字段(LOWER(requester_name))上使用计算会使情况更糟.

1. (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 DISTINCT仅抑制真实重复，因此元组(someuser,peter)和元组(someotheruser, peter)将导致重复.

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.

修改

要使(someuser, peter)与(peter, someuser)重复，可以使用:

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

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
  ...

因此，您只选择已经知道的someuser:peter

So you only select someuser which you already know : peter

这篇关于mysql查询两个表，UNION和where子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！