帮我完成这个 SQL 查询

Question

我有一个 SQL Server CE 3.5 表（事务），具有以下架构：

• ID
• Transaction_Date
• 类别
• 描述
• 金额

查询：

  SELECT Transaction_Date, SUM(Amount) 
    FROM Transactions 
GROUP BY Transaction_Date;

我正在尝试执行 SUM（金额）并按 transaction_date 进行分组，以便我可以获取每天的总金额，但即使没有交易的日子我也想取回值，所以基本上没有交易的一天的记录只有 0.00 美元的金额。

感谢您的帮助！

Answer 1

您需要一个日历表来选择日期。或者，如果您有数字表，则可以将其有效地转换为日历表。基本上，它只是一个包含每个日期的表。为其构建和生成数据非常容易，并且在这些情况下非常方便。然后您只需使用：

SELECT
    C.calendar_date,
    SUM(T.amount)
FROM
    Calendar C
LEFT OUTER JOIN Transactions T ON
    T.transaction_date = C.calendar_date
GROUP BY
    C.calendar_date
ORDER BY
    C.calendar_date

需要记住的一些事项：

如果您要将其发送到前端或报告引擎，那么您应该只发送您拥有的日期（您的原始查询）并让前端填写如果可能的话，0.00 美元的天数本身。

另外，我在这里假设日期是一个精确的日期值，没有时间部分（因此连接中的“=”）。您的日历表可以包含“start_time”和“end_time”，以便您可以使用 BETWEEN 来处理包含时间部分的日期。这使您不必削减时间部分并可能破坏索引的使用。您也可以在使用它时计算一天的开始和结束点，但由于它是一个预填充的工作表，因此在我看来更容易包含开始时间和结束时间。

Answer 2

您需要以某种方式限制您的陈述的上限和下限，但这也许会有所帮助。

DECLARE @Start  smalldatetime, @End smalldatetime

SELECT @Start = 'Jan 1 2010', @End = 'Jan 18 2010';

--- make a CTE of range of dates we're interested in
WITH Cal AS (
    SELECT CalDate = convert(datetime, @Start) 
    UNION ALL
    SELECT CalDate = dateadd(d,1,convert(datetime, CalDate))  FROM Cal WHERE CalDate < @End

)

SELECT CalDate AS TransactionDate, ISNULL(SUM(Amount),0) AS TransactionAmount
FROM  Cal AS C
LEFT JOIN Transactions AS T On C.CalDate = T.Transaction_Date
GROUP BY CalDate ;

Answer 3

一旦您有了日历表（稍后会详细介绍），您就可以对数据范围进行内部联接以填充缺失的日期：

SELECT CalendarDate, NULLIF(SUM(t.Amount),0)
FROM (SELECT CalendardDate FROM Calendar
WHERE CalendarDate>= (SELECT MIN(TransactionDate) FROM Transactions) AND
  CalendarDate<= (SELECT MAX(TransactionDate) FROM Transactions)) c
 LEFT JOIN 
    Transactions t ON t.TransactionDate=c.CalendarDate
GROUP BY CalendarDate

要创建日历表，您可以使用 CTE：

WITH CalendarTable
AS
(
  SELECT CAST('20090601' as datetime) AS [date]
  UNION ALL
  SELECT DATEADD(dd, 1, [date])
  FROM CTE_DatesTable
  WHERE DATEADD(dd, 1, [date]) <= '20090630' /* last date */
)
SELECT [date] FROM CTE_DatesTable
OPTION (MAXRECURSION 0);

将两者结合起来，我们有

WITH CalendarTable
AS
(
  SELECT MIN(TransactionDate) FROM Transactions AS [date]
  UNION ALL
  SELECT DATEADD(dd, 1, [date])
  FROM CTE_DatesTable
  WHERE DATEADD(dd, 1, [date]) <= (SELECT MAX(TransactionDate) FROM Transactions) 
)
SELECT c.[date], NULLIF(SUM(t.Amount),0)
FROM Calendar c
 LEFT JOIN 
    Transactions t ON t.TransactionDate=c.[date]
GROUP BY c.[date]

Answer 4

不确定这是否适用于 CE

使用公共表表达式

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME

SET @StartDate = '2010-07-10'
SET @EndDate = '2010-07-20'

;WITH Dates AS (
    SELECT  @StartDate AS DateValue
    UNION ALL
    SELECT  DateValue + 1
    FROM    Dates
    WHERE   DateValue + 1 <= @EndDate
)
SELECT      Dates.DateValue, ISNULL(SUM(Transactions.Amount), 0)
FROM        Dates
LEFT JOIN   Transactions ON
                Dates.DateValue = Transactions.Transaction_Date
GROUP BY    Dates.DateValue;

使用循环 + 临时表

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME

SET @StartDate = '2010-07-10'
SET @EndDate = '2010-07-20'

SELECT @StartDate AS DateValue INTO #Dates

WHILE @StartDate <= @EndDate
BEGIN
    SET @StartDate = @StartDate + 1
    INSERT INTO #Dates VALUES (@StartDate)
END

SELECT      Dates.DateValue, ISNULL(SUM(Transactions.Amount), 0)
FROM        #Dates AS Dates
LEFT JOIN   Transactions ON
                Dates.DateValue = Transactions.Transaction_Date
GROUP BY    Dates.DateValue;

DROP TABLE  #Dates

Answer 5

如果您希望显示没有交易的日期
您可以每天添加一笔金额为零的虚拟交易
它不会干扰 SUM 并且会是你想要的