SQL server语言 - 从当前行中查找上一行和下一行的查询，其中包括order by子句

Question

我执行以下查询:

SELECT quotesid 
FROM quotestable 
WHERE quotesauthor LIKE '%Mahatma%'  
ORDER BY popularity DESC

这给了我以下结果:(quotesid -是主键)

 968
 158
 159
 160
 161

我的要求:

我需要下一个和前一个quotesid (ORDER BY popularity DESC)。例如，如果我在第158条记录中。下一个和前一个值应该是159和968分别，我需要这在一个单一的查询。

my query

SELECT *  
FROM quotestable 
WHERE 
    (quotesid = (SELECT MIN(quotesid) FROM quotestable where quotesid > 158 and quotesauthor like '%Mah%' ) 
 OR quotesid = (SELECT MAX(quotesid) FROM quotestable where quotesid < 158 and quotesauthor like '%Mah%' ))
ORDER BY popularity DESC

这不起作用。这只给了我一个记录——159。是否有一种方法可以编写单个查询并获得所需的结果?

操纵:

sql-fiddle

Answer 1

也许这对您有用。它给你的查询row number索引，然后left outer join本身被+1和-1记录移位，产生[next]和[previous]列。

with cte as
(
    select quotesid, ROW_NUMBER() over (order by popularity desc) as [RowNo]
    from quotestable
)
select cte.quotesid, [next].quotesid as [Next], [prev].quotesid as [Previous]
from cte left outer join cte as [next] on cte.RowNo = ([next].RowNo - 1)
         left outer join cte as [prev] on cte.RowNo = ([prev].RowNo + 1)

结果:

quotesid Next   Previous
------------------------
968      158    NULL
158      159    968
159      160    158
160      161    159
161      NULL   160

sql小提琴:http://sqlfiddle.com/!6/0ac0b/4

Answer 2

select quotestable.quotesid, b.quotesid nextQuoteID, c.quotesid previousQuoteID from quotestable
join (
    select a.quotesID, max(b.popularity) previousQuoteID, min(c.popularity) nextQuoteID
    from quotestable a
    left join quotestable b
    on a.popularity > b.popularity
    left join quotestable c
    on a.popularity < c.popularity
    where a.author like '%Mahatma%'
    group by a.quotesid
) x
on quotestable.quotesid = x.quotesid
left join quotestable b
on b.popularity = x.previousquoteid
left join quotestable c
on c.popularity = x.nextquoteid
order by quotestable.popularity desc

Answer 3

我想这会给你你想要的:

;WITH t AS (
    SELECT *, ROW_NUMBER() OVER (ORDER BY popularity DESC) rn
    FROM quotestable
    WHERE quotesauthor LIKE '%Mah%')
SELECT *
FROM t
WHERE 1 = ABS(t.rn - (SELECT rn FROM t WHERE quotesid = 158))

你可以用它来把上一页和下一页放在一行:

;WITH t AS (
    SELECT *, ROW_NUMBER() OVER (ORDER BY popularity DESC) rn
    FROM quotestable
    WHERE quotesauthor LIKE '%Mah%')
SELECT t1.quotesid, t2.quotesid PrevID, t3.quotesid NextID
FROM t t1 
    LEFT JOIN t t2 ON t1.rn = t2.rn - 1
    LEFT JOIN t t3 ON t1.rn = t3.rn + 1
WHERE t1.rn = (SELECT rn FROM t WHERE quotesid = 158)