MySQL,SQL如何获取所有行？

Question

SELECT
  pm.PaymentOperationID as PaymentOperationID,
  pm.DateOperation as DateOperation,
  ac.Name as AccountName
FROM PaymentOperations as pm
LEFT JOIN Account as ac on ac.AccountID = pm.AccountID
WHERE pm.Status = 'false' ORDER by pm.DateOperation DESC

如果我们使用此查询，我们会从表数据库中获取 20 行，但现在我们想要获取 SUM(pm.Cost)。

为此我们正在做:

SELECT
      pm.PaymentOperationID as PaymentOperationID,
      pm.DateOperation as DateOperation,
      ac.Name as AccountName,
      SUM(pm.Cost) as Costs
    FROM PaymentOperations as pm
    LEFT JOIN Account as ac on ac.AccountID = pm.AccountID
    WHERE pm.Status = 'false' ORDER by pm.DateOperation DESC

现在我们只得到一行。

请告诉我为什么我们只得到一行，以及如何通过输入Costs 得到所有 20 行？

Answer 1

您得到 1 行是因为您使用的是聚合函数 SUM() 而未对记录进行分组，因此您的整个表被视为一个组并且总和将应用于 20 的所有成本行，如果你需要得到你的结果，那么你应该使用 group by 和适当的分组列，从你的问题来看，不清楚哪个组的成本总和？

SELECT
      pm.PaymentOperationID as PaymentOperationID,
      pm.DateOperation as DateOperation,
      ac.Name as AccountName,
      SUM(pm.Cost) as Costs
    FROM PaymentOperations as pm
    LEFT JOIN Account as ac on ac.AccountID = pm.AccountID
    WHERE pm.Status = 'false' 
  GROUP BY some_column /* like GROUP BY PaymentOperationID for each PaymentOperationID you can see the total cost */
ORDER by pm.DateOperation DESC

MySQL Extensions to GROUP BY

GROUP BY (Aggregate) Functions