我有两个MySQL表,“u字符”和“uplace”,这两个表都有列“u字符”。这两个表中的u字符列之间有一些重叠,但有些值对每个表都是唯一的。每个表都有用于“插曲”和“行”的列,但“uplace”表也有"place“列。这将创建此模式的一个简单版本:
CREATE TABLE ucharacters (
ucharacter VARCHAR(50),
episode INT(2),
line INT(6)
);
CREATE TABLE uplaces (
ucharacter VARCHAR(50),
episode INT(2),
line INT(6),
place VARCHAR(40)
);
INSERT INTO ucharacters VALUES ('Molly', 4, 123);
INSERT INTO ucharacters VALUES ('Leo', 5, 567);
INSERT INTO uplaces VALUES ('Leo', 5, NULL, 'Athens');
我希望从一个临时表中进行选择,该表将这些数据组合在一起,以便保留每个表中的每一行,并在适当情况下使用空值。其结果将是:
UCHARACTER EPISODE LINE PLACE
Molly 4 123 NULL
Leo 5 567 NULL
Leo 7 NULL Athens
下面是我的代码:
SELECT tabkey.ucharacter,
ucharacters.episode, ucharacters.line,
uplaces.episode, uplaces.line, uplaces.place
FROM
(SELECT ucharacters.ucharacter FROM ucharacters
UNION ALL
SELECT uplaces.ucharacter FROM uplaces) as tabkey
LEFT JOIN
ucharacters on tabkey.ucharacter = ucharacters.ucharacter
LEFT JOIN
uplaces on tabkey.ucharacter = uplaces.ucharacter;
这对于第一行(Molly,4,123,NULL)很好,但随后给出了两行相同的行(Leo,5,567,雅典),合并了这两个表。我怎样才能得到上面输入的结果呢?
http://sqlfiddle.com/#!2/a7013e/1
谢谢!
发布于 2014-08-18 16:51:21
我希望从一个临时表中进行选择,该表将这些数据组合在一起,以便保留每个表中的每一行>,并酌情使用空值。其结果将是:
如果你只想这么做,你就不能简单地使用下面的查询吗?
SELECT ucharacter, episode, line, null FROM ucharacters
UNION ALL
SELECT ucharacter, episode, line, place FROM uplaces;
https://stackoverflow.com/questions/25373992
复制相似问题