SQL：一个查询中有两个选择语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/31979008/
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
SQL: Two select statements in one query
提问by Berbatov
I want to select information from two SQL tables within one query, the information is unrelated though, so no potential joints exist.
我想从一个查询中的两个 SQL 表中选择信息，尽管这些信息是不相关的，因此不存在潜在的联合。
An example could be the following setup.
一个例子可能是以下设置。
tblMadrid
马德里
id | name | games | goals
1 | ronaldo | 100 | 100
2 | benzema | 50 | 25
3 | bale | 75 | 50
4 | kroos | 80 | 10
tblBarcelona
tbl巴塞罗那
id | name | games | goals
1 | neymar | 60 | 25
2 | messi | 150 | 200
3 | suarez | 80 | 80
4 | iniesta | 40 | 5
I want to have a query that gives me the following:
我想要一个查询，给我以下内容：
name | games | goals
messi | 150 | 200
ronaldo | 100 | 100
I tried to follow this logic: Multiple select statements in Single querybut the following code did not work:
我试图遵循这个逻辑：单个查询中的多个选择语句，但以下代码不起作用：
USE Liga_BBVA
SELECT (SELECT name,
games,
goals
FROM tblMadrid
WHERE name = 'ronaldo') AS table_a,
(SELECT name,
games,
goals
FROM tblBarcelona
WHERE name = 'messi') AS table_b
ORDER BY goals
Any advice on this one? Thanks Info: The football stuff is just a simplifying example. In reality it is not possible to put both tables into one and have a new "team" column. The two tables have completely different structures, but I need something that matches the characteristics of this example.
关于这个有什么建议吗？感谢信息：足球的东西只是一个简化的例子。实际上，不可能将两个表合二为一并拥有一个新的“团队”列。这两个表具有完全不同的结构，但我需要与此示例的特征相匹配的内容。
回答by Ray Kiddy
You can do something like this:
你可以这样做：
(SELECT
name, games, goals
FROM tblMadrid WHERE name = 'ronaldo')
UNION
(SELECT
name, games, goals
FROM tblBarcelona WHERE name = 'messi')
ORDER BY goals;
See, for example: https://dev.mysql.com/doc/refman/5.0/en/union.html
回答by user206168
If you like to keep records separate and not do the union.
Try query below
如果您喜欢将记录分开而不是合并。
试试下面的查询
SELECT (SELECT name,
games,
goals
FROM tblMadrid
WHERE name = 'ronaldo') AS table_a,
(SELECT name,
games,
goals
FROM tblBarcelona
WHERE name = 'messi') AS table_b
FROM DUAL
回答by oxymoron
The UNION statement is your friend:
UNION 语句是你的朋友：
SELECT a.playername, a.games, a.goals
FROM tblMadrid as a
WHERE a.playername = "ronaldo"
UNION
SELECT b.playername, b.games, b.goals
FROM tblBarcelona as b
WHERE b.playername = "messi"
ORDER BY goals;
回答by Wyatt Shipman
You can union the queries as long as the columns match.
只要列匹配，您就可以联合查询。
SELECT name,
games,
goals
FROM tblMadrid
WHERE id = 1
UNION ALL
SELECT name,
games,
goals
FROM tblBarcelona
WHERE id = 2
回答by Mathusuthanan
select name, games, goals
from tblMadrid where name = 'ronaldo'
union
select name, games, goals
from tblBarcelona where name = 'messi'
ORDER BY goals
回答by Amandeep Singh
Using union will help in this case.
在这种情况下，使用 union 会有所帮助。
You can also use join on a condition that always returns true and is not related to data in these tables.See below
您还可以在始终返回 true 且与这些表中的数据无关的条件上使用 join。见下文
select tmd .name,tbc.goals from tblMadrid tmd join tblBarcelona tbc on 1=1;
select tmd .name,tbc.goals from tblMadrid tmd 加入 tblBarcelona tbc on 1=1;
回答by zedfoxus
You can combine data from the two tables, order by goals highest first and then choose the top two like this:
您可以合并两个表中的数据，首先按目标最高排序，然后像这样选择前两个：
MySQL
MySQL
select *
from (
select * from tblMadrid
union all
select * from tblBarcelona
) alldata
order by goals desc
limit 0,2;
SQL Server
数据库服务器
select top 2 *
from (
select * from tblMadrid
union all
select * from tblBarcelona
) alldata
order by goals desc;
If you only want Messi and Ronaldo
如果你只想要梅西和C罗
select * from tblBarcelona where name = 'messi'
union all
select * from tblMadrid where name = 'ronaldo'
To ensure that messi is at the top of the result, you can do something like this:
为了确保梅西在结果的顶部，您可以执行以下操作：
select * from (
select * from tblBarcelona where name = 'messi'
union all
select * from tblMadrid where name = 'ronaldo'
) stars
order by name;
回答by Akash
You can use UNION in this case
在这种情况下，您可以使用 UNION
select id, name, games, goals from tblMadrid
union
select id, name, games, goals from tblBarcelona
you jsut have to maintain order of selected columns ie id, name, games, goals in both SQLs
您必须维护所选列的顺序，即两个 SQL 中的 ID、名称、游戏、目标
