时间戳

首页 > 解决方案 > SQL Query 如何减去不同 SQL 查询的结果？[Sum() 的相反减法]

python - SQL Query 如何减去不同 SQL 查询的结果？[Sum() 的相反减法]

问题描述

我有几个不同的 SQL 查询，我粘贴了两个查询，如下所示，我想从不同表中的这些不同 SQL 查询中计算值，我可以对所有值求和，但是减去所有值呢？如val1-val2-val3-val4

SELECT code, SUM(val)
FROM (
-- query A（table_name_a）, `val1`
SELECT code,
          sum(CASE WHEN remark IN ('111', '222', '333')
                    THEN t.value ELSE 0
                    END)/
          sum(CASE WHEN remark IN ('444')
                    THEN t.value ELSE 0
                    END) AS val
        FROM table_name_a
       WHERE code IN ('%(code)s')
         AND date BETWEEN '%(start_date)s' AND '%(end_date)s'
       GROUP BY code, date
UNION
-- query B(table_name_b), `val2`
SELECT code,
          sum(CASE WHEN remark IN ('111', '222', '333')
                    THEN t.value ELSE 0
                    END)/
          sum(CASE WHEN remark IN ('444')
                    THEN t.value ELSE 0
                    END) AS val
       FROM table_name_b
       WHERE code IN ('%(code)s')
         AND substr(regexp_replace(date,'-',''),1,8) BETWEEN '%(start_date)s' AND '%(end_date)s'
       GROUP BY code, substr(regexp_replace(date,'-',''),1,8)
) res
GROUP BY CODE;

如果我'%(code)s', '%(start_date)s' and %(end_date)s'用 '00001' ， '20191001' 和 '20191030' 输入参数，我得到的结果如下

    code    val1
1   00001   0.00798752 -- result from query A

    code    val2
1   00001   0.00472937 -- result from query B

现在我想通过 0.00798752 - 0.00472937 得到结果，我有四个不同 SQL 查询的四个值，我可以只通过一个 SQL 查询减去它们吗？

标签： pythonsql

解决方案

就让*-1你要减去的值，让值变成负数，哈哈哈哈，神奇的SQL！！！！我喜欢数学和 SQL

SELECT code, SUM(val)
FROM (
-- query A（table_name_a）, `val1`
SELECT code,
          sum(CASE WHEN remark IN ('111', '222', '333')
                    THEN t.value ELSE 0
                    END)/
          sum(CASE WHEN remark IN ('444')
                    THEN t.value ELSE 0
                    END) AS val
        FROM table_name_a
       WHERE code IN ('%(code)s')
         AND date BETWEEN '%(start_date)s' AND '%(end_date)s'
       GROUP BY code, date
UNION
-- query B(table_name_b), `val2`
SELECT code,
          (sum(CASE WHEN remark IN ('111', '222', '333')
                    THEN t.value ELSE 0
                    END)/
          sum(CASE WHEN remark IN ('444')
                    THEN t.value ELSE 0
                    END)) * -1  AS val  -- let the value * -1
       FROM table_name_b
       WHERE code IN ('%(code)s')
         AND substr(regexp_replace(date,'-',''),1,8) BETWEEN '%(start_date)s' AND '%(end_date)s'
       GROUP BY code, substr(regexp_replace(date,'-',''),1,8)
) res
GROUP BY CODE;

推荐阅读