如何利用参数化SQL查询实现今年与去年同期两周数据的对比？

Got it, let's work through this problem. You need to compare a two-week window from this year with the exact same two-week window last year—using your example, if today is August 30th, that means comparing Aug 16–29 this year to Aug 16–29 last year. Here's a practical, reusable approach across common databases, plus how to adapt it to your existing parameterized query pattern.

First, Break Down the Date Logic #

The core idea is to calculate the date ranges dynamically based on the current date, so you don’t have to hardcode values every time:

• This year’s end date: The day before today (since your example ends on Aug 29 when today is Aug 30)
• This year’s start date: 13 days before the end date (to get a full 14-day window)
• Last year’s dates: Simply subtract 1 year from both of this year’s range dates

Query Implementations by Database Type #

SQL Server

Use DATEADD and GETDATE() to compute ranges, or clean it up with variables:

DECLARE @CurrentDate DATE = CAST(GETDATE() AS DATE)
DECLARE @ThisYearEnd DATE = DATEADD(day, -1, @CurrentDate)
DECLARE @ThisYearStart DATE = DATEADD(day, -13, @ThisYearEnd)
DECLARE @LastYearStart DATE = DATEADD(year, -1, @ThisYearStart)
DECLARE @LastYearEnd DATE = DATEADD(year, -1, @ThisYearEnd)

-- Combine results with a period label to distinguish data
SELECT 'This Year' AS Period, * 
FROM table 
WHERE date BETWEEN @ThisYearStart AND @ThisYearEnd

UNION ALL

SELECT 'Last Year' AS Period, * 
FROM table 
WHERE date BETWEEN @LastYearStart AND @LastYearEnd

MySQL/MariaDB

Leverage DATE_SUB and CURDATE() for date calculations:

SET @CurrentDate = CURDATE();
SET @ThisYearEnd = DATE_SUB(@CurrentDate, INTERVAL 1 DAY);
SET @ThisYearStart = DATE_SUB(@ThisYearEnd, INTERVAL 13 DAY);
SET @LastYearStart = DATE_SUB(@ThisYearStart, INTERVAL 1 YEAR);
SET @LastYearEnd = DATE_SUB(@ThisYearEnd, INTERVAL 1 YEAR);

SELECT 'This Year' AS Period, * 
FROM table 
WHERE date BETWEEN @ThisYearStart AND @ThisYearEnd

UNION ALL

SELECT 'Last Year' AS Period, * 
FROM table 
WHERE date BETWEEN @LastYearStart AND @LastYearEnd

PostgreSQL

Use interval arithmetic with CURRENT_DATE in a CTE for readability:

WITH date_ranges AS (
    SELECT
        CURRENT_DATE - INTERVAL '1 day' AS this_year_end,
        CURRENT_DATE - INTERVAL '14 days' AS this_year_start,
        CURRENT_DATE - INTERVAL '1 year 1 day' AS last_year_end,
        CURRENT_DATE - INTERVAL '1 year 14 days' AS last_year_start
)
SELECT 'This Year' AS Period, t.*
FROM table t, date_ranges dr
WHERE t.date BETWEEN dr.this_year_start::DATE AND dr.this_year_end::DATE

UNION ALL

SELECT 'Last Year' AS Period, t.*
FROM table t, date_ranges dr
WHERE t.date BETWEEN dr.last_year_start::DATE AND dr.last_year_end::DATE

Adapting to Your Existing Parameterized Query #

If you want to stick with your original SELECT * FROM table WHERE date BETWEEN @DS_START_DATE and @DS_END_DATE pattern, compute the four range dates in your application code first, then run the query twice (once for each year’s range) and combine the results.

For example, in Python:

from datetime import datetime, timedelta

current_date = datetime.today().date()
this_year_end = current_date - timedelta(days=1)
this_year_start = this_year_end - timedelta(days=13)
last_year_start = this_year_start.replace(year=this_year_start.year - 1)
last_year_end = this_year_end.replace(year=this_year_end.year - 1)

# Execute your parameterized query twice with these date pairs

Quick Tips #

• Use UNION ALL instead of UNION—it’s faster because we don’t need to remove duplicates between the two periods
• Add the Period column to easily tell this year’s data apart from last year’s
• Ensure your date column is a date/datetime type to avoid casting errors

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