mysql - 从字段(虚拟字段)求和值

Question

我想知道是否可以在 mysql 中做这样的事情:

select 5 as a, 1 as b, a + b as c; 

问题是因为我有这个:

CONVERT( SUM(ifnull(commission,0.00)) ,DECIMAL(6,2)) as 'commission',
CONVERT( SUM(ifnull(income,0.00)) ,DECIMAL(6,2)) as 'income',
CONVERT( (SUM(ifnull(income,0.00)) - SUM(ifnull(commission,0.00))) ,DECIMAL(6,2)) as 'profit'

当然，如果不需要再次对所有值求和，第 3 行会更优化。

Answer 1

你做不到

select 5 as a, 1 as b, a + b as c;

但你可以做到

select a, b, a + b as c from (select 5 as a, 1 as b) as whatever;

(这是一个嵌套查询，当从外部查询使用时它充当假表)。

将您的想法应用到您的案例中，我们将:

select comission, income, income - comission from (
    select
        CONVERT( SUM(ifnull(commission,0.00)) ,DECIMAL(6,2)) as 'commission',
        CONVERT( SUM(ifnull(income,0.00)) ,DECIMAL(6,2)) as 'income'
    FROM ...
) as t

希望这对您有所帮助。