sql - 根据第二列值对列中的不同行求和

Question

我有一个订单表(简化)

OrderId,
SalesPersonId,
SaleAmount,
CurrencyId,
...

我正在尝试在此表上创建一个报告，我希望得到类似这样的结果:

SalesPersonId   TotalCAD    TotalUSD
1               12,345.00   6,789.00
2               7,890.00    1,234.00

我不希望进行自连接(也许我过早地进行了优化，但这似乎效率低下)IE:

SELECT SalesPersonId, SUM(OrdersCAD.SaleAmount), SUM(OrderUSD.SaleAmount)
FROM Orders
    LEFT JOIN Orders AS OrdersCAD ON Orders.OrderID AND Orders.CurrencyID = 1
    LEFT JOIN Orders AS OrdersUSD ON Orders.OrderID AND Orders.CurrencyID = 2

但我想不出另一种方法来做到这一点，有什么想法吗？

Answer 1

使用 CASE block :

SELECT
  SalesPersonId,
  SUM(
    CASE CurrencyID
      WHEN 1 THEN SaleAmount
      ELSE 0
    END
  ) AS TotalCAD,
  SUM(
    CASE CurrencyID
      WHEN 2 THEN SaleAmount
      ELSE 0
    END
  ) AS TotalUSD
FROM Orders
GROUP BY SalesPersonId