mysql - SQL查询联合最佳实践

Question

我试图在从表中提取数据时对数据进行分类。数据通过每行中的“valid_from”和“valid_to”日期字段保存历史记录。

我想提取数据并按如下方式限定它:

NEW => WHERE CURRENT_DATE BETWEEN valid_from AND (valid_from + 1 MOTNH)
CURRENT => WHERE CURRENT_DATE > (valid_from + 1 MOTNH)
RETIRED => the rest of the rows, so the "dish_id" items not in the tables above, BUT 
           returning the values from the row containing MAX(valid_to) date.

我这样做是最好/更有效的方法吗？提前致谢!

SELECT
    menu_table.dish_id,
    menu_table.dish_title,
    menu_table.marketing_desc,
    menu_table_status.rrp_inc_gst,
    menu_table_status.lowest_rrp,
    menu_table_status.highest_rrp,
    'n' as status
FROM 
    menu_table,
    menu_table_status
WHERE
    CURRENT_DATE BETWEEN menu_table_status.valid_from_date AND DATE_ADD(menu_table_status.valid_from_date, INTERVAL 1 MONTH) 
    AND CURRENT_DATE < menu_table_status.valid_to_date
    AND menu_table.dish_id = menu_table_status.dish_id
UNION
SELECT
    menu_table.dish_id,
    menu_table.dish_title,
    menu_table.marketing_desc,
    menu_table_status.rrp_inc_gst,
    menu_table_status.lowest_rrp,
    menu_table_status.highest_rrp,
    'c' as status
FROM 
    menu_table,
    menu_table_status
WHERE
    CURRENT_DATE > DATE_ADD(menu_table_status.valid_from_date, INTERVAL 1 MONTH) 
    AND CURRENT_DATE < menu_table_status.valid_to_date
    AND menu_table.dish_id = menu_table_status.dish_id              
UNION
SELECT
    menu_table.dish_id,
    menu_table.dish_title,
    menu_table.marketing_desc,
    menu_table_status.rrp_inc_gst,
    menu_table_status.lowest_rrp,
    menu_table_status.highest_rrp,
    'r' as status
FROM 
    menu_table,
    menu_table_status
WHERE
    menu_table_status.valid_to_date
    AND menu_table.dish_id NOT IN (SELECT inside_table1.dish_id
                       FROM menu_table_status AS inside_table1
                       WHERE CURRENT_DATE BETWEEN inside_table1.valid_from_date 
                                                          AND inside_table1.valid_to_date)
    AND menu_table_status.valid_to_date = (SELECT MAX(inside_table2.valid_to_date)
                           FROM menu_table_status AS inside_table2
                           WHERE inside_table2.dish_id = menu_table_status.dish_id)
    AND menu_table.dish_id = menu_table_status.dish_id

Answer 1

如果不仔细看的话，您肯定会混淆最后一个 where 子句中的日期。无论如何，你的陈述太复杂了。只需选择所有记录(无论如何您都想要这样做)并查看每个记录的日期来决定给出的状态:

SELECT
  menu_table.dish_id,
  menu_table.dish_title,
  menu_table.marketing_desc,
  menu_table_status.rrp_inc_gst,
  menu_table_status.lowest_rrp,
  menu_table_status.highest_rrp,
  CASE 
    WHEN
      CURRENT_DATE BETWEEN menu_table_status.valid_from_date AND DATE_ADD(menu_table_status.valid_from_date, INTERVAL 1 MONTH) 
      AND CURRENT_DATE < menu_table_status.valid_to_date
    THEN 'n' 
    WHEN
      CURRENT_DATE > DATE_ADD(menu_table_status.valid_from_date, INTERVAL 1 MONTH) 
      AND CURRENT_DATE < menu_table_status.valid_to_date
    THEN 'c'
    ELSE 'r'
  END as status
FROM menu_table
INNER JOIN menu_table_status ON menu_table.dish_id = menu_table_status.dish_id;

顺便说一句:请不要使用旧的连接语法来列出所有以逗号分隔的表。它很容易出错，这就是为什么从 1992 年开始就出现了"new"语法。

编辑:我发现了你的错误。而不是检查 CURRENT_DATE < menu_table_status.valid_to_date你检查menu_table_status.valid_to_date只有这样才能将日期视为 bool 值，这在 MySQL 中是特殊的。

还有一点:当联合不同的集合时(你的集合是因为​​不同的状态字母)，请使用 UNION ALL，而不是 UNION。 UNION 用于删除重复项。当您知道没有重复记录时，为什么还要让数据库管理系统检查您的所有记录？