我们可以同时使用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
更多推荐
同时加入操作和限制
发布评论