时间戳

首页 > 解决方案 > 如何按日期分组

mysql - 如何按日期分组

问题描述

下面我不能按日期分组以下数字。

我试图将Group By放在不同的行中，但没有用。

SELECT SUM(a.NetAmount) AS TotalDonation
FROM (
 SELECT
  (
   CASE WHEN bt.BalanceTransactionCurrencyID = 17
    THEN bt.BalanceTransactionNet
   ELSE
    bt.BalanceTransactionNet * (SELECT TOP 1 ExrateValue FROM Exrate WHERE ExrateDate < bt.BalanceTransactionCreated AND bt.BalanceTransactionCurrencyID = CurrencyID ORDER BY ExrateDate Desc)
   END
  ) AS NetAmount

 FROM Charge as ch

 JOIN BalanceTransaction as bt ON (ch.BalanceTransactionID = bt.BalanceTransactionID)

 WHERE ch.ChargeCreatedDate BETWEEN '3-1-2019' AND '3-31-2019'

) AS a

我想看：

Days              Total Amount
March 1           xxxx
March 2           xxxx
March 3           xxx

标签： mysqlsql

解决方案

以下代码有望显示您正在寻找的内容

SELECT a.Days AS Days, SUM(cast(a.NetAmount as decimal(16,9))) AS TotalDonation
FROM (
 SELECT
  (
   CASE WHEN bt.BalanceTransactionCurrencyID = 17
    THEN bt.BalanceTransactionNet
   ELSE
    bt.BalanceTransactionNet * (SELECT TOP 1 ExrateValue FROM Exrate WHERE ExrateDate < bt.BalanceTransactionCreated AND bt.BalanceTransactionCurrencyID = CurrencyID ORDER BY ExrateDate Desc)
   END
  ) AS NetAmount,
 ch.ChargeCreatedDate as Days

 FROM Charge as ch

 JOIN BalanceTransaction as bt ON (ch.BalanceTransactionID = bt.BalanceTransactionID)

 WHERE ch.ChargeCreatedDate BETWEEN '3-1-2019' AND '3-31-2019'

) AS a GROUP BY a.Days

这应该足够了。您需要SELECT在查询中输入所需的值才能显示它。此外，在使用该SUM()函数时，您需要指定组值。

推荐阅读