SQL - 使用 where 子句的一个选择中的不同总和级别

发布于 2024-10-27 13:20:37 字数 499 浏览 0 评论 0原文

我有 2 张桌子。其原始金额保持不变。第二个表列出了随时间推移针对第一个表中的原始金额应用的部分金额。

数据库表：

***memotable***
ID [primary, unique]
Amount (Orginal Amount)

***transtable***
ID [many IDs in transtable to single ID in memotable] 
AmountUsed (amount applied)
ApplyDate (date applied)

我想在单个选择中找到 ID、自上周以来使用的金额（ApplyDate > 2011-04-21）、迄今为止使用的金额。

结果中应该出现的唯一行是自上周以来已使用金额的行（ApplyDate > 2011-04-21）。

我一直在尝试获取迄今为止使用的金额的总和，因为这需要包括 ApplyDate > 之外的 AmountUsed 值。 2011年4月21日。

原文

I have 2 tables. One has the orginal amount that remains static. The second table has a list of partial amounts applied over time against the orginal amount in the first table.

DB Tables:

***memotable***
ID [primary, unique]
Amount (Orginal Amount)

***transtable***
ID [many IDs in transtable to single ID in memotable] 
AmountUsed (amount applied)
ApplyDate (date applied)

I would like to find, in a single select, the ID, amount used since last week (ApplyDate > 2011-04-21), amount used to date.

The only rows that should appear in the result is when an amount has been used since last week (ApplyDate > 2011-04-21).

I'm stuck on trying to get the sum for the amount used to date, since that needs to include AmountUsed values that are outside of when ApplyDate > 2011-04-21.

分享到QQ

分享到微博

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

发布评论

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

给妤﹃绝世温柔 2024-11-03 13:20:37

在这种情况下可以避免子选择：

SELECT
  ID,
  AmountUsedSinceLastWeek = SUM(CASE WHEN ApplyDate > '4/21/2011' THEN AmountUsed END)
  AmountUsedToDate = SUM(AmountUsed)
FROM TransTable
GROUP BY ID

It is possible to avoid subselects in this case:

SELECT
  ID,
  AmountUsedSinceLastWeek = SUM(CASE WHEN ApplyDate > '4/21/2011' THEN AmountUsed END)
  AmountUsedToDate = SUM(AmountUsed)
FROM TransTable
GROUP BY ID

回复收藏 0 原文

善良天后 2024-11-03 13:20:37

由于您想将其限制为自上周以来发生的行，但也想包括迄今为止的总数，我认为最有效的方法是使用子选择......

SELECT
    lastWeek.ID,
    lastWeek.AmountUsedSinceLastWeek,
    toDate.AmountUsedToDate
FROM
(
    SELECT
        ID,
        SUM(AmountUsed) AS AmountUsedSinceLastWeek
    FROM TransTable
    WHERE ApplyDate > '4/21/2011'
    GROUP BY ID
) lastWeek JOIN
(
    SELECT
        ID,
        SUM(AmountUsed) AS AmountUsedToDate
    FROM TransTable
    GROUP BY ID
) toDate ON lastWeek.ID = toDate.ID

Since you want to limit it to rows that happened since last week, but also want to include the total to date, I think the most efficient method would be to use sub-selects...

SELECT
    lastWeek.ID,
    lastWeek.AmountUsedSinceLastWeek,
    toDate.AmountUsedToDate
FROM
(
    SELECT
        ID,
        SUM(AmountUsed) AS AmountUsedSinceLastWeek
    FROM TransTable
    WHERE ApplyDate > '4/21/2011'
    GROUP BY ID
) lastWeek JOIN
(
    SELECT
        ID,
        SUM(AmountUsed) AS AmountUsedToDate
    FROM TransTable
    GROUP BY ID
) toDate ON lastWeek.ID = toDate.ID

回复收藏 0 原文

~没有更多了~