SQL 查询帮助 - 需要一些魔法

发布于 2024-09-12 19:53:57 字数 643 浏览 5 评论 0原文

我需要一些查询帮助 - 我正在使用 Firebird 2.1。

我有一张表，如下所示：

RowID (primary key) | ActivityID | Duration | BilledAt

1 | 1 | 50 | 06.08.2010, 14:05:00.598
2 | 1 | 70 | 06.08.2010, 14:05:00.608
3 | 2 | 30 | 06.08.2010, 14:05:00.598
4 | 3 | 40 | 06.08.2010, 14:05:00.598
5 | 3 | 50 | 06.08.2010, 14:05:00.608

我想获取每个 ActivityID 的持续时间，但如果有多个条目具有相同的 ActivityID，我需要获取具有最高 BilledAt 值的条目。（最新条目）

如果我执行：

SELECT ActivityID, Max(BilledAt) 
FROM BilledTime 
GROUP BY ActivityID;

我将得到我想要的东西，而无需持续时间值。如果我在 GROUP BY 子句中包含 Duration 列，则会选择多个 ActivityID。

有一个优雅的解决方案吗？

谢谢！

原文

I need some help with a query - I'm using Firebird 2.1.

I have a table like:

RowID (primary key) | ActivityID | Duration | BilledAt

1 | 1 | 50 | 06.08.2010, 14:05:00.598
2 | 1 | 70 | 06.08.2010, 14:05:00.608
3 | 2 | 30 | 06.08.2010, 14:05:00.598
4 | 3 | 40 | 06.08.2010, 14:05:00.598
5 | 3 | 50 | 06.08.2010, 14:05:00.608

I'd like to get the Durations for each ActivityID BUT if there are more than one entries available with the same ActivityID, I need the get the one with the highest BilledAt value. (the most recent entry)

If I execute:

SELECT ActivityID, Max(BilledAt) 
FROM BilledTime 
GROUP BY ActivityID;

I'll get what I want without the Duration values. If I include the Duration column in the GROUP BY clause, then multiple ActivityIDs are selected.

Is there an elegant solution to this?

Thanks!

分享到QQ

分享到微博

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

发布评论

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

自我难过 2024-09-19 19:53:57

不熟悉 Firebird，因此语法可能是错误的，但这应该可行：

SELECT a.ActivityID, a.Duration, a.BilledAt
FROM BilledTime a
LEFT JOIN BilledTime b on a.ActivityID = b.ActivityID AND b.BilledAt > a.BilledAt
WHERE b.ActivityID IS NULL

或者您可以使用更直观的 WHERE NOT EXISTS 子查询而不是 LEFT JOIN，但我相信上面的结果会更快。

Not familiar with Firebird so the syntax might be wrong, but this should work:

SELECT a.ActivityID, a.Duration, a.BilledAt
FROM BilledTime a
LEFT JOIN BilledTime b on a.ActivityID = b.ActivityID AND b.BilledAt > a.BilledAt
WHERE b.ActivityID IS NULL

Alternatively you can use a more intuitive WHERE NOT EXISTS subquery instead of the LEFT JOIN, but I believe the above ends up being faster.

回复收藏 0 原文

吹泡泡o 2024-09-19 19:53:57

我会这样做

SELECT a.ActivityID, a.Duration, a.BilledAt 
FROM BilledTime a 
WHERE a.BilledAt = (select max(b.billedAt) from BilledTime b where b.ActivityId = a.ActivityID)

I would do like this

SELECT a.ActivityID, a.Duration, a.BilledAt 
FROM BilledTime a 
WHERE a.BilledAt = (select max(b.billedAt) from BilledTime b where b.ActivityId = a.ActivityID)

回复收藏 0 原文

~没有更多了~