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

我在为从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";

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

最佳答案

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

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

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

关于sql - 如何从sql表中获取每日利润，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/31816155/