mysql 修复了选择不返回结果的问题

lokaqttq 于 2022-12-17 发布在 Mysql

关注(0)|答案(1)|浏览(103)

这是我之前问的question的延续。
我正在尝试在WordPress数据库上使用原始sql实现一个解决方案
当我运行如下所示的查询时

SELECT *
FROM wp_postmeta
WHERE post_id IN
    (SELECT ID
     FROM wp_posts
     WHERE post_type = 'promotions'
       AND post_status = 'publish')
  AND meta_key IN ('promo_start_date',
                   'promo_end_date')

我得到以下结果

+---------+---------+------------------+------------+
| meta_id | post_id | meta_key         | meta_value |
+---------+---------+------------------+------------+
| 6199874 |   54679 | promo_end_date   | 2023-01-01 |
| 6199876 |   87246 | promo_end_date   | 2023-01-02 |
| 6199878 |   87251 | promo_end_date   | 2023-01-03 |
| 6199880 |   87255 | promo_end_date   | 2023-01-04 |
| 6199882 |   87257 | promo_end_date   | 2023-01-05 |
| 6199873 |   54679 | promo_start_date | 2022-12-14 |
| 6199875 |   87246 | promo_start_date | 2022-12-15 |
| 6199877 |   87251 | promo_start_date | 2022-12-16 |
| 6199879 |   87255 | promo_start_date | 2022-12-17 |
| 6199881 |   87257 | promo_start_date | 2022-12-18 |
+---------+---------+------------------+------------+

但是当我运行以下命令时

SELECT *
FROM wp_postmeta
WHERE post_id IN
    (SELECT ID
     FROM wp_posts
     WHERE post_type = 'promotions'
       AND post_status = 'publish')
  AND (meta_key = 'promo_start_date'
       AND CURRENT_DATE >= CAST(meta_value AS DATE))
  AND (meta_key = 'promo_end_date'
       AND CURRENT_DATE <= CAST(meta_value AS DATE))

得到一个空结果集

Empty set (0.00 sec)

是否有一种方法可以改进无结果查询，以获得反映以下代码片段所指示内容的结果

current_date >= promo_start_date AND current_date <= promo_end_date

mysql

来源：https://stackoverflow.com/questions/74801613/fix-for-select-returning-no-results

1条答案

按热度按时间

egmofgnx1#

WHERE子句一次只作用于一行。根据定义，meta_key在给定行上只能有一个值。同一行上不能同时为'promo_start_date'和'promo_end_date'。因此，检查meta_key是否等于一个值以及meta_key是否等于另一个值将始终为false。
你需要做的是检查meta_key是否等于一个字符串或另一个字符串，你可以根据它的值与其他条件合并。

SELECT *
FROM wp_postmeta
WHERE post_id IN
    (SELECT ID
     FROM wp_posts
     WHERE post_type = 'promotions'
       AND post_status = 'publish')
  AND ((meta_key = 'promo_start_date'
       AND CURRENT_DATE >= CAST(meta_value AS DATE))
  OR (meta_key = 'promo_end_date'
       AND CURRENT_DATE <= CAST(meta_value AS DATE)))

编写相同逻辑的另一种方法：

SELECT *
FROM wp_postmeta
WHERE post_id IN
    (SELECT ID
     FROM wp_posts
     WHERE post_type = 'promotions'
       AND post_status = 'publish')
  AND CASE meta_key 
    WHEN 'promo_start_date'
    THEN CURRENT_DATE >= CAST(meta_value AS DATE)
    WHEN 'promo_end_date'
    THEN CURRENT_DATE <= CAST(meta_value AS DATE)
    ELSE false END

赞(0）回复(0）举报 2022-12-17

我来回答

mysql 修复了选择不返回结果的问题

1条答案

相关问题

热门标签

最新问答