如何在一行中看到结果?——我的id重复在两个不同的行——

Question

(SELECT
id, post_date
FROM mln_posts WHERE post_type = 'shop_order')
UNION
(SELECT
post_id, meta_value
FROM mln_postmeta WHERE meta_key = '_order_total')
ORDER BY id

输入图片描述

Answer 1

包装您的查询，然后使用GROUP BY

SELECT * FROM 
(SELECT 
id, 
post_date 
FROM 
mln_posts 
WHERE post_type = 'shop_order'
UNION 
SELECT 
post_id, 
meta_value 
FROM 
mln_postmeta 
WHERE 
meta_key = '_order_total'
) final
GROUP BY id
ORDER BY id

Answer 2

这是一个技巧，它将创建一个虚构的列供您连接第二个子查询。

SELECT t1.id, t1.post_date, t2.post_id, t2.meta_value
(SELECT
id, post_date, 1 as col
FROM mln_posts WHERE post_type = 'shop_order'
ORDER BY id DESC LIMIT 1) t1
LEFT JOIN 
(SELECT
post_id, meta_value, 1 as col
FROM mln_postmeta WHERE meta_key = '_order_total'
ORDER BY id DESC LIMIT 1) t2 ON t2.col = t1.col