Oracle SQL 查询帮助

发布于 2024-12-23 10:05:25 字数 179 浏览 1 评论 0原文

给定一个表：

ID NUMBER
OBJECTID NUMBER
CATEGORYID NUMBER
SCORE NUMBER
SCOREDATE DATE

是否可以在一个查询中有效地检索给定对象的每个不同类别中的最后分数（基于SCOREDATE）？

原文

Given a table:

ID NUMBER
OBJECTID NUMBER
CATEGORYID NUMBER
SCORE NUMBER
SCOREDATE DATE

Is it possible to efficiently retrieve the last score (based on SCOREDATE) in each distinct category for a given object in one query?

分享到QQ

分享到微博

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

发布评论

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

鱼窥荷 2024-12-30 10:05:25

尝试：

select v.* from (
select category_id, 
       score, 
       scoredate, 
       row_number() over (partition by category_id order by scoredate desc) rn
from MyTable) v
where rn=1

Try:

select v.* from (
select category_id, 
       score, 
       scoredate, 
       row_number() over (partition by category_id order by scoredate desc) rn
from MyTable) v
where rn=1

回复收藏 0 原文

溺深海 2024-12-30 10:05:25

您想要的内容属于 [greatest-n-per-group] 标记。实现结果的一种方法：

SELECT
    t.CategoryId
  , t.Score
FROM
    ( SELECT
          CategoryId
        , MAX(ScoreDate) AS LastScoreDate
      FROM
          TableX
      WHERE
          ObjectId = @ObjectId
      GROUP BY 
          CategoryId
    ) AS grp
  JOIN
      TableX AS t
    ON  grp.Category = t.CategoryId
    AND grp.LastScoreDate = t.ScoreDate
WHERE
    t.ObjectId = @ObjectId

What you want falls into the [greatest-n-per-group] tag. One way to achieve the result:

SELECT
    t.CategoryId
  , t.Score
FROM
    ( SELECT
          CategoryId
        , MAX(ScoreDate) AS LastScoreDate
      FROM
          TableX
      WHERE
          ObjectId = @ObjectId
      GROUP BY 
          CategoryId
    ) AS grp
  JOIN
      TableX AS t
    ON  grp.Category = t.CategoryId
    AND grp.LastScoreDate = t.ScoreDate
WHERE
    t.ObjectId = @ObjectId

回复收藏 0 原文

~没有更多了~