select a.tmp_warehouse as ack,count(a.tmp_num) as kctm,sum(a.tmp_price_t) as kczj from tmp_kcjl a group by a.tmp_warehouse

select b.tmp_warehouse as bck,count(b.tmp_num) as cktm,sum(b.tmp_price_t) as ckzj from tmp_ckmx b group by b.tmp_warehouse

有a、b两个表，先从两个表中提取相关字段后以tmp_warehouse汇总，然后把汇总后结果做除法运算，该怎么写语句？

a.ack as ck,(b.cktm/(a.kctm+b.cktm)) as tmzzl,(b.ckzj/(a.kczj+b.ckzj)) as jezzl,((b.cktm/(a.kctm+bb.cktm))+(b.ckzj/(a.kczj+bb.ckzj))) as hjzzl 

[此贴子已经被作者于2021-11-20 20:30编辑过]

请各位前辈帮帮忙

[此贴子已经被作者于2021-11-20 20:31编辑过]

无关联

SELECT
    aa.ack,
    bb.bck,
    ( bb.cktm / ( aa.kctm + bb.cktm ) ) AS tmzzl,
    ( bb.ckzj / ( aa.kczj + bb.ckzj ) ) AS jezzl,
    (
    ( bb.cktm / ( aa.kctm + bb.cktm ) ) + ( bb.ckzj / ( aa.kczj + bb.ckzj ) ) 
    ) AS hjzzl 
FROM
    (
        SELECT
            a.tmp_warehouse AS ack,
            count( a.tmp_num ) AS kctm,
            sum( a.tmp_price_t ) AS kczj 
        FROM
            tmp_kcjl a 
        GROUP BY
            a.tmp_warehouse 
    ) aa,
    (
        SELECT
            b.tmp_warehouse AS bck,
            count( b.tmp_num ) AS cktm,
            sum( b.tmp_price_t ) AS ckzj 
        FROM
            tmp_ckmx b 
        GROUP BY
            b.tmp_warehouse 
    ) bb 

关联tmp_warehouse

SELECT
    aa.ack,
    bb.bck,
    ( bb.cktm / ( aa.kctm + bb.cktm ) ) AS tmzzl,
    ( bb.ckzj / ( aa.kczj + bb.ckzj ) ) AS jezzl,
    (
    ( bb.cktm / ( aa.kctm + bb.cktm ) ) + ( bb.ckzj / ( aa.kczj + bb.ckzj ) ) 
    ) AS hjzzl 
FROM
    (
        SELECT
            a.tmp_warehouse AS ack,
            count( a.tmp_num ) AS kctm,
            sum( a.tmp_price_t ) AS kczj 
        FROM
            tmp_kcjl a 
        GROUP BY
            a.tmp_warehouse 
    ) aa
    left join
    (
        SELECT
            b.tmp_warehouse AS bck,
            count( b.tmp_num ) AS cktm,
            sum( b.tmp_price_t ) AS ckzj 
        FROM
            tmp_ckmx b 
        GROUP BY
            b.tmp_warehouse 
    ) bb 
    on bb.bck=aa.ack;

谢谢大神！
可是tmzzl算出来怎么都是0啊，问题可能出在哪儿呢？

[此贴子已经被作者于2021-11-20 21:12编辑过]

前面加上分子分母的查询，看看查的对不对
SELECT
    aa.ack,
    bb.bck,bb.cktm , aa.kctm + bb.cktm,
    ( bb.cktm / ( aa.kctm + bb.cktm ) ) AS tmzzl,
    ( bb.ckzj / ( aa.kczj + bb.ckzj ) ) AS jezzl,
    (
    ( bb.cktm / ( aa.kctm + bb.cktm ) ) + ( bb.ckzj / ( aa.kczj + bb.ckzj ) )
    ) AS hjzzl
。。。。

查的是对的

哪部分是对的？测试用例有吗

问题已解决，感谢版主的指点！！！

SELECT
    aa.ack ,
    ( 1.00*bb.cktm / ( aa.kctm + bb.cktm ) ) AS tmzzl,
    ( bb.ckzj / ( aa.kczj + bb.ckzj ) ) AS jezzl,
    (
    ( 1.00*bb.cktm / ( aa.kctm + bb.cktm ) ) + ( bb.ckzj / ( aa.kczj + bb.ckzj ) ) 
    ) AS hjzzl 
FROM
    (
        SELECT
            a.tmp_warehouse AS ack,
            count( a.tmp_num ) AS kctm,
            sum( a.tmp_price_t ) AS kczj 
        FROM
            tmp_kcjl a 
        GROUP BY
            a.tmp_warehouse 
    ) aa
    left join
    (
        SELECT
            b.tmp_warehouse AS bck,
            count( b.tmp_num ) AS cktm,
            sum( b.tmp_price_t ) AS ckzj 
        FROM
            tmp_ckmx b 
        GROUP BY
            b.tmp_warehouse 
    ) bb 
    on bb.bck=aa.ack;

[此贴子已经被作者于2021-11-20 22:35编辑过]