mysql - 列的总计

Question

如何获得所有列的总和？假设我有一张像下面这样的 table 。我的查询获取总成本和净返回。我怎样才能在不编写第二个查询的情况下计算“总”总和。

Date        rate      supplyfee     demandfee     chargedfee   othersTotalcosts  netReturn 
2010-01-10  1,339.00  2,150.00     10,000.00       120,000.00        1,085.78     1,064.22
2011-01-01  3,339.00  1,150.00     1,100.00        150,000.00        1,144.13      5.87
*Total*               3,300.00     2,100.00

总成本由 sum(chargedfee + others )/rate + demandfee 计算，净返回由 supplyfee - totalcosts 计算。

下面的查询计算总成本:

 SELECT date
        , rate
        , supply_fee
        , demand_fee
        , charged_fee
        , (charged + others/rate) + demandfee AS totalcosts
        , supplyfee-((charged + others/rate) + demandfee  AS net returns

 FROM  financies
 WHERE date BETWEEN '2010-01-10' AND '2011-01-01'

Answer 1

这看起来很复杂，但请耐心等待。它需要对 others/rate 的含义进行一些澄清，但原则是合理的。如果你有一个可以使用的财务主键，那么一个更优雅的(GROUP BY ... ROLLUP)解决方案可能是可行的，但是我没有足够的经验来提供可靠的建议。下面是我将如何解决这个问题。

冗长的选项

(
    SELECT
        financesTallied.date,
        financesTallied.rate,
        financesTallied.supply_fee,
        financesTallied.demand_fee,
        financesTallied.charged_fee,
        financesTallied.total_costs,
        financesTallied.net_return

    FROM (

        SELECT
            financeWithNetReturn.*,
            @supplyFee := @supplyFee + financeWithNetReturn.supply_fee,
            @demandFee := @demandFee + financeWithNetReturn.demand_fee,
            @charedFee := @charedFee + financeWithNetReturn.charged_fee
        FROM 
        ( // Calculate net return based off total costs
            SELECT 
                financeData.*,
                financeData.supply_fee - financeData.total_costs AS net_return
            FROM 
            ( // Select the data
                SELECT
                    date, 
                    rate, 
                    supply_fee, 
                    demand_fee, 
                    charged_fee,
                    (supply_fee+demand_fee+charged_fee)/rate AS total_costs // need clarification on others/rate
                FROM financies
                WHERE date BETWEEN '2010-01-10' AND '2011-01-01'
                ORDER BY date ASC
            ) AS financeData
        ) AS financeWithNetReturn,
        (
            SELECT
                @supplyFee := 0
                @demandFee := 0
                @charedFee := 0
        ) AS variableInit
    ) AS financesTallied
) UNION (
    SELECT
        '*Total*',
        NULL,
        @supplyFee,
        @demandFee,
        @chargedFee,
        NULL,
        NULL
)

从最内层查询到最外层查询。此查询选择基本费用并计算此行的 total_costs。这个 total_costs 公式需要调整，因为我不是 100% 清楚你在那里寻找什么。将其称为[SQ1]

            SELECT
                date, 
                rate, 
                supply_fee, 
                demand_fee, 
                charged_fee,
                (supply_fee+demand_fee+charged_fee)/rate AS total_costs // need clarification on others/rate
            FROM financies
            WHERE date BETWEEN '2010-01-10' AND '2011-01-01'
            ORDER BY date ASC

下一级我只是重复使用计算的 total_costs 列和 supply_fee 列以添加到 net_return 列中。这总结了每行所需的基本数据，将其称为 [SQL2]

        SELECT 
            financeData.*,
            financeData.supply_fee - financeData.total_costs AS net_return
        FROM 
        ([SQ1]) AS financeData

在这个级别是时候开始计算值了，因此需要用 0 值初始化所需的变量 ([SQL3])

        SELECT
            @supplyFee := 0
            @demandFee := 0
            @charedFee := 0 

下一步，我使用计算行来计算总计 ([SQL4])

    SELECT
        financeWithNetReturn.*,
        @supplyFee := @supplyFee + financeWithNetReturn.supply_fee,
        @demandFee := @demandFee + financeWithNetReturn.demand_fee,
        @charedFee := @charedFee + financeWithNetReturn.charged_fee
    FROM 
    ([SQL2]) AS financeWithNetReturn,
    ([SQL3]) AS variableInit

现在终于到了顶层，只需要输出想要的列而不需要计算列([SQL5])

SELECT
    financesTallied.date,
    financesTallied.rate,
    financesTallied.supply_fee,
    financesTallied.demand_fee,
    financesTallied.charged_fee,
    financesTallied.total_costs,
    financesTallied.net_return

FROM ([SQL4]) AS financesTallied

然后将其与总计行联合输出

([SQL5]) UNION (
    SELECT
        '*Total*',
        NULL,
        @supplyFee,
        @demandFee,
        @chargedFee,
        NULL,
        NULL
)