注册 登录
编程论坛 → SQL Server论坛

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

dibei 发布于 2021-11-20 16:30， 8115 次点击
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编辑过]

7 回复
#2
dibei2021-11-20 19:38
请各位前辈帮帮忙

[此贴子已经被作者于2021-11-20 20:31编辑过]
#3
林月儿2021-11-20 20:45
无关联
程序代码：
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
dibei2021-11-20 21:09
回复 3楼 林月儿
谢谢大神！
可是tmzzl算出来怎么都是0啊，问题可能出在哪儿呢？

[此贴子已经被作者于2021-11-20 21:12编辑过]
#5
林月儿2021-11-20 21:13
前面加上分子分母的查询，看看查的对不对
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
dibei2021-11-20 21:20
回复 5楼 林月儿
查的是对的
#7
林月儿2021-11-20 21:24
哪部分是对的？测试用例有吗
#8
dibei2021-11-20 21:44
问题已解决，感谢版主的指点！！！
程序代码：
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编辑过]
1
©2024, BCCN.NET
电脑版