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

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

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

 

image

 

用程式來講蠻單純的,只要取得目前的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 

 


image


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


--


Reference


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


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