问题描述
我有两个桌子.
我这样查询:
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有两个问题:
-
(不是不是问题,但是应该考虑),通过在
UNION
而不是表上使用WHERE
,会造成性能方面的噩梦:MySQL将创建一个包含UNION
的临时表,然后通过WHERE
查询它.在字段(LOWER(requester_name)
)上使用计算会使情况更糟.
(THis is not the question, but should be considered) by using
WHERE
over theUNION
instead of the tables, you create a performance nightmare: MySQL will create a temporary table containing theUNION
, then query it over theWHERE
. 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子句的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!