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

given the following Table1:

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

and Table2 containing some RefIDs and intVals like

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

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

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

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

    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

help would be appreciated !

帮助将不胜感激！

3 个解决方案

#1

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

派生表a从table1中检索最小值，给定ref2和来自table2的intVal;外部查询仅检索someValue。

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

Here is Sql Fiddle with live test.

这是Sql Fiddle的实时测试。

#2

You can solve this using the ROW_NUMBER() function:

您可以使用ROW_NUMBER（）函数解决此问题：

SELECT
  RefID,
  intVal,
  NextGt,
  SomeVal,
FROM
  (
    SELECT
      t2.RefID,
      t2.intVal,
      t1.intVal AS NextGt,
      t1.SomeVal,
      ROW_NUMBER() OVER (PARTITION BY t2.RefID, t2.intVal ORDER BY t1.intVal) AS rn
    FROM
      dbo.Table2 AS t2
      LEFT JOIN dbo.Table1 AS t1 ON t1.RefID = t2.RefID AND t1.intVal >= t2.intVal
  ) s
WHERE
  rn = 1
;

The derived table matches each Table2 row with all Table1 rows that have the same RefID and an intVal that is greater than or equal to Table2.intVal. Each subset of matches is ranked and the first row is returned by the main query.

派生表将每个Table2行与具有相同RefID且intVal大于或等于Table2.intVal的所有Table1行匹配。对每个匹配子集进行排序，并且主查询返回第一行。

The nested query uses an outer join, so that those Table2 rows that have no Table1 matches are still returned (with nulls substituted for the Table1 columns).

嵌套查询使用外部联接，因此仍返回没有Table1匹配的那些Table2行（使用null替换Table1列）。

Alternatively you can use OUTER APPLY:

或者，您可以使用OUTER APPLY：

SELECT
  t2.RefID,
  t2.intVal,
  t1.intVal AS NextGt,
  t1.SomeVal
FROM
  dbo.Table2 AS t2
  OUTER APPLY
  (
    SELECT TOP (1)
      t1.intVal
    FROM
      dbo.Table1 AS t1
    WHERE
      t1.RefID = t2.RefID
      AND t1.intVal >= t2.intVal
    ORDER BY
      t1.intVal ASC
  ) AS t1
;

This method is arguably more straightforward: for each Table2 row, get all matches from Table1 based on the same set of conditions, sort the matches in the ascending order of Table1.intVal and take the topmost intVal.

这个方法可以说更简单：对于每个Table2行，根据相同的条件集从Table1获取所有匹配，按Table1.intVal的升序对匹配进行排序，并取最顶端的intVal。

#3

This can be done with a join, group by, and a case statement, and a trick:

这可以通过join，group by和case语句以及技巧来完成：

select t1.refid, t2.intval,
       min(case when t1.intval > t2.intval then t1.intval end) as min_greater_than_ref,
       substring(min(case when t1.intval > t2.intval
                          then right('00000000'+cast(t1.intval as varchar(255)), 8)+t1.SomeVal)
                     end)), 9, 1000)
from table1 t1 left join
     table2 t2
     on t1.refid = t2.refid
group by t1.refid, t2.intval

SO, the trick is to prepend the integer value to SomeValue, zero-padding the integer value (in this case to 8 characters). You get something like: "00000020val01". The minimum on this column is based on the minimum of the integer. The final step is to extract the value.

所以，诀窍是将整数值添加到SomeValue，将整数值填充为零（在本例中为8个字符）。你会得到类似的东西：“00000020val01”。此列的最小值基于整数的最小值。最后一步是提取值。

For this example, I used SQL Server syntax for the concatenation. In other databases you might use CONCAT() or ||.

对于此示例，我使用SQL Server语法进行连接。在其他数据库中，您可以使用CONCAT（）或||。

#1