SQL：一个查询中有两个select语句

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

   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 query but the following code did not work:

我试图遵循这个逻辑:单个查询中的多个select语句,但以下代码不起作用:

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.

对此有何建议?谢谢信息:足球的东西只是一个简化的例子。实际上,不可能将两个表放在一个并且有一个新的“团队”列。这两个表有完全不同的结构,但我需要一些与这个例子的特征相匹配的东西。

8 个解决方案

#1

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

例如,请参阅:https://dev.mysql.com/doc/refman/5.0/en/union.html

#2

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;

#3

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

#4

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

#5

You can combine data from the two tables, order by goals highest first and then choose the top two like this:

您可以组合来自两个表的数据,按目标最高排序,然后选择前两个,如下所示:

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

如果你只想要梅西和罗纳尔多

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:

为了确保将messi放在结果的顶端,您可以执行以下操作:

select * from (
  select * from tblBarcelona where name = 'messi'
  union all
  select * from tblMadrid where name = 'ronaldo'
) stars
order by name;

#6

select name, games, goals
from tblMadrid where name = 'ronaldo'
union
select name, games, goals
from tblBarcelona where name = 'messi'
ORDER  BY goals

#7

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;

选择tmd .name,tbc.goals来自tblMadrid tmd join tblBarcelona tbc on 1 = 1;

#8

-1

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,name,games,goals

#1