T-SQL按年、月降序排序结果不符合预期的解决方案求助

Hey there! Let's break down why your current query isn't returning the results you expect, then fix it up.

The Problem with Your Current Query #

Your current WHERE clause year <= @year and month <= @month has a logical flaw: when the year is less than your target year (like 2018 when @year is 2019), it only keeps months that are <= @month (2 in your example). That's why you're missing all months from March to December in 2018, 2017, etc.

What you actually need is to include:

• All records where the year is less than @year (regardless of month), plus
• All records where the year is equal to @year and the month is <= @month

Solution 1: Explicit Logical Condition #

This is the most straightforward approach, and it plays nicely with indexes on year and month if you have them:

SELECT * 
FROM mytable 
WHERE year < @year OR (year = @year AND month <= @month)
ORDER BY year DESC, month DESC;

Solution 2: Convert to a Date Value #

If your database supports date functions, you can combine the year and month columns into a single date (using the first day of the month) and compare that to your target date. This makes the condition more readable:

-- For SQL Server, PostgreSQL, etc.
SELECT * 
FROM mytable 
WHERE DATEFROMPARTS(year, month, 1) <= DATEFROMPARTS(@year, @month, 1)
ORDER BY year DESC, month DESC;

-- For MySQL/MariaDB
SELECT * 
FROM mytable 
WHERE STR_TO_DATE(CONCAT(year, '-', month, '-01'), '%Y-%m-%d') <= STR_TO_DATE(CONCAT(@year, '-', @month, '-01'), '%Y-%m-%d')
ORDER BY year DESC, month DESC;

Solution 3: Numeric Year-Month Calculation #

Another option is to convert the year and month into a single numeric value (like 201902 for February 2019) and compare those values:

SELECT * 
FROM mytable 
WHERE (year * 100 + month) <= (@year * 100 + @month)
ORDER BY year DESC, month DESC;

Any of these approaches will give you the results you want: 2019,2 → 2019,1 → 2018,12 → 2018,11 → ... and so on.

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