如何在T-SQL分组查询中正确排序日期以展示每日收款总额？

Hey there! Let's troubleshoot why your daily collection totals query isn't sorting correctly by order date in descending order. This is a super common issue, usually tied to how you're handling the date field in grouping or sorting—let's break down the most likely problems and fixes.

Common Mistakes That Cause Wrong Sorting #

First, let's look at a typical incorrect query that might be giving you trouble:

SELECT 
    OrderDate,
    SUM(Amount) AS DailyTotal
FROM Orders
GROUP BY OrderDate
ORDER BY OrderDate DESC;

This fails if:

• OrderDate is a datetime/datetime2 type with time values (e.g., 2024-05-20 14:30:00)—grouping by the full datetime splits the same day into multiple rows, making sorting look disjointed.
• OrderDate is stored as a string—sorting uses lexicographical order (e.g., "2024-01-10" comes after "2024-12-01" because "0" < "1"), which doesn't align with actual date logic.

Correct Query Options #

1. For datetime/datetime2 Fields (Recommended)

Truncate the datetime to just the date part so all orders from the same day are grouped together, then sort by this truncated date:

SELECT 
    CAST(OrderDate AS DATE) AS OrderDate, -- Removes time portion (e.g., 2024-05-20)
    SUM(Amount) AS DailyTotal
FROM Orders
GROUP BY CAST(OrderDate AS DATE) -- Must match the SELECT expression exactly
ORDER BY CAST(OrderDate AS DATE) DESC;

Alternatively, you can use CONVERT(DATE, OrderDate) instead of CAST—they work identically here. For SQL Server 2012+, you can also use DATEFROMPARTS for extra clarity:

SELECT 
    DATEFROMPARTS(YEAR(OrderDate), MONTH(OrderDate), DAY(OrderDate)) AS OrderDate,
    SUM(Amount) AS DailyTotal
FROM Orders
GROUP BY DATEFROMPARTS(YEAR(OrderDate), MONTH(OrderDate), DAY(OrderDate))
ORDER BY 1 DESC; -- Shorthand for sorting by the first column (OrderDate)

2. For String-Stored Dates (Not Recommended, But Fixable)

If OrderDate is a string, convert it to a DATE type first to ensure proper date sorting. You'll need to use the correct style code for your string format (e.g., 120 for yyyy-mm-dd, 101 for mm/dd/yyyy):

SELECT 
    CONVERT(DATE, OrderDate, 120) AS OrderDate, -- Adjust style code to match your string format
    SUM(Amount) AS DailyTotal
FROM Orders
GROUP BY CONVERT(DATE, OrderDate, 120)
ORDER BY CONVERT(DATE, OrderDate, 120) DESC;

Pro Tip: If possible, alter your table to store OrderDate as a datetime2 type—this avoids all string-based date headaches long-term.

Quick Checks to Verify #

• Double-check that your GROUP BY expression matches exactly what's in your SELECT clause for the date field.
• If using an alias for sorting (e.g., ORDER BY OrderDate DESC), make sure the alias doesn't conflict with an existing column name.
• Test the date conversion alone first (e.g., SELECT CAST(OrderDate AS DATE) FROM Orders) to confirm it's returning the correct date values.

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