帮我处理这个 SQL 查询 [英] Help me with this SQL Query

问题描述

我有一个 SQL Server CE 3.5 表(事务)，其架构如下:

I've got a SQL Server CE 3.5 table (Transactions) with the following Schema:

• 身份证
• 交易日期
• 类别
• 说明
• 金额

查询:

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

我正在尝试做一个 SUM(Amount) 并按 transaction_date 分组，这样我就可以获得每天的总金额，但我想取回即使在没有交易的日子里的价值，所以基本上是一天的记录没有交易的金额只有 0.00 美元.

I'm trying to do a SUM(Amount) and group by transaction_date just so I can get the total amount for each day but I want to get back values even for days there were no transactions so basically the record for a day with no transactions would just have $0.00 for amount.

感谢您的帮助！

推荐答案

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

You need a Calendar table to select over the dates. Alternatively, if you have a Numbers table, you could turn that effectively into a Calendar table. Basically, it's just a table with every date in it. It's easy enough to build and generate the data for it and it comes in very handy for these situations. Then you would simply use:

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 美元的天数.

If you're sending this to a front-end or reporting engine then you should just send the dates that you have (your original query) and have the front end fill in the $0.00 days itself if that's possible.

另外，我在这里假设日期是一个没有时间分量的确切日期值(因此在连接中使用=").您的日历表可以包括开始时间"和结束时间"，以便您可以使用 BETWEEN 处理包含时间部分的日期.这使您不必剥离时间部分并可能破坏索引使用.您也可以在使用它时计算当天的起点和终点，但由于它是一个预填充的工作表，因此 IMO 更容易包含 start_time 和 end_time.

Also, I've assumed here that the date is an exact date value with no time component (hence the "=" in the join). Your calendar table could include a "start_time" and "end_time" so that you can use BETWEEN for working with dates that include a time portion. That saves you from having to strip off time portions and potentially ruining index usage. You could also just calculate the start and end points of the day when you use it, but since it's a prefilled work table it's easier IMO to include a start_time and end_time.

这篇关于帮我处理这个 SQL 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！