获取每个组的下一个最小值，大于或等于给定值

问题描述

给出以下表1:

    RefID  intVal  SomeVal
    ----------------------
        1      10    val01
        1      20    val02
        1      30    val03
        1      40    val04
        1      50    val05
        2      10    val06
        2      20    val07
        2      30    val08
        2      40    val09
        2      50    val10
        3      12    val11
        3      14    val12
        4      10    val13
        5     100    val14
        5     150    val15
        5    1000    val16

和Table2包含一些RefID和intVal之类的

and Table2 containing some RefIDs and intVals like

    RefID  intVal
    -------------
        1      11
        1      28
        2       9
        2      50
        2      51
        4      11
        5       1
        5     150
        5     151

需要一个SQL语句来获取每个RefID的下一个更大的intValue，如果未在Table1中找到NULL，则为NULL以下是预期结果

need an SQL Statement to get the next greater intValue for each RefID and NULL if not found in Table1following is the expected result

    RefID  intVal  nextGt  SomeVal
    ------------------------------
        1      11      20  val01
        1      28      30  val03
        2       9      10  val06
        2      50      50  val10
        2      51    NULL   NULL
        4      11    NULL   NULL
        5       1     100  val14
        5     150     150  val15
        5     151    1000  val16

帮助将不胜感激！

推荐答案

派生表a在给定refid和intVal的情况下从table1检索最小值.外部查询仅检索someValue.

Derived table a retrieves minimal values from table1 given refid and intVal from table2; outer query retrieves someValue only.

select a.refid, a.intVal, a.nextGt, table1.SomeVal
from
(
    select table2.refid, table2.intval, min (table1.intVal) nextGt
      from table2
      left join table1
        on table2.refid = table1.refid
       and table2.intVal <= table1.intVal
     group by table2.refid, table2.intval
) a
-- table1 is joined again to retrieve SomeVal
left join table1
  on a.refid = table1.refid
 and a.nextGt = table1.intVal

这是带有实时测试的Sql Fiddle .

这篇关于获取每个组的下一个最小值，大于或等于给定值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！