如何在oracle中为每个重复的id行获取一条记录？

suppose i have this table:

假设我有这张桌子：

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

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

如何获得每组id中只有1个的结果？在这种情况下，一个组ID可能有多个图像，我只想要每个group_id的一个结果

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

我尝试了不同但我只会得到group_id。图像的最大值也不起作用。

2 个解决方案

#1

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

Oracle中没有可与BLOB一起使用的标准聚合函数，因此GROUP BY解决方案将无法工作。

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

在子查询中尝试基于ROW_NUMBER（）的这个。

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;

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

上面应该返回每个组的第一行（基于image_id）。

SQL Fiddle

SQL小提琴

2 个解决方案

#1

更多相关文章

随机推荐