两个具有不同列的表的SQL联合 [英] SQL union of two tables with different columns

问题描述

我想从具有不同列名的两个表中获得一行结果集，每个表中的每一行一行。

I want to get one result set of rows back from two tables with different column names, one line per line in each table.

结果应如下所示，空白可以为null，第二半部分的team_id来自coach_id：

Result should look like this, blanks can be null, team_id in 2nd half comes from coach_id:

-----------------------------------------
player_id | team_id | score | improvement
-----------------------------------------
11          20         5
11          21         4
12          22         2
12          23         2
11          20                   5
11          21                   6
12          21                   5
13          23                   10

以下是模式：

CREATE TABLE coaches
    (`id` int, `team_id` int)
;

INSERT INTO coaches
    (`id`, `team_id`)
VALUES
    (1, 20),
    (2, 21),
    (3, 22),
    (4, 23)
;

CREATE TABLE players
 (`id` int, `player_id` int);


INSERT INTO players
(`id`, `player_id`)
VALUES
(1,11),
(2,12),
(3,13),
(4,14)
;
CREATE TABLE games
    (`id` int, `player_id` int, `team_id` int, `score` int)
;

INSERT INTO games
    (`id`, `player_id`, `team_id`, `score`)
VALUES
    (1, 11, 20, 5),
    (2, 11, 21, 4),
    (3, 12, 22, 2),
    (4, 12, 23, 2)
;

CREATE TABLE sessions
    (`id` int, `player_id` int, `coach_id` int, `improvement` int)
;

INSERT INTO sessions
      (`id`, `player_id`, `coach_id`, `improvement`)
VALUES
    (1, 11, 1, 5),
    (2, 11, 2, 6),
    (3, 12, 2, 5),
    (4, 13, 4, 10)
;

对此进行了尝试，但并没有真正接近：

Tried this, but it wasn't really close:

SELECT tweets.player_id
      ,tweets.team_id
      ,follows.coach_id 
FROM tweets FULL OUTER JOIN follows ON (1 = 0);

推荐答案

nuullullry this

nulnullry this

 SELECT player_id
    ,team_id
    ,score
    ,NULL AS improvement
FROM games
UNION All
SELECT sessions.player_id
    ,coaches.team_id
    ,NULL AS score
    ,sessions.improvement
FROM sessions
INNER JOIN coaches ON coaches.id = sessions.coach_id

这篇关于两个具有不同列的表的SQL联合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！