sql - 我只需要 Min

Question

我有一个查询需要返回每个 ID 本月的最低余额。我遇到的问题是它返回多个余额而不是 Balance 上的最小余额。我不断得到这样的结果:

ID      Name   Month     Year  TodayMonth   TodayYear  BalMin 
1        A      4         12     4            2012      10,000.00 
1        A      4         12     4            2012      20,000.00

当我需要它返回最低余额时:

ID      Name   Month     Year  TodayMonth   TodayYear  BalMin 
1        A      4         12     4            2012      10,000.00

这是我目前所拥有的:

SELECT DISTINCT
     TOP (100) PERCENT History.ID, info.Name, DATEPART(mm, History.ReportDate) AS Month, DATEPART(yy,History.ReportDate) AS Year, DATEPART(mm, { fn CURDATE() }) AS TodayMonth, DATEPART(yy, { fn CURDATE() }) AS TodayYear, MIN(History.Balance) AS BalMin
    FROM         History LEFT OUTER JOIN Info ON History.ID = Info.ID
    WHERE     (DATEPART(yy, History.ReportDate) = DATEPART(yy, { fn CURDATE() })) AND (DATEPART(mm, History.ReportDate) = DATEPART(mm, 
                          { fn CURDATE() })) AND (History.Balance > 0)
    GROUP BY History.ID, History.ReportDate, Info.Name, History.Balance
    ORDER BY History.ID

Answer 1

您的 group by 不应包括 balance(因为您在其上运行 min)，并且您可能应该使用您在选择中使用的 datepart (否则你不会按月分组)。 left join 也没有意义，因为您无论如何都要在 where 子句中查找非空记录。这可能有效:

SELECT History.ID,
    Info.Name,
    DATEPART(mm, History.ReportDate) AS [Month],
    DATEPART(yy, History.ReportDate) AS [Year],
    MIN(History.Balance) AS BalMin
FROM History
    JOIN Info ON History.ID = Info.ID
WHERE (DATEPART(yy, History.ReportDate) = DATEPART(yy, { fn CURDATE() }))
    AND (DATEPART(mm, History.ReportDate) = DATEPART(mm, { fn CURDATE() }))
    AND (History.Balance > 0)
GROUP BY History.ID,
    DATEPART(mm, History.ReportDate),
    DATEPART(yy, History.ReportDate),
    Info.Name
ORDER BY History.ID

如您所见，我还删除了 distinct 和 top 关键字。 Distinct 对于 group by 通常是不必要的(如果不是代码味道)，我假设您的 top 用于调试或订购 View ，你是哪个should probably not do .