如何在主查询中添加以选择子查询的多个值 - How can I add to select multiple values of subquery in main query

How can I add to select multiple values of subquery in main query

本文关键字：查询选择添加 | 更新日期: 2023-09-27

Query ：

SELECT *
FROM `wp_posts`
WHERE `ID`
IN GROUP_CONCAT(
(
SELECT post_id
FROM `wp_postmeta`
WHERE `meta_key` = 'friday_happy_hour_start_time'
AND `meta_value` <= '9'
) && (
SELECT post_id
FROM `wp_postmeta`
WHERE `meta_key` = 'friday_happy_hour_end_time'
AND `meta_value` >= '9'
)
)

子查询 1 和 2 有多个结果，所以我使用了 GROUP_CONCAT((。但它不起作用。我想要所有 9 等于或介于 meta_value ('s( 之间的帖子 meta_key = 'friday_happy_hour_end_time' 和 meta_key = 'friday_happy_hour_start_time'。请为我提供解决方案

无需使用group_concat和多个子查询这可以通过将wp_postmeta与帖子连接两次并定义您的标准来轻松完成

SELECT p.* 
FROM `wp_posts` p
JOIN wp_postmeta m ON(p.ID = m.post_id)
JOIN wp_postmeta m1 ON(p.ID = m1.post_id)
WHERE m.`meta_key` = 'friday_happy_hour_start_time' 
AND m1.`meta_key` = 'friday_happy_hour_end_time' 
AND m.`meta_value` <= '9' 
AND m1.`meta_value` >= '9'

我认为与其使用group_contact，不如在单个子查询中实现您想要的。括号是这里的重要因素。

SELECT *
FROM `wp_posts`
WHERE `ID`
IN (
SELECT post_id
FROM `wp_postmeta`
WHERE 
(`meta_key` = 'friday_happy_hour_start_time' AND `meta_value` <= '9')  
OR 
(`meta_key` = 'friday_happy_hour_end_time' AND `meta_value` >= '9')
)-- End of IN

SELECT *
FROM `wp_posts`
WHERE `ID` IN (
    SELECT DISTINCT post_id
    FROM `wp_postmeta`
    WHERE `meta_key` = 'friday_happy_hour_start_time'
    AND `meta_value` <= '9'
    UNION DISTINCT 
    SELECT DISTINCT post_id
    FROM `wp_postmeta`
    WHERE `meta_key` = 'friday_happy_hour_end_time'
    AND `meta_value` >= '9'
)