gpt4 book ai didi

sql - 在 FULL JOIN 中添加多列,假设空行为零

转载 作者:行者123 更新时间:2023-11-29 14:12:06 25 4
gpt4 key购买 nike

我运行 PostgreSQL,我有多个(10 多个)具有相同列名和格式的表,我想对其中两个列求和。 如果其中一个表中缺少一行,我需要将其视为 0(零)

双表示例:

表A:

primkey | year | month | day | data_value
--------+------+-------+-----+-----------
20120101| 2012 | 01 | 01 | 3.7
20120102| 2012 | 01 | 02 | 1.0

表B:

primkey | year | month | day | data_value
--------+------+-------+-----+-----------
20120101| 2012 | 01 | 01 | 1.3
20120103| 2012 | 01 | 03 | 5.5

想要的输出:

primkey | year | month | day | data_value
--------+------+-------+-----+-----------
20120101| 2012 | 01 | 01 | 5.0
20120102| 2012 | 01 | 02 | 1.0
20120103| 2012 | 01 | 03 | 5.5

我正在考虑这样做:

SELECT a.primkey, a.year, a.month, a.day, a.data_value+b.data_valueas data_value FROM "TableA" as a FULL JOIN "TableB" as b ON a.primkey = b.primkey;

但它会产生这个(缺少一些 data_values):

primkey | year | month | day | data_value
--------+------+-------+-----+-----------
20120101| 2012 | 01 | 01 | 5.0
20120102| 2012 | 01 | 02 |
20120103| 2012 | 01 | 03 |

注意:primkey 是唯一的并且是 PRIMARY KEY,这需要对连接 10+ 表有效

最佳答案

SELECT COALESCE(a.primkey, b.primkey) AS primkey
, COALESCE(a.year, b.year) AS year
, COALESCE(a.month, b.month) AS month
, COALESCE(a.day, b.day) AS day
, COALESCE(a.data_value,0) + COALESCE( b.data_value, 0) AS data_value
FROM "TableA" AS a
FULL JOIN "TableB" AS b ON a.primkey = b.primkey
;

关于sql - 在 FULL JOIN 中添加多列,假设空行为零,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/15056704/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com