MySQL:在子目录中使用JOIN语句

Question

可以用显式语法([INER|LEFT|OUTER|..]JOIN…ON…(或在WHERE语句中指定条件来表示联接。

如何引用外部FROM语句中调用的表并将其加入子查询？

在这种情况下，我指的是别名为p 的表

SELECT 
p.id,
p.post_title,
(SELECT 
GROUP_CONCAT(DISTINCT wp_terms.name
SEPARATOR ',')
FROM
p
JOIN
wp_term_relationships ON (p.id = wp_term_relationships.object_id)
LEFT JOIN
wp_term_taxonomy ON (wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id)
LEFT JOIN
wp_terms ON (wp_terms.term_id = wp_term_taxonomy.term_id)
AND wp_term_taxonomy.taxonomy IN ('post_tag' , 'category'))
FROM
`post_senza_revisioni` p
WHERE
p.post_type = 'post'
AND p.post_status = 'publish'
AND p.post_parent = 0
GROUP BY id , post_title

Answer 1

我很确定你想要一个相关的子查询：

SELECT p.id, p.post_title,
(SELECT GROUP_CONCAT(DISTINCT wp_terms.nameSEPARATOR ',')
FROM wp_term_relationships tr
wp_term_taxonomy tt
ON tr.term_taxonomy_id = tt.term_taxonomy_id LEFT JOIN
wp_terms t
ON t.term_id = tt.term_id AND
tt.taxonomy IN ('post_tag' , 'category'))
WHERE p.id = r.object_id
)
FROM `post_senza_revisioni` p
WHERE p.post_type = 'post' AND
p.post_status = 'publish' AND
p.post_parent = 0;

我怀疑外部查询中是否需要GROUP BY，所以我删除了它。如果p表中确实有重复的id值，那么可以将其添加回中——尽管这表明id对列来说是一个非常糟糕的名称。

Answer 2

在不了解数据的情况下，我认为您应该能够在FROM子句中使用子查询，在ID上联接，然后从子查询中选择*或您想要的列。

SELECT 
p.id,
p.post_title,
sub.*
FROM
`post_senza_revisioni` p
JOIN    (SELECT 
GROUP_CONCAT(DISTINCT wp_terms.name
SEPARATOR ',')
FROM
p
JOIN
wp_term_relationships ON (p.id = wp_term_relationships.object_id)
LEFT JOIN
wp_term_taxonomy ON (wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id)
LEFT JOIN
wp_terms ON (wp_terms.term_id = wp_term_taxonomy.term_id)
AND wp_term_taxonomy.taxonomy IN ('post_tag' , 'category')
) sub
ON p.id = sub.id
WHERE
p.post_type = 'post'
AND p.post_status = 'publish'
AND p.post_parent = 0
GROUP BY id , post_title