获取每个组的下一个最小值，大于或等于给定值 [英] Get next minimum, greater than or equal to a given value for each group

问题描述

给出以下表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 Table1 following 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 .

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