从同一表的行中选择值和下一个最高值

BlueChippy 发表于 Dev

BlueChippy

我有这样的桌子

LowerPoints MessageTemplate
0           zero
10          ten
100         hundred

我想从中选择，所以我有一个列表，lowerPoints然后是LowerPoints

例如

select
    low.LowerPoints As LowerPoints
    , high.LowerPoints as UpperPoints
    , low.MessageTemplate as MessageTemplate
from MessageTemplate low
    , MessageTemplate high
where high.LowerPoints = (select top 1 LowerPoints from MessageTemplate where LowerPoints > low.LowerPoints order by LowerPoints)

哪个返回

LowerPoints UpperPoints MessageTemplate
0           10          zero
10          100         ten

但我看不到如何获取第三个值并将其赋予“空”值UpperPoints。即最后一行是

100                     hundred

布米

您使用过时的语法（用逗号分隔表）的使用将被视为INNER JOIN。您可以将其编写为：

select
    low.LowerPoints As LowerPoints
    , high.LowerPoints as UpperPoints
    , low.MessageTemplate as MessageTemplate
from MessageTemplate low
LEFT JOIN MessageTemplate high
ON high.LowerPoints = (select top 1 LowerPoints from MessageTemplate where LowerPoints > low.LowerPoints order by LowerPoints)

您也可以使用简单的子选择

Declare @a Table (LowerPoints int, MessageTemplate varchar(30))
insert into @a Values(0,'zero'),(10,'ten'),(100,'hundred')

Select a.LowerPoints
     ,(Select top 1 LowerPoints from @a b 
       where b.LowerPoints>a.LowerPoints
       order by b.LowerPoints) as UpperPoints
     ,a.MessageTemplate
from @a a
Order by LowerPoints

或CTE在条件b.rn = a.rn + 1的情况下使用其自身的LEFT JOIN生成定义的行号（rn）

;With CTE as
(
Select LowerPoints,MessageTemplate
       ,ROW_NUMBER() OVER (ORDER by LowerPoints) as rn
from @a a
)
Select c1.LowerPoints
      ,c2.LowerPoints as UpperPoints
      ,c1.MessageTemplate 
from CTE c1
LEFT JOIN CTE c2 on c2.rn=c1.rn +1 
Order by c1.LowerPoints

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。