mysql - UNION 上如何有两个约束？

Question

我在下面有这个代码。如果我只使用一个 WHERE 变量，它就可以工作，但是当我添加另一个变量时，查询就不起作用了。

如果我只使用这个所有工会，它就会起作用:

       where table_constant.user_id = '$uid'

但是当我使用下面这个时，它不起作用:

      where table_constant.user_id = '$uid' and table_one.something <> '$uid'

代码:

  $sql = "select table_one.field1, table_constant.field1, 
table_one.field2, table_one.field3, table_one.field4, 
table_one.field5, table_constant.c_id
from table_one LEFT JOIN table_constant on table_one.field1 
= table_constant.c_id 
where table_constant.user_id = '$uid' and table_one.something <> '$uid'
UNION
select table_two.field1, table_constant.field1, table_two.field2, 
table_two.field3,    table_two.field4, table_two.field5, table_constant.c_id
from table_two LEFT JOIN table_constant on table_two.c_id 
= table_constant.c_id 
where table_two.added_by = '$uid' and table_two.something <> '$uid'
UNION 
select table_three.field1, table_constant.field1, table_three.field2, 
table_three.field3, table_three.field4, table_three.field5,
table_constant.c_id
from table_three LEFT JOIN table_constant ON table_three.c_id 
= table_constant.c_id
where table_constant.user_id = '$uid' and table_three.something <> '$uid'
UNION
select table_four.field1, table_constant.field1, table_four.field2, 
table_four.field3, table_four.field4, table_four.field5, 
table_constant.c_id
from table_four LEFT JOIN table_constant ON table_four.c_id 
= table_constant.c_id
where table_constant.user_id = '$uid' and table_four.something <> '$uid'
ORDER BY date DESC LIMIT $start, $limit";
$result = mysql_query($sql);

Answer 1

再想一想，我对问题所在有了一个不错的猜测。当您编写 LEFT JOIN 时，我认为您这样做是因为可能有一些行与连接右侧的任何内容都不匹配。如果您想限制那些确实以某种方式匹配的行，则需要在JOIN 本身中执行此操作。例如:

LEFT JOIN table_constant ON table_one.field1 = table_constant.c_id AND table_constant.user_id = '$uid'

通过将第二个条件放在 WHERE 子句中，您实际上是通过强制右侧具有值来将左联接转变为内联接。