我有个问题
SELECT
d.GUID, w.word
FROM
dictionary d
JOIN
word w ON w.ID = d.ID_word
WHERE
w.id_language = #ID#;
如果#id#=1，则返回表
GUID | word ----------- 1 | A 2 | B 3 | C1 3 | C2 4 | D1 4 | D2 5 | E
如果#id#=2，则返回表
GUID | word ----------- 1 | AA 2 | BB1 2 | BB2 3 | CC 4 | DD1 4 | DD2 6 | FF
现在我想合并这两个表/查询，得到一个如下所示的表:
GUID | word1 | word2 -------------------- 1 | A | AA 2 | B | BB1 2 | B | BB2 3 | C1 | CC 3 | C1 | CC 4 | D1 | DD1 4 | D1 | DD2 4 | D2 | DD1 4 | D2 | DD2
所以它基本上是具有相同GUID的行的笛卡尔乘积的表
您不需要子查询:
SELECT d1.GUID, w1.word, w2.word
FROM dictionary d1 JOIN
word w1
ON w1.ID = d1.ID_word JOIN
dictionary d2
ON d2.GUID = d.GUID JOIN
word w2
ON w2.id = d2.ID_word
WHERE w1.id_language = ? AND
w2.id_language = ?;
我认为使用CTE更易读:
WITH dw AS (
SELECT d.GUID, w.word, w.id_language
FROM dictionary d JOIN
word w
ON w.ID = d.ID_word
)
SELECT dw1.GUID, dw1.word, dw2.word
FROM dw dw1 JOIN
dw dw2
ON dw1.GUID = dw2.GUID
WHERE dw1.id_language = ? AND
dw2.id_language = ?;
在这两个示例中（以及您的问题所暗示的），只有一个单词的GUID都被过滤掉了。 这些可以通过调整查询来包含。
select *
from
( -- if #ID# = 1 it returns table
SELECT
d.GUID, w.word
FROM
dictionary d
JOIN
word w ON w.ID = d.ID_word
WHERE
w.id_language = 1
) as t1
join
( -- if #ID# = 2 it returns table
SELECT
d.GUID, w.word
FROM
dictionary d
JOIN
word w ON w.ID = d.ID_word
WHERE
w.id_language = 2
) as t2
-- Cartesian product of the rows with the same GUID
on t1.guid = t2.guid
您可以在GUID上连接您的两个查询:
SELECT q1.GUID, q1.word AS word1, q2.word AS word2
FROM (
SELECT
d.GUID, w.word
FROM
dictionary d
JOIN
word w ON w.ID = d.ID_word
WHERE
w.id_language = 1
) q1
JOIN (
SELECT
d.GUID, w.word
FROM
dictionary d
JOIN
word w ON w.ID = d.ID_word
WHERE
w.id_language = 1
) q2 ON q2.GUID = q1.GUID
