如何在此查询中模拟完全外连接? [英] How to emulate full outer join in this query?
问题描述
所以显然 mySQL 不支持完全外连接，但这确实是我所需要的.我看过很多关于用工会模拟它的博客文章和文章，但这会删除重复项.有人可以帮我从这里出去吗?
So apparently mySQL doesn't support full outer join, but it's really what I need. I've seen a bunch of blog posts and articles about emulating it with unions, but that removes duplicates. Can anyone help me out here?
这是包含完整外连接的查询(此处仅进行了多个连接中的一个)；我如何将其翻译成 mySQL 能够理解的内容?
Here's the query containing the full outer join (only one of a number of joins being done here); how would I translate that into something mySQL understands?
SELECT DISTINCT n.title, nr.teaser, n.nid, DATE_FORMAT(FROM_UNIXTIME(n.created), '%M %e, %Y') AS date, f.filepath AS image,tn_img.tid as image_tid
FROM node n
JOIN node_revisions nr ON n.nid = nr.nid
LEFT JOIN content_field_related_images cfri ON (n.nid = cfri.nid AND cfri.delta = 0)
LEFT JOIN content_field_att_file cfaf ON cfri.field_related_images_nid = cfaf.nid
LEFT JOIN files f ON cfaf.field_att_file_fid = f.fid
JOIN term_node tn2 ON n.nid = tn2.nid
FULL OUTER JOIN term_node tn_img ON cfri.field_related_images_nid = tn_img.nid
WHERE n.status = 1
AND n.type = 'article'
AND nr.body LIKE '%kimberly-clark%'
AND tn2.tid = 143
ORDER BY n.created DESC LIMIT 3
推荐答案
你基本上做了一个 LEFT 和 RIGHT JOIN 的联合.
You basically do a Union of LEFT and RIGHT JOIN.
您实际上有一个有趣的问题，因为您还想将行数限制为 3.要解决这个问题，您需要
You actually have interesting wrinkle in that you also want to limit the rows to 3. To solve that you need to
- 同时限制左"和右"选择 3
- 然后在内联视图中使用 UNION 的结果
- 然后再次将联合限制为 3
UPDATE 遗憾的是，除非我弄错了，您不能直接在 UNION 中执行此操作，因此您需要在 UNION 之前添加另一层内联视图
UPDATE Sadly, unless I'm mistaken you can't do this directly in a UNION so you need to add another layer of inline views prior to the UNION
UNION 内部的 LIMITS 将提供一些性能优势，然后之后的限制将为您提供正确的结果.
The LIMITS inside the UNION will offer some performance benefit and then the limit after will give you the correct results.
SELECT title,
teaser,
nid,
DATE,
image,
image_tid
FROM (SELECT title,
teaser,
nid,
DATE,
image,
image_tid,
created
FROM (SELECT DISTINCT n.title,
nr.teaser,
n.nid,
Date_format(From_unixtime(n.created),
'%M %e, %Y') AS
DATE,
f.filepath
AS
image,
tn_img.tid
AS
image_tid
,
n.created
FROM node n
JOIN node_revisions nr
ON n.nid = nr.nid
LEFT JOIN content_field_related_images cfri
ON ( n.nid = cfri.nid
AND cfri.delta = 0 )
LEFT JOIN content_field_att_file cfaf
ON cfri.field_related_images_nid = cfaf.nid
LEFT JOIN files f
ON cfaf.field_att_file_fid = f.fid
JOIN term_node tn2
ON n.nid = tn2.nid
LEFT OUTER JOIN term_node tn_img
ON cfri.field_related_images_nid = tn_img.nid
WHERE n.status = 1
AND n.TYPE = 'article'
AND nr.body LIKE '%kimberly-clark%'
AND tn2.tid = 143
ORDER BY n.created DESC
LIMIT 3) tleft
UNION
SELECT title,
teaser,
nid,
DATE,
image,
image_tid,
created
FROM (SELECT DISTINCT n.title,
nr.teaser,
n.nid,
Date_format(From_unixtime(n.created),
'%M %e, %Y') AS
DATE,
f.filepath
AS
image,
tn_img.tid
AS
image_tid
,
n.created
FROM node n
JOIN node_revisions nr
ON n.nid = nr.nid
LEFT JOIN content_field_related_images cfri
ON ( n.nid = cfri.nid
AND cfri.delta = 0 )
LEFT JOIN content_field_att_file cfaf
ON cfri.field_related_images_nid = cfaf.nid
LEFT JOIN files f
ON cfaf.field_att_file_fid = f.fid
JOIN term_node tn2
ON n.nid = tn2.nid
RIGHT OUTER JOIN term_node tn_img
ON cfri.field_related_images_nid = tn_img.nid
WHERE n.status = 1
AND n.TYPE = 'article'
AND nr.body LIKE '%kimberly-clark%'
AND tn2.tid = 143
ORDER BY n.created DESC
LIMIT 3) tright) t
ORDER BY created DESC
LIMIT 3
更新使用 spencer7593 和 ypercube 建议这里是使用两个 UNION ALL 语句且没有内联视图的替代方法.
UPDATE Using spencer7593 and ypercube suggestions here's an alternative approach using two UNION ALL statements and no inline views.
SELECT DISTINCT n.created,
n.title,
nr.teaser,
n.nid,
Date_format(From_unixtime(n.created), '%M %e, %Y') AS DATE,
f.filepath AS image,
tn_img.tid AS image_tid
FROM node n
JOIN node_revisions nr
ON n.nid = nr.nid
LEFT JOIN content_field_related_images cfri
ON ( n.nid = cfri.nid
AND cfri.delta = 0 )
LEFT JOIN content_field_att_file cfaf
ON cfri.field_related_images_nid = cfaf.nid
LEFT JOIN files f
ON cfaf.field_att_file_fid = f.fid
JOIN term_node tn2
ON n.nid = tn2.nid
LEFT OUTER JOIN term_node tn_img
ON cfri.field_related_images_nid = tn_img.nid
WHERE n.status = 1
AND n.TYPE = 'article'
AND nr.body LIKE '%kimberly-clark%'
AND tn2.tid = 143
UNION ALL
SELECT DISTINCT n.created,
n.title,
nr.teaser,
n.nid,
Date_format(From_unixtime(n.created), '%M %e, %Y') AS DATE,
f.filepath AS image,
tn_img.tid AS image_tid
FROM node n
JOIN node_revisions nr
ON n.nid = nr.nid
LEFT JOIN content_field_related_images cfri
ON ( n.nid = cfri.nid
AND cfri.delta = 0 )
LEFT JOIN content_field_att_file cfaf
ON cfri.field_related_images_nid = cfaf.nid
LEFT JOIN files f
ON cfaf.field_att_file_fid = f.fid
JOIN term_node tn2
ON n.nid = tn2.nid
RIGHT JOIN term_node tn_img
ON cfri.field_related_images_nid = tn_img.nid
WHERE n.status = 1
AND n.TYPE = 'article'
AND nr.body LIKE '%kimberly-clark%'
AND tn2.tid = 143
AND cfri.field_related_images_nid IS NULL
ORDER BY 1 DESC
LIMIT
3
这篇关于如何在此查询中模拟完全外连接?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
