/********************************************************************************************
-- Title : [SQL2012] 간단한 게시판 및 정렬 구현(offset~fetch 이용)
-- Key word : 게시판 board 정렬 order 페이징 paging offset fetch 옵셋
********************************************************************************************/
[2k8] 간단한 게시판 페이징 및 정렬 구현
use tempdb;go
if (object_id('ttt') is not null) drop table ttt;
create table ttt( id int not null identity(1,1), ti nvarchar(100) not null , cntnt nvarchar(100) not null, dt nchar(8));
insert into ttt (ti, cntnt, dt)select '1a','aaaaaaaaaaaaaaaa','64052942' union allselect '1b','bbbbbbbb','74548791' union allselect 'c','ccccccccccccccccccc','6801764' union allselect 'd','dddddddddddddddddddddd','18924935' union allselect 'e','eeeeeeeeeeeeeeeeeeeeeeeeee','31745271' union allselect 'f','ffffffffffffff','45104765' union allselect 'g','ggggggg','58832284' union allselect 'h','hhhhhhhhhhh','20622497' union allselect 'i','iiiiiiiii','168210' union allselect 'j','jjjjjjjjjjjjjj','93725101' union allselect 'k','kkkk','73437448' union allselect 'l','llllllllllll','73916259' union allselect 'm','mmmmmmmmmmmmmmm','63226110' union allselect 'n','nnnnnnnnnnnnnnnnnnnnnn','89381602' union allselect 'o','ooooooooooooo','1406258' union allselect 'p','pp','4658064' union allselect 'q','q','43126025' union allselect 'r','rrrr','43652985' union allselect 's','ssssssssssss','71359108' union allselect 't','tttttttttt','5371203' union allselect 'u','uuuuuuuuuuuu','44450904' union allselect 'v','vvvvv','13489010' union allselect 'w','wwwwwwwwww','80313525' union allselect 'x','xxxxxxxxxxxxxx','9038415' union allselect 'y','y','26895779' union allselect 'z','zzzzzzzzzz','63313872' union allselect 'aa','aaaaaaaaaaaaaaaaaaaaaa','33566495' union allselect 'bb','bbbbbbbbbbbbbbbbbbbbbbbbbb','5656510' union allselect 'cc','cccccccccccccccccccccccccccccccccccccc','85651701' union allselect 'dd','','72452337' union allselect 'ee','ee','80190564' union allselect 'ff','ffffffffffffffffffffffffffffffffffffffffffffffffffffffff','35817497' union allselect 'gg','gggggggggggggggggggggggggggggggggggggg','67168990' union allselect 'hh','hhhhhhhhhhhhhhhhhhhhhhhhhh','82203546' union allselect 'ii','iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii','34606848' union allselect 'jj','jjjjjjjjjjjjjjjjjjjjjj','77631735' union allselect 'kk','kk','26830033' union allselect 'll','llllllllllllllllllllllllllllllll','37433720' union allselect 'mm','mmmmmm','35696528' union allselect 'nn','nnnn','24871974' union allselect 'oo','oooooooooo','9415573' union allselect 'pp','pppppppppppppppppppppppppppppppppp','90460739' union allselect 'qq','qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq','87864894' union allselect 'rr','rrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrr','8589107' union allselect 'ss','ssssssssssssssssssssssssssssssssssssssss','70073687' union allselect 'tt','tttt','27105415' union allselect 'uu','uuuuuuuuuuuuuuuuuuuuuuuuuuuuuu','21385725' union allselect 'vv','vvvvvvvvvvvvvvvv','86102411' union allselect 'ww','','29408282' union allselect 'xx','xxxxxxxxxxxxxxxxxx','39257381' union allselect 'yy','yyyyyyyyyyyyyy','63459911' union allselect 'zz','zzzzzzzzzzzzzzzzzzzzzzzz','74868285' union allselect 'aaa','aaaaaaaaaaaaaaaaaaaaaaaaaaa','71236153' union allselect 'bbb','bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb','95385402' union allselect 'ccc','ccccccccccccccccccccc','11773667' union allselect 'ddd','ddddddddddddddddddddddddddddddddddddddddddddddddddd','98221804' union allselect 'eee','eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee','3612327' union allselect 'fff','fff','20618724' union allselect 'ggg','','32981589' union allselect 'hhh','hhhhhhhhh','77936009' union allselect 'iii','iiiiiiiiiiiiiii','3260894' union allselect 'jjj','jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj','57477735' union allselect 'kkk','kkkkkk','3437300' union allselect 'lll','lllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllll','29493780' union allselect 'mmm','mmmmmmmmmmmmmmmmmmmmmmmmmmmm','89729456' union allselect 'nnn','nnnnnnnnnnnnnnn','68382022' union allselect 'ooo','oooooooooooooooooooooooooooooooooooooooooo','60855973' union allselect 'ppp','ppppppppppppppppppppppppppppppppp','18908783' union allselect 'qqq','qqqqqqqqqqqqqqqqqqqqqqqqqqqqqq','68596801' union allselect 'rrr','rrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrr','96542515' union allselect 'sss','sssssssssssssssssssssssssssssssssssssss','16789782' union allselect 'ttt','ttttttttttttttttttttttttttttttttt','71768544' union allselect 'uuu','uuuuuuuuuuuuuuu','48024218' union allselect 'vvv','vvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvv','21253596' union allselect 'www','wwwwwwwwwwwwwwww','49983154' union allselect 'xxx','xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx','70757231' union allselect 'yyy','yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy','49749874' union allselect 'zzz','zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz','49495872';
select * from ttt;
/**********************************************************************/declare @page int = 2, @row int = 10;--------------------------------------declare @offset int;set @offset = (@page - 1) * @row;
select id, ti, cntnt, dtfrom tttorder by id descoffset @offset rows
fetch next @row rows only;
/**********************************************************************/