给自己留个发泄的地方

留个纪念：

报表需求理解不透彻。。。。。。。。问题想复杂了。

    select * from 

    (

    select 

    a.first_category_id_fir as first_category_id,

            a.first_category_name_fir as first_category_name,

            a.second_category_id_sec as second_category_id,

            a.second_category_name_sec as second_category_name,

    b.category_id_third as category_id,

    b.category_name_third as category_name,

    b.total_amount_third as total_amount_third,

    a.total_amount_sec as total_amount_sec,

    b.total_amount_third/a.total_amount_sec as total_zhanbi 

    from 

    (

    SELECT 

    sec.first_category_id as first_category_id_fir,

    sec.first_category_name as first_category_name_fir,

    sec.second_category_id as second_category_id_sec,

    sec.second_category_name as second_category_name_sec,

    sum(sec.total_amount) as total_amount_sec 

    FROM 

    report.ads_product_region_date as sec 

    where 

    <![CDATA[sec.area_code<>#{areaCode}]]> 

    and 

    substr(sec.l_date,1,10)>=#{startTime} 

    and 

    <![CDATA[substr(sec.l_date,1,10)<=#{endTime}]]> 

    group by 

    sec.first_category_id,

    sec.first_category_name,

    sec.second_category_id,

    sec.second_category_name

    )as a 

    INNER JOIN 

    (

    SELECT 

    third.first_category_id as first_category_id_fir,

    third.first_category_name as first_category_name_fir,

    third.second_category_id as second_category_id_sec,

    third.second_category_name as second_category_name_sec,

    third.category_id as category_id_third,

    third.category_name as category_name_third,

    sum(third.total_amount) as total_amount_third 

    FROM 

    report.ads_product_region_date as third 

    where 

    <![CDATA[third.area_code<>#{areaCode}]]> 

    and 

    substr(third.l_date,1,10)>=#{startTime} 

    and 

    <![CDATA[substr(third.l_date,1,10)<=#{endTime}]]> 

    group by 

    third.first_category_id,

    third.first_category_name,

    third.second_category_id,

    third.second_category_name,

    third.category_id,

    third.category_name

    ) as b 

    ) as c 

    where 

    c.total_zhanbi >='0.1'

这个大sql 白写了。