sql - 如何从sql表中获取每日利润

Question

我一直在寻找解决方案，以解决从 db (ms access) 表中查找每日利润的问题。与我在网上找到的其他提示的不同之处在于，我在表中没有一个字段“价格”和一个“成本”，而是一个字段“类型”来区分它是收入“S”还是成本“C” "

这是“记录”表

| Date | Price | Quantity | Type |
-----------------------------------
|01/02 | 20    | 2        | C    |
|01/02 | 10    | 1        | S    |
|01/02 | 3     | 10       | S    |
|01/02 | 5     | 2        | C    |
|03/04 | 12    | 3        | C    |
|03/03 | 200   | 1        | S    |
|03/03 | 120   | 2        | C    |

到目前为止，我尝试了不同的解决方案，例如:

SELECT 
    (SELECT SUM (RS.Price* RS.Quantity) 
     FROM Record RS WHERE RS.Type='S' GROUP BY RS.Data
    ) as totalSales,
    (SELECT SUM (RC.Price*RC.Quantity) 
     FROM Record RC WHERE RC.Type='C' GROUP BY RC.Date
    ) as totalLosses, 
    ROUND(totalSales-totaleLosses,2) as NetTotal, 
    R.Date 
FROM RECORD R";

在我看来它可以工作但显然行不通

和

SELECT RC.Data, ROUND(SUM (RC.Price*RC.QuantitY),2) as DailyLoss
INTO #DailyLosses
FROM Record RC
WHERE RC.Type='C' GROUP BY RC.Date 

SELECT RS.Date, ROUND(SUM (RS.Price*RS.Quantity),2) as DailyRevenue
INTO #DailyRevenues
FROM Record RS
WHERE RS.Type='S'GROUP BY RS.Date 

SELECT Date, DailyRevenue - DailyLoss as DailyProfit 
FROM #DailyLosses dlos, #DailyRevenues drev
WHERE dlos.Date = drev.Date";

除了正确的语法之外，我的问题是解决这类问题的方法

Answer 1

您可以使用分组和条件求和。试试这个:

SELECT data.Date, data.Income - data.Cost as Profit
FROM (
    SELECT Record.Date as Date,
        SUM(IIF(Record.Type = 'S', Record.Price * Record.Quantity, 0)) as Income,
        SUM(IIF(Record.Type = 'C', Record.Price * Record.Quantity, 0)) as Cost,
    FROM Record
    GROUP BY Record.Date
) data

在这种情况下，您首先创建一个子查询来获取收入和成本的单独字段，然后您的外部查询使用减法来获取实际利润。