如何对现有SQL查询得到的投资基金总价值计算指定百分比

要获取原查询返回的总基金价值的指定百分比（例如30%），只需在原计算结果基础上乘以对应比例即可，以下是两种可行的修改方式：

方式一：直接修改外层计算逻辑 #

直接在原ROUND函数内的求和结果后乘以目标比例（如30%对应0.3），即可直接输出百分比结果：

SELECT ROUND (SUM (NVL ((a.units * p.price) / 100, a.amt)) * 0.3, 2) totalfundvalue_30percent
  FROM (
            SELECT b1,
                   REF,
                   schme,
                   contdef,
                   cont,
                   fundcode,
                   fund,
                   SUM (rrr) amt,
                   SUM (ttt) units
              FROM hist
             WHERE     b1 = 'SL'
                   AND eee <= SYSDATE
                   AND refno = '5656'
                   AND schme = 'M'
          GROUP BY b1,
                   refno,
                   schme,
                   contdef,
                   cont,
                   fundcode,
                   fund
       ) a,
       (
          SELECT b1,
                 fundcode,
                 fund,
                 www price
            FROM (
                    SELECT b1,
                           fundcode,
                           fund,
                           www,
                           qqq,
                           seqno,
                           MAX (qqq) OVER (PARTITION BY b1, fundcode, fund) maxdate,
                           MAX (seqno)
                              OVER (PARTITION BY b1,
                                                 fundcode,
                                                 fund,
                                                 qqq) maxseqno
                      FROM funddef
                     WHERE     b1 = 'SL'
                           AND qqq <= SYSDATE
                 )
           WHERE     qqq = maxdate
                 AND seqno = maxseqno
       ) p,
       conttype  c,
       mp_fund   f,
       basic     b
 WHERE     a.b1 = p.b1(+)
       AND a.b1 = f.b1(+)
       AND a.b1 = b.b1
       AND a.b1 = c.b1(+)
       AND a.fundcode = p.fundcode(+)
       AND a.fundcode = f.fundcode(+)
       AND a.fund = p.fund(+)
       AND a.conttype = c.conttype(+)
       AND a.REF = b.REF
       AND NVL (a.units * p.price, a.amt) > 0

方式二：通过子查询保留原价值并计算百分比 #

如果需要同时查看原总基金价值和对应百分比结果，可以将原查询作为子查询，在外层进行百分比计算：

SELECT 
    totalfundvalue,
    ROUND(totalfundvalue * 0.3, 2) totalfundvalue_30percent
FROM (
    SELECT ROUND (SUM (NVL ((a.units * p.price) / 100, a.amt)), 2) totalfundvalue
      FROM (
                SELECT b1,
                       REF,
                       schme,
                       contdef,
                       cont,
                       fundcode,
                       fund,
                       SUM (rrr) amt,
                       SUM (ttt) units
                  FROM hist
                 WHERE     b1 = 'SL'
                       AND eee <= SYSDATE
                       AND refno = '5656'
                       AND schme = 'M'
              GROUP BY b1,
                       refno,
                       schme,
                       contdef,
                       cont,
                       fundcode,
                       fund
           ) a,
           (
              SELECT b1,
                     fundcode,
                     fund,
                     www price
                FROM (
                        SELECT b1,
                               fundcode,
                               fund,
                               www,
                               qqq,
                               seqno,
                               MAX (qqq) OVER (PARTITION BY b1, fundcode, fund) maxdate,
                               MAX (seqno)
                                  OVER (PARTITION BY b1,
                                                     fundcode,
                                                     fund,
                                                     qqq) maxseqno
                          FROM funddef
                         WHERE     b1 = 'SL'
                               AND qqq <= SYSDATE
                     )
               WHERE     qqq = maxdate
                     AND seqno = maxseqno
           ) p,
           conttype  c,
           mp_fund   f,
           basic     b
     WHERE     a.b1 = p.b1(+)
           AND a.b1 = f.b1(+)
           AND a.b1 = b.b1
           AND a.b1 = c.b1(+)
           AND a.fundcode = p.fundcode(+)
           AND a.fundcode = f.fundcode(+)
           AND a.fund = p.fund(+)
           AND a.conttype = c.conttype(+)
           AND a.REF = b.REF
           AND NVL (a.units * p.price, a.amt) > 0
)

说明 #

• 若需要其他百分比，只需将代码中的0.3替换为对应比例即可（如20%替换为0.2）。
• 保留ROUND函数可以确保结果保留两位小数，与原查询的精度一致。

内容的提问来源于stack exchange，提问作者Alex