从wordpress（woocommerce）中的两个表中选择不同的值

Question

null

    SELECT a.ID,a.post_title,b.post_id,b.meta_value FROM 
    (
        SELECT DISTINCT ID FROM wp_posts
        UNION
        SELECT DISTINCT post_id FROM wp_postmeta
    ) x LEFT JOIN
    wp_posts a ON x.ID = a.ID LEFT JOIN
    wp_postmeta b ON x.post_id = b.post_id

我什么也想象不到，有人来帮我吗？

----编辑----

@拉胡尔，

    SELECT a.ID, a.post_title, b.post_id, b.meta_value FROM wp_posts a
    LEFT JOIN (

        SELECT DISTINCT ID
        FROM wp_posts
    )xx ON a.ID = xx.ID
    LEFT JOIN (

        SELECT DISTINCT post_id
        FROM wp_postmeta
    )yy ON a.ID = yy.post_id
    LEFT JOIN wp_postmeta b ON b.post_id = yy.post_id
    WHERE a.post_type = 'product'
    AND a.post_status = 'publish'
    LIMIT 0 , 30

141  测试粉笔 ；   141        123

141  测试粉彩 ；   141        可见

Answer 1

是，因为在子选择结果集中没有post_id列。您需要更改查询并将它们分开，如

SELECT a.ID,a.post_title,b.post_id,b.meta_value FROM
wp_posts a LEFT JOIN 
( SELECT DISTINCT ID FROM wp_posts ) xx ON a.ID = xx.ID
LEFT JOIN ( SELECT DISTINCT post_id FROM wp_postmeta ) yy 
ON a.some_common_column = yy.some_common_column
LEFT JOIN wp_postmeta b ON b.post_id = yy.post_id;

编辑：

根据您编辑的帖子；您需要获得行号，并获得非重复的行号，如下所示

SELECT ID, post_title, post_id, meta_value
FROM ( 
SELECT a.ID,
a.post_title,
b.post_id,
b.meta_value,
(@row_number:=@row_number + 1) AS rownum 
FROM wp_posts a
JOIN (SELECT @row_number:=0) r 
LEFT JOIN wp_postmeta b ON a.ID = b.post_id ) xxx
WHERE rownum = 1;