MySQL 为 SELECT 查询合并 2 个表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1129659/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Merge 2 tables for a SELECT query?
提问by nkspartan
First.. here are the two tables I've created (sans irrelevant columns)..
首先.. 这是我创建的两个表（没有不相关的列）。
CREATE TABLE users_history1 (
circuit tinyint(1) unsigned NOT NULL default '0',
userh_season smallint(4) unsigned NOT NULL default '0',
userh_userid int(11) unsigned NOT NULL default '0',
userh_rank varchar(2) NOT NULL default 'D',
userh_wins int(11) NOT NULL default '0',
userh_losses int(11) NOT NULL default '0',
userh_points int(11) NOT NULL default '1000',
KEY (circuit, userh_userid),
KEY (userh_season)
) ENGINE=MyISAM;
CREATE TABLE users_ladders1 (
circuit tinyint(1) unsigned NOT NULL default '0',
userl_userid int(11) unsigned NOT NULL default '0',
userl_rank char(2) NOT NULL default 'D',
userl_wins smallint(3) NOT NULL default '0',
userl_losses smallint(3) NOT NULL default '0',
userl_points smallint(4) unsigned NOT NULL default '1000',
PRIMARY KEY (circuit, userl_userid),
KEY (userl_userid)
) ENGINE=MyISAM;
Some background.. these tables hold data for a competitive ladder where players are compared against each other on an ordered standings by points. users_history1 is a table that contains records stored from previous seasons. users_ladders1 contains records from the current season. I'm trying to create a page on my site where players are ranked on the average points of their previous records and current record. Here is the main standings for a 1v1 ladder: http://vilegaming.com/league.x/standings1/3
一些背景……这些表格保存了一个竞争阶梯的数据，在这个阶梯上，玩家在一个有序的积分榜上相互比较。users_history1 是一个包含前几季存储记录的表。users_ladders1 包含当前季节的记录。我正在尝试在我的网站上创建一个页面，其中根据玩家之前记录和当前记录的平均分进行排名。以下是 1v1 天梯的主要积分榜：http: //vilegaming.com/league.x/standings1/3
I want to select from the database from the two tables an ordered list players depending on their average points from their users_ladders1 and users_history1 records. I really have no idea how to select from two tables in one query, but I'll try, as generic as possible, to illustrate it..
我想从两个表中的数据库中选择一个有序列表玩家，这取决于他们从 users_ladders1 和 users_history1 记录中获得的平均分。我真的不知道如何在一个查询中从两个表中进行选择，但我会尝试尽可能通用，以说明它..
Using hyphens throughout the examples since SO renders it weird.
在整个示例中使用连字符，因为 SO 使它变得奇怪。
SELECT userh-points
FROM users-history1
GROUP BY userh-userid
ORDER BY (total userh-points for the user)
Needs the GROUP BY since some players may have played in multiple previous seasons.
需要 GROUP BY，因为有些球员可能已经打了多个前赛季。
SELECT userl-points
FROM users-ladders1
ORDER BY userl-points
I want to be able to combine both tables in a query so I can get the data in form of rows ordered by total points, and if possible also divide the total points by the number of unique records for the player so I can get the average.
我希望能够在查询中组合两个表，这样我就可以以按总分排序的行的形式获取数据，如果可能的话，还可以将总分除以玩家的唯一记录数，这样我就可以获得平均值.
回答by sgriffinusa
You'll want to use a UNION SELECT:
您需要使用UNION SELECT：
SELECT p.id, COUNT(p.id), SUM(p.points)
FROM (SELECT userh_userid AS id, userh_points AS points
FROM users_history1
UNION SELECT userl_userid, userl_points
FROM users_ladders1) AS p
GROUP BY p.id
The sub query is the important part. It will give you a single table with the results of both the current and history tables combined. You can then select from that table and do COUNT and SUM to get your averages.
子查询是重要的部分。它将为您提供一个表，其中包含当前表和历史表的结果。然后您可以从该表中进行选择并执行 COUNT 和 SUM 以获得您的平均值。
My MySQL syntax is quite rusty, so please excuse it. I haven't had a chance to run this, so I'm not even sure if it executes, but it should be enough to get you started.
我的 MySQL 语法很生疏，所以请原谅。我没有机会运行它，所以我什至不确定它是否执行，但它应该足以让你开始。
回答by sumeet
If you want to merge to table and you want to select particular column from one table and in another table want to select all.
如果要合并到表，并且要从一个表中选择特定列，而在另一个表中要全选。
e.g.
例如
Table name = test1 , test2
query:
询问：
SELECT test1.column1,test1.column2, test2.* FROM test1 ,test2
if you want to merge with particular column
如果要与特定列合并
query:
询问：
SELECT test1.column1,test1.column2, test2.* FROM test1 ,test2 where test2.column3='(what ever condition u want to pass)'
回答by smith jonesh
Select col1 from test1 where id = '1'
union
select * from table2
this one can also used for the joining to tables.
这个也可以用于连接到表。
