【求助】SQL两个表中的数据做运算，为什么不是最终运算结果？ - SQL Server论坛

已结贴√ 问题点数：10 回复次数：7

【求助】SQL两个表中的数据做运算，为什么不是最终运算结果？

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编辑过]

搜索更多相关主题的帖子: 两个表　运算　结果　 SQL　数据　

第 2 楼

得分:0

请各位前辈帮帮忙

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

第 3 楼

得分:10

无关联

程序代码：

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;

剑栈风樯各苦辛，别时冰雪到时春

第 4 楼

得分:0

回复 3楼林月儿

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

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

第 5 楼

得分:0

前面加上分子分母的查询，看看查的对不对
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
。。。。

剑栈风樯各苦辛，别时冰雪到时春

第 6 楼

得分:0

回复 5楼林月儿

查的是对的

第 7 楼

得分:0

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

剑栈风樯各苦辛，别时冰雪到时春

第 8 楼

得分:0

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

程序代码：

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编辑过]