[MS SQL]如何抓取某筆資料的上下筆(Cross Join)

朋友問到，如何將下圖的資料，抓取指定的上下筆，以新聞輪播的功能來舉例：

用程式來講蠻單純的，只要取得目前的ID，就可以用select top 1 + where條件就可以完成，

但這樣有可能要connection兩次，所以就寫了一個Script + CROSS JOIN 來解決 :P

Script

declare @TABLE TABLE (ID int,Title nvarchar(50))
declare @tagetID int

--新增資料
INSERT INTO @TABLE VALUES(1,N'新聞標題1')
INSERT INTO @TABLE VALUES(2,N'新聞標題2')
INSERT INTO @TABLE VALUES(3,N'新聞標題3')
INSERT INTO @TABLE VALUES(4,N'新聞標題4')
INSERT INTO @TABLE VALUES(5,N'新聞標題5')

select * from @TABLE

set @tagetID = 3 --抓取ID為3的上下筆

SELECT P.PrevID,P.Name as 'Prev News', N.NextID,N.Name as 'Next News' FROM
(
    SELECT MAX(A.id) PrevID,
    (select Title FROM @TABLE where id = MAX(A.id)) Name
    from @TABLE A
    where A.id < @tagetID
) P
CROSS JOIN
(
    SELECT MIN(A.id) NextID,
    (select Title FROM @TABLE where id = MAX(A.id)) Name
    from @TABLE A
    where A.id > @tagetID
) N

CROSS JOIN

Cross Join 是一個實現笛卡兒乘積 (Cartesian Product)的語法，已兩個table來講，如tableA資料5筆，table資料4筆，select出來就會是20筆，用文字說明有點複雜，以下寫個Sample :

declare @employee TABLE (empID int,Name nvarchar(50)) --員工表
declare @Dept TABLE (DeptID int,Name nvarchar(50)) --部門表

--新增資料
INSERT INTO @employee VALUES(1,N'周杰倫')
INSERT INTO @employee VALUES(2,N'蕭敬騰')
INSERT INTO @employee VALUES(3,N'方大同')

INSERT INTO @Dept VALUES(1,N'財務部')
INSERT INTO @Dept VALUES(2,N'行銷部')
INSERT INTO @Dept VALUES(3,N'研發部')


select * from @employee cross join @Dept -- 3 * 3 9筆資料

--結果等同於
select * from @employee,@Dept 

使用如果不慎注意會是效能殺手，譬如1000*1000筆資料吃的效能可是很可怕的，而回到上例，其實上一頁跟下一頁都只是會有一筆資料而已，故只是很簡單的應用讓他查詢出來會是一筆記錄

--

Reference

http://technet.microsoft.com/zh-tw/library/ms190690(v=sql.105).aspx

http://blog.csdn.net/xiaolinyouni/article/details/6943337