我正在尝试查询WordPress数据库以导出帖子及其内容的列表。我一切正常，但无法弄清楚如何包括附件中的图像。我不确定如何在同一表格中将孩子与父母联系起来。
父标识符位于名为ID的列中，子标识符位于称为post_parent的列中。我尝试连接的值在名为guid的列中，该列包含图像的完整URL（正是我所需要的）。
wp_posts表结构：
ID post_parent guid post_type
1 0 http://... attachment
2 1 http://... attachment
3 1 http://... attachment
4 1 http://... attachment
这是我目前的查询：
SELECT DISTINCT
post_title
,(SELECT meta_value FROM wap_postmeta WHERE wap_postmeta.meta_key = 'car-year' AND wap_postmeta.post_id = wap_posts.ID) AS "Year"
,(SELECT group_concat(wap_terms.name separator ', ')
FROM wap_terms
INNER JOIN wap_term_taxonomy ON wap_terms.term_id = wap_term_taxonomy.term_id
INNER JOIN wap_term_relationships wpr ON wpr.term_taxonomy_id = wap_term_taxonomy.term_taxonomy_id
WHERE taxonomy= 'car-manufacturer' AND wap_posts.ID = wpr.object_id
) AS "Manufacturer"
,(SELECT meta_value FROM wap_postmeta WHERE wap_postmeta.meta_key = 'car-model' AND wap_postmeta.post_id = wap_posts.ID) AS "Model"
,(SELECT meta_value FROM wap_postmeta WHERE wap_postmeta.meta_key = 'car-trim' AND wap_postmeta.post_id = wap_posts.ID) AS "Trim"
,(SELECT meta_value FROM wap_postmeta WHERE wap_postmeta.meta_key = 'car-vin' AND wap_postmeta.post_id = wap_posts.ID) AS "VIN"
,(SELECT meta_value FROM wap_postmeta WHERE wap_postmeta.meta_key = 'car-odometer' AND wap_postmeta.post_id = wap_posts.ID) AS "Odometer"
,(SELECT meta_value FROM wap_postmeta WHERE wap_postmeta.meta_key = 'car-price' AND wap_postmeta.post_id = wap_posts.ID) AS "Price"
,(SELECT meta_value FROM wap_postmeta WHERE wap_postmeta.meta_key = 'car-extcolor' AND wap_postmeta.post_id = wap_posts.ID) AS "Exterior Color"
,(SELECT meta_value FROM wap_postmeta WHERE wap_postmeta.meta_key = 'car-intcolor' AND wap_postmeta.post_id = wap_posts.ID) AS "Interior Color"
,(SELECT meta_value FROM wap_postmeta WHERE wap_postmeta.meta_key = 'car-transmission' AND wap_postmeta.post_id = wap_posts.ID) AS "Transmission"
,(SELECT meta_value FROM wap_postmeta WHERE wap_postmeta.meta_key = 'car-engine' AND wap_postmeta.post_id = wap_posts.ID) AS "Engine"
,(SELECT meta_value FROM wap_postmeta WHERE wap_postmeta.meta_key = 'car-fuel' AND wap_postmeta.post_id = wap_posts.ID) AS "Fuel Type"
,(SELECT meta_value FROM wap_postmeta WHERE wap_postmeta.meta_key = 'car-drivetrain' AND wap_postmeta.post_id = wap_posts.ID) AS "Drivetrain"
,(SELECT meta_value FROM wap_postmeta WHERE wap_postmeta.meta_key = 'car-doors' AND wap_postmeta.post_id = wap_posts.ID) AS "car-doors"
,(SELECT meta_value FROM wap_postmeta WHERE wap_postmeta.meta_key = 'car-stockno' AND wap_postmeta.post_id = wap_posts.ID) AS "Stock #"
,(SELECT group_concat(wap_terms.name separator ', ')
FROM wap_terms
INNER JOIN wap_term_taxonomy ON wap_terms.term_id = wap_term_taxonomy.term_id
INNER JOIN wap_term_relationships wpr ON wpr.term_taxonomy_id = wap_term_taxonomy.term_taxonomy_id
WHERE taxonomy= 'car-feature' AND wap_posts.ID = wpr.object_id
) AS "Features"
,(SELECT group_concat(wap_terms.name separator ', ')
FROM wap_terms
INNER JOIN wap_term_taxonomy ON wap_terms.term_id = wap_term_taxonomy.term_id
INNER JOIN wap_term_relationships wpr ON wpr.term_taxonomy_id = wap_term_taxonomy.term_taxonomy_id
WHERE taxonomy= 'car-body-type' AND wap_posts.ID = wpr.object_id
) AS "Body Type"
-- MY PROBLEM STARTS HERE
,(SELECT group_concat(wap_posts.guid separator ', ')
FROM wap_posts
WHERE wap_posts.post_type = 'attachment' AND wap_posts.post_parent = wap_posts.ID
) AS "Images"
-- MY PROBLEM ENDS HERE
FROM wap_posts
WHERE post_type = 'vehicle' AND post_status = 'publish'
ORDER BY
post_title
我意识到这将返回NULL，因为附件的post_parent从不匹配其自己的ID。我只是不知道如何告诉查询将post_parent值与父母的ID而不是其自己的ID匹配。
希望我已经发布了足够的信息，让我知道是否应该包含其他数据。谢谢阅读！
最好的祝福，
杰伊
最佳答案
您可以对同一张表做一个left join以产生所需的结果（假设并非每个帖子都具有附件）
select p.Id,group_concat(c.guid separator ', ') guids
from wap_posts p
left join wap_posts c on (c.post_parent = p.id)
http://sqlfiddle.com/#!2/f7522/1/0