场景

当有需求是这样的，我需要在表1中拿到某些数据(拿到每一条记录中两个字段的比值，然后再拿某一个字段的值)到表2中计算出对应的数据，并返回通过接口返回数据。

计算source_track中每条记录的(af_install/ssot_install)值，然后将该值乘上month_sie_enable等对应的字段，最后在统计这些字段：month_sie_enable * (af_install/ssot_install)的总和。

子SQL：

SELECT
        month_sie_enable * (af_install/ssot_install) as sub_total,
        login_on_install * (af_install/ssot_install) as loi_total,
        sie_enable * (af_install/ssot_install) as se_total,
        country as Country,
        date as Date
    FROM source_track tt
    WHERE tt.date BETWEEN '2023-02-01' AND '2023-02-31' AND country = 'All';

查询：

SELECT
     date_format(subquery.Date, '%Y-%m') date,
     subquery.Country as country,
     sum(subquery.Cost) as cost,
     sum(subquery.Ssot_install) as ssot_install,
     SUM(subquery.sub_total) as total_month_sie_enable,
     sum(subquery.loi_total) as total_login_on_install,
     sum(subquery.se_total) as total_sie_enable,
     sum(subquery.total_msee) as month_sie_enable_empower,
     sum(subquery.ae_total) as allays_empower,
     sum(subquery.ar_total) as all_rev,
     sum(subquery.appr_total) as app_rev,
     sum(subquery.sr_total) as sie_rev
FROM (
    SELECT
        country as Country,
        date as Date,
        cost as Cost,
        ssot_install as Ssot_install,
        month_sie_enable * (af_install/ssot_install) as sub_total,
        login_on_install * (af_install/ssot_install) as loi_total,
        sie_enable * (af_install/ssot_install) as se_total,
        month_sie_enable_empower * (af_install/ssot_install) as total_msee,
        allays_empower * (af_install/ssot_install) as ae_total,
        all_rev * (af_install/ssot_install) as ar_total,
        app_rev * (af_install/ssot_install) as appr_total,
        sie_rev * (af_install/ssot_install) as sr_total
    FROM source_track tt
    WHERE tt.date BETWEEN '2023-02-01' AND '2023-03-31' AND country in ('All', 'AC')
) AS subquery group by date_format(Date, '%Y-%m') , subquery.Country ORDER BY date_format(Date, '%Y-%m'),subquery.country desc;

使用关联查询获取到source_track每一条记录的at.af_install/at.ssot_install，然后根据关联条件计算每一个符合条件的(at.af_install/at.ssot_install)*air.AllowSIELive 值

select (at.af_install/at.ssot_install)*air.AllowSIELive  AllowSIELive, (at.af_install/at.ssot_install)*air.1stRev 1stRev from
 				revenue_ov air
        right join
        source_track at
            on air.Date = at.date and air.Country = at.country where at.date <= '2023-02-31' and at.date >= '2023-02-01' and air.Source in ('ALL','AC', 'ASA', 'AFC-ALL') and at.Country = 'ALL';