同时加入操作和限制

本文介绍了同时加入操作和限制的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我们可以同时使用join操作和做限制吗?我想基于来自另一个数据库(在示例中称为 DB2)的两个表的数据库(在示例中称为 DB1)上创建一个表，其中当另一列(在例如性别"列中的4").-> 如果是，那怎么办?

Can we use the join operation and make restrictions at the same time? I want tocreate a table on a Database (called DB1 in the example) based on two tables from Database another database (called DB2 in the example) where one of the columns is filled when there´s a specific entry in on other column (in the example a"4" in column "gender"). -> If yes, then how to do ?

两个数据库在同一台服务器上，DBMS 是一样的.ID1和ID2基于DB2中的table1；ID1 和名称基于 DB2 中的 table2.

Both databases are on the same server and DBMS is the same. ID1 and ID2 based on table1 in DB2; ID1 and Name based on table2 in DB2.

这是我已经尝试过的，但我不确定它是否有效和/或从我使用 DB1 的角度来看它是否是最好的方法:

Here´s what I tried out already but I´m not sure if it works and/or its the best way Made from point of view that I´m using DB1:

INSERT INTO table3 (id1, id2, NAME) SELECT t1.id1, t1.id2, t2.NAME FROM db1.table1 t1 LEFT JOIN db1.table2 t2 ON t1.ID1=t2.ID1 WHERE gender = 4;

或者使用 AND 是否正确(更好)?:

Or is it correct (better) with AND?:

INSERT INTO table3 (id1, id2, NAME) SELECT t1.id1, t1.id2, t2.NAME FROM db1.table1 t1 LEFT JOIN db1.table2 t2 ON t1.ID1=t2.ID1 AND gender = 4;

推荐答案

它们是等价的(只要小心表别名 ..anyway )

They are equivalent (just be carefull with the table alias ..anyway )

你可以直接在 on join 子句中使用 and 条件

You can use the and condition directly in on join clause

INSERT INTO table3 (id1, id2, NAME) SELECT t1.id1, t1.id2, t2.NAME FROM db1.table1 t1 LEFT JOIN db1.table2 t2 ON t1.ID1=t2.ID1 and t1.gender = 4

或在 join 子句后使用 where

or use where after the join clause

INSERT INTO table3 (id1, id2, NAME) SELECT t1.id1, t1.id2, t2.NAME FROM db1.table1 t1 LEFT JOIN db1.table2 t2 ON t1.ID1=t2.ID1 WHERE t1.gender = 4;

dev.mysql/doc/refman/5.7/en/left-join-optimization.html