使用Left join通过SQL Server按日期获取净额

Question

我正在尝试使用CTE和我的采购表来获得每天的净金额。下面给我一个错误，calendar.DateValue不在group-by子句中。我使用CTE是因为我想不出任何其他方法来获取该范围的日期值。o.date是在下订单时由GETDATE()填充的datetime，因此它也包含时间。

WITH calendar AS
(
  SELECT CAST('2013-01-01' AS DATETIME) DateValue
  UNION ALL
  SELECT  DateValue + 1
  FROM    calendar
  WHERE   DateValue < '2013-12-31'
)
SELECT
    SUM(o.amount) AS total_amount,
    c.DateValue 
FROM 
    calendar c 
LEFT JOIN 
    dbo.orders o ON c.DateValue = o.date
OPTION(MAXRECURSION 0)

期望结果：

DateValue   | total_amount
------------|-------------
2013-01-01  | 219.00
2013-01-02  | 397.00

Answer 1

试试这个：

WITH calendar AS
(
  SELECT CAST('2013-01-01' AS DATETIME) DateValue
  UNION ALL
  SELECT  DateValue + 1
  FROM    calendar
  WHERE   DateValue < '2013-12-31'
)
SELECT
    SUM(o.amount) AS total_amount,   
    c.DateValue 
FROM 
    calendar c 
LEFT JOIN 
    dbo.orders o ON c.DateValue = cast (o.[date] as date)
group by c.DateValue 
OPTION(MAXRECURSION 0)

Answer 2

您可以将o.[date]强制转换为DATE类型，只获取日期部分（无时间）。

WITH calendar AS
(
  SELECT CAST('2013-01-01' AS DATETIME) DateValue
  UNION ALL
  SELECT  DateValue + 1
  FROM    calendar
  WHERE   DateValue < '2013-12-31'
)
SELECT
    X.total_amount,
    c.DateValue 
FROM 
    calendar c
LEFT JOIN
(
    SELECT CAST(o.[date] AS DATE) AS order_date, SUM(o.amount) AS total_amount
    FROM dbo.orders o
    WHERE o.[date] >= '2013-01-01' AND o.[date] <= '2013-12-31 23:59:59'
    GROUP BY CAST(o.[date] AS DATE)
) X
ON X.order_date = c.DateValue