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

Question

给定以下Table1：

    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 和 intVals，例如：

    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。以下是预期结果。

    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 !

- armen

---

你在使用哪种数据库管理系统？是PostgreSQL？还是Oracle？DB2？Firebird？ - user330315

你尝试过什么？ - Andriy M

Answer 1

派生表a从table1中获取给定refid和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

这里是带有实时测试的Sql Fiddle。

- Nikola Markovinović

---

这太棒了，正是我在寻找的！非常感谢@Nikola。这个查询帮助我从两个不同的表中获取具有相似ID的最小值。我的第一个结果超过了500K，仅使用您的带有distinct函数的查询后，我的结果约为15K。我真的很感激！ - PatsonLeaner

Answer 2

您可以使用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
;

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

嵌套查询使用外联接，以便仍然返回那些没有Table1匹配项的Table2行（在Table1列中用null代替）。

或者你可以使用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
;

这种方法可以说更加直观：对于每一行Table2，在同样的条件下从Table1中获取所有匹配项，并按照Table1.intVal的升序排序，然后取最顶部的intVal。

- Andriy M

Answer 3

这可以通过使用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 

所以，诀窍是在SomeValue之前添加整数值，将整数值进行零填充（在此情况下为8个字符）。你会得到类似于："00000020val01"的东西。这一列的最小值基于整数的最小值。最后一步是提取值。

对于这个例子，我使用了SQL Server语法进行连接。在其他数据库中，您可能会使用CONCAT()或||。

- Gordon Linoff