Oracle SQL：需要帮助根据子表中的值总和更新记录

发布于 2024-11-09 09:40:01 字数 510 浏览 0 评论 0原文

假设我有一组与以下场景匹配的表：

板条箱有盒子盒子里有桶桶保存一个称为 Pieces 的数值一个板条箱保存一个称为 TotalPieces 的数值，它是其中所有盒子中的所有桶中的所有件的总和，

所以我有以下称为 CRATES、BOXES、BUCKETS 的表。我需要一个更新语句，它将填充 crates 表中所有 crate 的 TotalPieces 列（下面的 SQL 中未显示），作为所有桶件的总和。是的，出于特定原因，我必须这样做，以防有人决定这样做，“好吧，为什么你要这样做，而不是仅仅运行一个 select 语句？”我可以执行 select 语句，它看起来像这样：

SELECT CR.ID, SUM(BU.PIECES) FROM CRATES CR
JOIN BOXES BO ON BO.CRATE_ID = CR.ID
JOIN BUCKETS BU ON POPR.BO_ID = BO.ID
GROUP BY CR.ID;

如何将所有桶的总件放入 crates 表中的件列中？

原文

Let's says I have a set of tables that match the following scenario:

Crates have boxes
Boxes have buckets
Buckets hold a numeric value called Pieces
A crate holds a numeric value called TotalPieces that is the sum of all the pieces in all the buckets in all the boxes within it

So I have the following tables called CRATES, BOXES, BUCKETS. I need an update statement that will populate the TotalPieces column (not shown in the SQL below) of ALL crates in the crates table to be the sum of all the bucket pieces. Yes, I have to do it this way for a specific reason in case anyone decides to be all like, "Well, why are you doing it like that instead of just running a select statement?" I can do the select statement and it looks like this:

SELECT CR.ID, SUM(BU.PIECES) FROM CRATES CR
JOIN BOXES BO ON BO.CRATE_ID = CR.ID
JOIN BUCKETS BU ON POPR.BO_ID = BO.ID
GROUP BY CR.ID;

How do I get the total pieces of all the buckets into the pieces column in the crates table?

分享到QQ

分享到微博

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

发布评论

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

回忆追雨的时光 2024-11-16 09:40:01

这可以解决问题：

update crates cr set cr.total_pieces = 
(
    select sum(bu.pieces) pieces from crates cr
    join boxes bo on bo.crate_id = cr.id
    join buckets bu on bu.box_id = box.id
    where crate.id = cr.id
    group by crate.id
);

This does the trick:

update crates cr set cr.total_pieces = 
(
    select sum(bu.pieces) pieces from crates cr
    join boxes bo on bo.crate_id = cr.id
    join buckets bu on bu.box_id = box.id
    where crate.id = cr.id
    group by crate.id
);

回复收藏 0 原文

彩虹直至黑白 2024-11-16 09:40:01

试试这个：

 SELECT BO.CRATE_ID AS CRATEID, SUM(BUCKETSUM) AS CRATESUM
  FROM BOXES BO INNER JOIN 
    (SELECT BU.BO_ID AS BOXID, SUM(BU.PIECES) as BUCKETSUM
     FROM BUCKETS BU GROUP BY BU.BO_ID) BU
    ON BU.BOXID = BO.ID
  GROUP BY BO.CRATE_ID

Try this:

 SELECT BO.CRATE_ID AS CRATEID, SUM(BUCKETSUM) AS CRATESUM
  FROM BOXES BO INNER JOIN 
    (SELECT BU.BO_ID AS BOXID, SUM(BU.PIECES) as BUCKETSUM
     FROM BUCKETS BU GROUP BY BU.BO_ID) BU
    ON BU.BOXID = BO.ID
  GROUP BY BO.CRATE_ID

回复收藏 0 原文

~没有更多了~