数据库查询优化咨询：筛选本周数据但排除当日记录

Hey there! Let's nail this down—you want records from the current work week (Monday to Friday) but need to exclude today's entries, right? Your example makes it clear you’re targeting workdays only, so let’s break down optimized solutions based on common SQL dialects, plus some tips to keep things efficient.

Optimized Queries by SQL Dialect #

The key is to define the current work week (Mon-Fri) and exclude today’s date. Here’s how to do it cleanly:

1. MySQL/MariaDB #

This uses WEEKDAY() to calculate the start and end of the work week, avoiding unnecessary function calls on your date column (which helps with index usage):

WHERE
    -- Keep dates within the current work week (Mon-Fri)
    date_column BETWEEN DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY) 
                    AND DATE_ADD(CURDATE(), INTERVAL (4 - WEEKDAY(CURDATE())) DAY)
    -- Exclude today's records
    AND DATE(date_column) != CURDATE()

• If your date_column is a DATETIME type: Skip the DATE() wrapper on date_column by adjusting the range to include full days (this lets the database use an index if you have one):

  WHERE
      date_column >= DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY)
      AND date_column <= DATE_ADD(DATE_ADD(CURDATE(), INTERVAL (4 - WEEKDAY(CURDATE())) DAY), INTERVAL '23:59:59.999' HOUR_SECOND)
      AND (date_column < CURDATE() OR date_column > DATE_ADD(CURDATE(), INTERVAL '23:59:59.999' HOUR_SECOND))
  

2. PostgreSQL #

Leverage date_trunc to get the start of the week, then add 4 days to reach Friday:

WHERE
    -- Current work week (Mon-Fri)
    date_column BETWEEN date_trunc('week', CURRENT_DATE)::date 
                    AND (date_trunc('week', CURRENT_DATE) + INTERVAL '4 days')::date
    -- Exclude today
    AND date_column != CURRENT_DATE

• For TIMESTAMP columns, use date_trunc('day', date_column) != CURRENT_DATE to ignore time while keeping index compatibility.

3. SQL Server #

Use DATEADD and DATEPART to calculate the work week bounds (note: this assumes your server uses Sunday as the first day of the week—adjust offsets if needed):

WHERE
    -- Current work week (Mon-Fri)
    date_column BETWEEN DATEADD(day, -(DATEPART(weekday, CURRENT_DATE) - 2), CURRENT_DATE)
                    AND DATEADD(day, (5 - DATEPART(weekday, CURRENT_DATE)), CURRENT_DATE)
    -- Exclude today
    AND date_column != CURRENT_DATE

General Optimization Tips #

• Index Usage: If you have an index on date_column, avoid wrapping it in functions (like DATE()) whenever possible. Using range conditions (e.g., >=/<=) lets the database use the index for faster filtering.
• Handle Edge Cases: If your data spans years, add a year check to avoid matching the same week number from a different year (e.g., YEAR(date_column) = YEAR(CURDATE()) in MySQL).
• Test Work Week Logic: Double-check how your database defines weeks—some default to Sunday as the start, so adjust the offset values if your work week starts on Monday.

内容的提问来源于stack exchange，提问作者ViPZoMbie1