连接 3 个具有相似结构的表

发布于 2024-08-09 18:04:25 字数 508 浏览 2 评论 0原文

我正在 oracle 10g 中执行 sql 查询。

我想将 3 个表连接到一个新表中，其中包含 3 个单独表中的所有记录。只要 reg_id 在 3 个表之间重复，就应该对余额进行求和，这样每个 reg_id 只有一个条目与我的新表中的余额求和。

样本数据（类似的表，具有不同的值）。表：temp1、temp2、temp3

reg_id    |            balance
--------------------------------
92603013               183.77
92611902               419.46
92766121               77.04
93527720               24.84
93581368               120.09
93684684                89.88
93527720               113.66

感谢有人可以帮助我解决语法问题。

原文

I'm executing sql queries in oracle 10g.

I want to join 3 tables into a single new table containing all the records from the 3 individual tables. The balance should be Summed up wherever the reg_id is duplicated between the 3 tables, such that there is just one entry per reg_id with the summed balance in my new table.

Sample data ( similar tables, with different values ) . tables : temp1, temp2, temp3

reg_id    |            balance
--------------------------------
92603013               183.77
92611902               419.46
92766121               77.04
93527720               24.84
93581368               120.09
93684684                89.88
93527720               113.66

Appreciate if someone can help me with the syntax.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

西瑶 2024-08-16 18:04:25

尝试以下操作...

INSERT INTO target_table (reg_id, balance)
SELECT reg_id, sum(balance) as balance
FROM (select reg_id, balance from temp1
      UNION ALL
      select reg_id, balance from temp2
      UNION ALL
      select reg_id, balance from temp3)
GROUP BY reg_id;

我还没有尝试过，所以不知道语法是否正确以及它是否会严重破坏您的数据。 :)

编辑：将 UNION 更改为 UNION ALL。谢谢，APC！

编辑2：根据托尼的建议明确指定列。

Try the following...

INSERT INTO target_table (reg_id, balance)
SELECT reg_id, sum(balance) as balance
FROM (select reg_id, balance from temp1
      UNION ALL
      select reg_id, balance from temp2
      UNION ALL
      select reg_id, balance from temp3)
GROUP BY reg_id;

I haven't tried it so don't know if the syntax is correct and whether it'll will horribly mangle your data. :)

EDIT: Changed UNION to UNION ALL. Thanks, APC!

EDIT 2: Specified the columns explicitly per Tony's recommendation.

回复收藏 0 原文

轻许诺言 2024-08-16 18:04:25

我建议：

SELECT coalesce(t1.reg_id, t2.reg_id, t3.reg_id) AS the_reg_id,
       coalesce(t1.balance, 0.0) + 
       coalesce(t2.balance, 0.0) +
       coalesce(t3.balance, 0.0) AS the_balance
FROM t1 FULL OUTER JOIN t2 ON (t1.reg_id = t2.reg_id)
        FULL OUTER JOIN t3 ON (t1.reg_id = t3.reg_id)

I'd suggest:

SELECT coalesce(t1.reg_id, t2.reg_id, t3.reg_id) AS the_reg_id,
       coalesce(t1.balance, 0.0) + 
       coalesce(t2.balance, 0.0) +
       coalesce(t3.balance, 0.0) AS the_balance
FROM t1 FULL OUTER JOIN t2 ON (t1.reg_id = t2.reg_id)
        FULL OUTER JOIN t3 ON (t1.reg_id = t3.reg_id)

回复收藏 0 原文

~没有更多了~