如何在oracle中ID的每个重复行仅获取一条记录? [英] How to get only one record for each duplicate rows of the id in oracle?

问题描述

假设我有这张桌子:

group_id | image | image_id |
-----------------------------
23        blob       1
23        blob       2
23        blob       3
21        blob       4
21        blob       5
25        blob       6
25        blob       7

如何获取每个组ID中只有1个的结果?在这种情况下，一个组ID可能有多个图像，我只希望每个 group_id

how to get results of only 1 of each group id? in this case,there may be multiple images for one group id, i just want one result of each group_id

我尝试了与众不同，但我只会得到group_id.图片的最大值也不起作用.

i tried distinct but i will only get group_id. max for image also would not work.

推荐答案

Oracle中没有否标准聚合函数，这些函数不能与BLOB一起使用，因此GROUP BY解决方案将不起作用

There are no standard aggregate functions in Oracle that would work with BLOBs, so GROUP BY solutions won't work.

在子查询中根据ROW_NUMBER()试试这个.

Try this one based on ROW_NUMBER() in a sub-query.

SELECT inn.group_id, inn.image, inn.image_id
FROM
(
    SELECT t.group_id, t.image, t.image_id, 
        ROW_NUMBER() OVER (PARTITION BY t.group_id ORDER BY t.image_id) num
    FROM theTable t
) inn
WHERE inn.num = 1;

上面应该为每个组返回第一行(基于image_id).

The above should return the first (based on image_id) row for each group.

SQL小提琴

这篇关于如何在oracle中ID的每个重复行仅获取一条记录?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！