我需要帮助从两个独立的数据库中选择2个相同的表 [英] I need help with selecting from 2 identical tables in 2 separate databases
问题描述
MySQL 3.23.58 - 4.0.17（是的，几个数据库服务器实例，不要
问）
我有数据库Spring with学生
我有数据库夏天和桌子学生
我的任务是在两个表格中生成所有学生的查询，没有
重复。毫无疑问。
我正在考虑一个INTERSECT，然后是2个UNION SELECT语句，但
我离我有多远？这些表在各个方面完全相同
包括索引;数据可能是相同的（相同的数据可能在
Spring.Students和Summer.Students中），然而，这并不总是
的情况。 Spring.Students.id列不会与
Summer.Students.id共享相同的值，因为数据可以在单独的序列中输入
即使两个表中的数据也是如此绝对相同。
我是否正确使用INTERSECT / UNION或您推荐什么？
Thanx
Phil
MySQL 3.23.58 - 4.0.17 (yep, several database server instances, don''t
ask)
I have database Spring with table Students
I have database Summer with table Students
I am tasked to produce a query of all students in both tables with no
duplicates. No clue whatsoever.
I''m thinking an INTERSECT followed by 2 UNION SELECT statements, but
how far off am I? The tables are absolutely identical in every way
including indexes; the data MIGHT be identical (same data might be in
Spring.Students and Summer.Students), however, that won''t always be the
case. The column Spring.Students.id will not share the same value as
Summer.Students.id as data can be entered within separate sequences
even if the data in both tables is absolutely identical.
Am I on the right track with INTERSECT/UNION or what do you recommend?
Thanx
Phil
推荐答案
ph ************** @ gmail.com 写道：
MySQL 3.23.58 - 4.0.17（是的，几个数据库服务器实例，不要问）
我有数据库Spring带表学生
我有数据库夏天用表学生
我的任务是在两个表中生成所有学生的查询，没有
重复。毫无疑问。
我正在考虑一个INTERSECT，然后是2个UNION SELECT语句，但是
我有多远？表格各方面完全相同
包括索引;数据可能相同（相同的数据可能在Spring.Students和Summer.Students中），然而，这并不总是
的情况。 Spring.Students.id列不会与
Summer.Students.id共享相同的值，因为数据可以在单独的序列中输入
即使两个表中的数据完全相同也是如此。
我和INTERSECT / UNION走在正确的轨道上或者你推荐什么？
Thanx
Phil
MySQL 3.23.58 - 4.0.17 (yep, several database server instances, don''t
ask)
I have database Spring with table Students
I have database Summer with table Students
I am tasked to produce a query of all students in both tables with no
duplicates. No clue whatsoever.
I''m thinking an INTERSECT followed by 2 UNION SELECT statements, but
how far off am I? The tables are absolutely identical in every way
including indexes; the data MIGHT be identical (same data might be in
Spring.Students and Summer.Students), however, that won''t always be the
case. The column Spring.Students.id will not share the same value as
Summer.Students.id as data can be entered within separate sequences
even if the data in both tables is absolutely identical.
Am I on the right track with INTERSECT/UNION or what do you recommend?
Thanx
Phil
好吧，如果你在一个数据库中有两个表，这会更容易。我认为，比较同一数据库中存在
的表格更简单，更便携。
如有必要，创建第三个数据库，并将学生表从
Spring and Summer复制到第三个数据库。您可以使用mysqldump
工具从每个工具创建单个表的备份，然后使用文本编辑器编辑两个
转储文件，以确保表名称将是
distinct，然后将两个转储恢复到第三个数据库。
这里有一个查询来获取所有学生的集合。让所有在春季和夏季都有b $ b b的学生，然后所有学生只在春季，然后
所有学生只在夏天。这三组没有重叠。
（选择S1.student
来自Spring AS S1 INNER JOIN Summer AS U1 ON S1.student = U1.student）
UNION
（选择S2.student
来自Spring AS S2 LEFT OUTER JOIN夏季AS U2 ON S2.student = U2.student
WHERE U2.student IS NULL）
UNION
（SELECT U3.student
来自Spring AS S3 RIGHT OUTER JOIN Summer AS U3 ON S3.student = U3.student
WHERE S3.student IS NULL）
学生订购;
此致，
Bill K.
Well, this would be easier if you had both tables in one database. I
think it''s more straightforward and portable to compare tables that
exist in the same database.
If necessary, create a third database and copy the Student tables from
Spring and Summer into the third database. You can use the mysqldump
tool to create a backup of a single table from each, then edit the two
dump files with a text editor to make sure the table names will be
distinct, then restore both dumps to the third database.
Here''s one query to get the set of all students. Get all students who
exist in both Spring and Summer, then all students only in Spring, then
all students only in Summer. These three sets have no overlap.
(SELECT S1.student
FROM Spring AS S1 INNER JOIN Summer AS U1 ON S1.student = U1.student)
UNION
(SELECT S2.student
FROM Spring AS S2 LEFT OUTER JOIN Summer AS U2 ON S2.student = U2.student
WHERE U2.student IS NULL)
UNION
(SELECT U3.student
FROM Spring AS S3 RIGHT OUTER JOIN Summer AS U3 ON S3.student = U3.student
WHERE S3.student IS NULL)
ORDER BY student;
Regards,
Bill K.
正确的方法是使用UNION加入：
选择*来自Spring.Students
union
select * from Summer.Students
Markus
The right thing is to use a UNION join:
select * from Spring.Students
union
select * from Summer.Students
Markus
不幸的是，这个选择产生了MySQL语法错误..我应该提到
我们在当前版本中使用MySQL 3.23.58并将
希望升级到明年新版本中的MySQL 4.0+
Phil
Markus Popp写道：
Unfortunately that choice produced a MySQL syntax error.. I should have
mentioned we''re using MySQL 3.23.58 in the current version and will
hopefully be upgrading to MySQL 4.0+ in the new version next year
Phil
Markus Popp wrote:
正确的是使用UNION加入：
选择*来自Spring.Students
联盟
选择*来自Summer.Students
Markus
The right thing is to use a UNION join:
select * from Spring.Students
union
select * from Summer.Students
Markus
这篇关于我需要帮助从两个独立的数据库中选择2个相同的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
