Oracle 子查询前 1 个结果

发布于 2024-10-12 18:04:44 字数 464 浏览 6 评论 0原文

我想获取 b 的每个唯一值的前 1 行以及 b 的特定值的 c 的最小值。即使可以有超过 1 行具有相同的最小值（仅选择第一个）

myTable

a 整数（唯一）
b 整数
c 整数

我已经尝试过此查询

SELECT t1.* 
  FROM myTable t1, 
       (SELECT b, 
               MIN(c) as c 
          FROM myTable 
      GROUP BY b) t2 
 WHERE t1.b = t2.b 
   AND t1.c = t2.c

但是，在此表中可能有超过对于给定 b 值，c 的最小值的 1 个实例。上述查询在这些条件下会生成重复项。

我有一种感觉，我需要在某个地方使用 rownum，但我不太确定在哪里。

原文

I want to get the top 1 row for each unique value of b with the minimum value of c for that particular value of b. Even though there can be more than 1 row with the same min value (just chose the first one)

myTable

a integer (unique)
b integer
c integer

I've tried this query

SELECT t1.* 
  FROM myTable t1, 
       (SELECT b, 
               MIN(c) as c 
          FROM myTable 
      GROUP BY b) t2 
 WHERE t1.b = t2.b 
   AND t1.c = t2.c

However, in this table it's possible for there to be more than 1 instance of the minimum value of c for a given value of b. The above query generates duplicates under these conditions.

I've got a feeling that I need to use rownum somewhere, but I'm not quite sure where.

分享到QQ

分享到微博

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

发布评论

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

巷雨优美回忆 2024-10-19 18:04:44

您可以使用ROW_NUMBER：

SELECT *
FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY b ORDER BY c) AS rn
    FROM myTable
) AS T1
WHERE rn = 1

You can use ROW_NUMBER:

SELECT *
FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY b ORDER BY c) AS rn
    FROM myTable
) AS T1
WHERE rn = 1

回复收藏 0 原文

秋风の叶未落 2024-10-19 18:04:44

要在相等的 c 之间进行平局，您需要进一步子查询一级以获得每组相等 c 的每个 b 的 min-a。（一口！）

select t0.*
FROM myTable t0
inner join (
    select t1.b, t1.c, MIN(a) as a
    from myTable t1
    inner join (
        select b, min(c) as c 
        from myTable 
        group by b
    ) t2 on t1.b = t2.b and t1.c = t2.c
    group by t1.b, t1.c
) t3 on t3.a = t0.a and t3.b = t0.b and t3.c = t0.c

To tie-break between the equal c's, you will need to subquery one level further to get the min-a for each group of equal c's per b. (A mouthful!)

select t0.*
FROM myTable t0
inner join (
    select t1.b, t1.c, MIN(a) as a
    from myTable t1
    inner join (
        select b, min(c) as c 
        from myTable 
        group by b
    ) t2 on t1.b = t2.b and t1.c = t2.c
    group by t1.b, t1.c
) t3 on t3.a = t0.a and t3.b = t0.b and t3.c = t0.c

回复收藏 0 原文

~没有更多了~