mysql - 我想在 mysql 子查询中获取别名的总和

Question

我想获得 mysql 中子查询结果的总和。这是我的查询的片段

SELECT DISTINCT (st.accountNumber) AS ac,
       (SELECT CASE cmo.mode 
                    WHEN 'FIXED' THEN cs.fixedChargeAmount * SUM(st.convertedAmount)
                    WHEN 'FIXED_PERCENTAGE' THEN (cs.percentageAmount/100) * SUM(st.convertedAmount) 
                    WHEN 'TIERED_AMOUNT_FIXED' THEN cs.fixedChargeAmount * 1 
                    WHEN 'TIERED_COUNT_FIXED' THEN cs.fixedChargeAmount * SUM(st.convertedAmount)
                    WHEN 'TIERED_AMOUNT_PERCENTAGE' THEN (cs.percentageAmount/100) * SUM(st.convertedAmount) 
                    WHEN 'TIERED_COUNT_PERCENTAGE' THEN (cs.percentageAmount/100) * SUM(st.convertedAmount) 
                END AS computedCommissionAmount 
           FROM s_cm cm
           JOIN....
            AND cs.active=1 
            AND CASE cmo.mode 
                    WHEN 'FIXED' THEN TRUE 
                    WHEN 'FIXED_PERCENTAGE' THEN TRUE
                    WHEN 'TIERED_AMOUNT_FIXED' THEN SUM(st.convertedAmount) .... 
                    END ) AS TOTAL_SUM 
           FROM s_st st 
          INNER 
           JOIN ....
          GROUP 
             BY st.serviceID, 
                st.convertedCurrencyID;

如何获得别名 TOTAL_SUM 的总和。如果我在选择之前添加 SUM ，如下所示，则会失败并出现错误

ERROR 1111 (HY000): Invalid use of group function 

这无法工作。

SELECT DISTINCT(st.accountNumber) AS ac,
       SUM((
       SELECT CASE cmo.mode 
                  WHEN 'FIXED' THEN cs.fixedChargeAmount * SUM(st.convertedAmount) 
                  WHEN 'FIXED_PERCENTAGE' THEN (cs.percentageAmount/100) * SUM(st.convertedAmount) 
                  WHEN 'TIERED_AMOUNT_FIXED' THEN cs.fixedChargeAmount * 1 
                  WHEN 'TIERED_COUNT_FIXED' THEN cs.fixedChargeAmount * SUM(st.convertedAmount) 
                  WHEN 'TIERED_AMOUNT_PERCENTAGE' THEN (cs.percentageAmount/100) * SUM(st.convertedAmount) 
                  WHEN 'TIERED_COUNT_PERCENTAGE' THEN (cs.percentageAmount/100) * SUM(st.convertedAmount) 
              END AS computedCommissionAmount 
         FROM s_cm cm 
         JOIN.... 
          AND cs.active=1 
          AND CASE cmo.mode 
                  WHEN 'FIXED' THEN TRUE 
                  WHEN 'FIXED_PERCENTAGE' THEN TRUE
                  WHEN 'TIERED_AMOUNT_FIXED' THEN SUM(st.convertedAmount) .... 
              END )) AS TOTAL_SUM 
         FROM s_st st 
        INNER 
         JOIN .... 
        GROUP 
           BY st.serviceID, 
              st.convertedCurrencyID;

有人帮忙

这是整个查询；

SELECT DISTINCT(st.accountNumber) as ac, oc.clientID as ownerClientID,oc.clientName as ownerClient,
    s.serviceID,  st.settlementLogID, s.serviceName AS service,  SUM(st.convertedAmount) AS totalAmount, 
    ss.commissionModelID,   st.convertedCurrencyID,
         SUM((
            SELECT  case cmo.mode WHEN 'FIXED' then cs.fixedChargeAmount * SUM(st.convertedAmount) 
            WHEN 'FIXED_PERCENTAGE' then (cs.percentageAmount/100) * SUM(st.convertedAmount) 
            WHEN 'TIERED_AMOUNT_FIXED' then cs.fixedChargeAmount * 1 
            WHEN 'TIERED_COUNT_FIXED' then cs.fixedChargeAmount * SUM(st.convertedAmount) 
            WHEN 'TIERED_AMOUNT_PERCENTAGE' then (cs.percentageAmount/100) * SUM(st.convertedAmount) 
            WHEN 'TIERED_COUNT_PERCENTAGE' then (cs.percentageAmount/100) * SUM(st.convertedAmount)
      END as computedCommissionAmount 
      FROM s_commissionModels cm 
            JOIN s_commissionSettings cs on cm.commissionModelID=cs.commissionModelID 
            JOIN s_commissionModes cmo on cmo.commissionModeID = cm.commissionModeID 
        WHERE cm.commissionModelID = 2 AND cm.active=1 AND cs.active=1 
        AND 
         case cmo.mode 
            WHEN 'FIXED' then true when 'FIXED_PERCENTAGE' then true
            WHEN 'TIERED_AMOUNT_FIXED' then SUM(st.convertedAmount) between cs.minTransactionAmount AND cs.maxTransactionAmount
            WHEN 'TIERED_COUNT_FIXED' then SUM(st.convertedAmount) between cs.minTransactionCount AND cs.maxTransactionCount
            WHEN 'TIERED_AMOUNT_PERCENTAGE' then SUM(st.convertedAmount) between cs.minTransactionAmount AND cs.maxTransactionAmount
            WHEN 'TIERED_COUNT_PERCENTAGE' then SUM(st.convertedAmount) between cs.minTransactionCount AND cs.maxTransactionCount end 
         AND cs.beneficiaryClientID =2 )) as TOTAL_SUM 
         FROM s_settlementTransactions st 
            INNER JOIN s_services s ON st.serviceID = s.serviceID  
            INNER JOIN clients oc on s.ownerClientID = oc.clientID 
            INNER JOIN currencies cu on st.convertedCurrencyID = cu.currencyID 
            INNER JOIN s_serviceSettings ss ON st.serviceID = ss.serviceID AND st.payerClientID = ss.payerClientID 
            LEFT JOIN bankBranches bb ON s.settlementBankBranchID = bb.bankBranchID 
            LEFT JOIN banks b ON bb.bankID = b.bankID 
            INNER JOIN countries c ON oc.countryID = c.countryID 
        WHERE st.overallStatus IN (140, 217) 
            AND st.settlementLogID = 94 AND st.overallSettlementStatus = 1 AND st.settlementUnit = 'account' 
            GROUP BY st.serviceID, st.convertedCurrencyID;

Answer 1

我猜你的案例是错误的。您可能想要条件聚合:

SUM(CASE cmo.mode 
        WHEN 'FIXED' THEN cs.fixedChargeAmount * st.convertedAmount
        WHEN 'FIXED_PERCENTAGE' THEN (cs.percentageAmount/100) * st.convertedAmount
        WHEN 'TIERED_AMOUNT_FIXED' THEN cs.fixedChargeAmount * 1 
        WHEN 'TIERED_COUNT_FIXED' THEN cs.fixedChargeAmount * st.convertedAmount
        WHEN 'TIERED_AMOUNT_PERCENTAGE' THEN (cs.percentageAmount/100) * st.convertedAmount 
        WHEN 'TIERED_COUNT_PERCENTAGE' THEN (cs.percentageAmount/100) * st.convertedAmount)
    END) AS computedCommissionAmount 

您的查询可能有其他问题。计算特定列的不同值似乎过于复杂。