SQL新手求适配跨年度/同年度的动态时间区间营收查询语句

Hey there! Let's sort out that SQL query issue you're facing—your original statement has a classic logical mistake that's preventing it from returning any data, and I'll show you how to write a flexible query that works for both cross-year ranges (like 2016-09 to 2017-03) and same-year ranges (like 2017-01 to 2017-06).

First, Why Your Original Query Fails #

Your current code uses AND to demand that financial_year is both 2016 and 2017 at the same time:

SELECT revenue FROM myapp_financialdata WHERE financial_year = 2016 AND financial_month = 9 AND financial_year = 2017 AND financial_month = 3

That's impossible—no single row can have two different values for financial_year, so this query will always return zero results. We need to switch to using OR to handle the split across years, paired with proper range checks.

Solution 1: Hardcoded Query for Your Specific Cross-Year Range #

For the 2016-09 to 2017-03 interval, use this query to target two separate date segments and combine them:

SELECT revenue 
FROM myapp_financialdata 
WHERE 
    -- Include all 2016 months from September onward
    (financial_year = 2016 AND financial_month >= 9)
    OR
    -- Include all 2017 months up to March
    (financial_year = 2017 AND financial_month <= 3);

Solution 2: Flexible Query for Any Date Range (Cross or Same Year) #

If you want a query that adapts to any start/end year/month combination (no need to rewrite it every time), use this generalized version. It works seamlessly for both cross-year and same-year ranges:

SELECT revenue 
FROM myapp_financialdata 
WHERE 
    -- Include all full years between the start and end year
    (financial_year > @start_year AND financial_year < @end_year)
    OR
    -- Include months from the start year that are on or after the start month
    (financial_year = @start_year AND financial_month >= @start_month)
    OR
    -- Include months from the end year that are on or before the end month
    (financial_year = @end_year AND financial_month <= @end_month);

To use this, replace @start_year, @start_month, @end_year, @end_month with your actual values. For example:

• For 2017-01 to 2017-06: Set @start_year = 2017, @start_month = 1, @end_year = 2017, @end_month = 6
• For 2016-09 to 2017-03: Set @start_year = 2016, @start_month = 9, @end_year = 2017, @end_month = 3

Bonus: Same-Year Shortcut #

If you're only dealing with a same-year range, you can simplify the query using BETWEEN for cleaner code:

SELECT revenue 
FROM myapp_financialdata 
WHERE 
    financial_year = 2017 
    AND financial_month BETWEEN 1 AND 6;

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