*/
--企 覦 一危
create database sample
go
use sample
go
--drop table bbs
create table bbs(
seq int identity(1,1) primary key nonclustered
, question_num int
, parent_num int
, cust_id varchar(20)
, title varchar(255)
, contents varchar(7000)
);
create clustered index cix_question_num on bbs(question_num);
--create index nix_parent_num on bbs(parent_num);
--create index nix_seq on bbs(seq);
go
insert bbs(question_num, parent_num, cust_id, title, contents) values(1, 0, 'yasicom', '讌覓', 'sql 襾語?');
insert bbs(question_num, parent_num, cust_id, title, contents) values(1, 1, 'yasicom', '給', '給.');
insert bbs(question_num, parent_num, cust_id, title, contents) values(1, 2, 'yasicom', '給', '給.');
insert bbs(question_num, parent_num, cust_id, title, contents) values(1, 2, 'yasicom', '給', '給.');
insert bbs(question_num, parent_num, cust_id, title, contents) values(1, 3, 'yasicom', '給', '給.');
insert bbs(question_num, parent_num, cust_id, title, contents) values(2, 0, 'yasicom', '讌覓', 'sql 襾語?');
insert bbs(question_num, parent_num, cust_id, title, contents) values(1, 2, 'yasicom', '給', '給.');
insert bbs(question_num, parent_num, cust_id, title, contents) values(2, 1, 'yasicom', '給', '給.');
insert bbs(question_num, parent_num, cust_id, title, contents) values(2, 1, 'yasicom', '給', '給.');
insert bbs(question_num, parent_num, cust_id, title, contents) values(2, 1, 'yasicom', '給', '給.');
insert bbs(question_num, parent_num, cust_id, title, contents) values(1, 5, 'yasicom', '給', '給.');
insert bbs(question_num, parent_num, cust_id, title, contents) values(1, 4, 'yasicom', '給', '給.');
set nocount on
set statistics io off
declare @i int
set @i = 3
begin tran
while(@i <= 500000)
begin
insert bbs(question_num, parent_num, cust_id, title, contents)
values(@i, 0, 'yasicom', '讌覓', 'sql 襾語?');
set @i = @i + 1
end
commit
go
insert bbs(question_num, parent_num, cust_id, title, contents) values(499980, 499989, 'yasicom', '給', '給.');
insert bbs(question_num, parent_num, cust_id, title, contents) values(499980, 499989, 'yasicom', '給', '給.');
insert bbs(question_num, parent_num, cust_id, title, contents) values(499980, 500012, 'yasicom', '給', '給.');
insert bbs(question_num, parent_num, cust_id, title, contents) values(499980, 500011, 'yasicom', '給', '給.');
insert bbs(question_num, parent_num, cust_id, title, contents) values(499980, 500014, 'yasicom', '給', '給.');
insert bbs(question_num, parent_num, cust_id, title, contents) values(400010, 400020, 'yasicom', '給', '給.');
insert bbs(question_num, parent_num, cust_id, title, contents) values(400010, 500016, 'yasicom', '給', '給.');
insert bbs(question_num, parent_num, cust_id, title, contents) values(400010, 500017, 'yasicom', '給', '給.');
insert bbs(question_num, parent_num, cust_id, title, contents) values(400010, 500018, 'yasicom', '給', '給.');
insert bbs(question_num, parent_num, cust_id, title, contents) values(400010, 500019, 'yasicom', '給', '給.');
insert bbs(question_num, parent_num, cust_id, title, contents) values(400010, 500017, 'yasicom', '給', '給.');
go
create proc usp_select_bbs
@page_num int
, @page_row_cnt int
as
--set statistics io on
--declare
-- @page_num int
--, @page_row_cnt int
--, @max_question_num int;
--
--set @page_num = 45;
--set @page_row_cnt = 10;
--set @max_question_num = (select max(question_num) from bbs);
declare
@max_question_num int
, @begin int
, @end int
, @end_ck int
, @cnt int
set @max_question_num = (select max(question_num) from bbs);
set @begin = @max_question_num - ((@page_num) * @page_row_cnt);
set @end = @max_question_num - ((@page_num - 1) * @page_row_cnt);
set @end_ck = case when @end > 0 then 0 else 1 end;
set @begin = case when @end > 0 then @begin else 1 end;
set @end = case when @end > 0 then @end else @page_row_cnt end;
if @end_ck = 0 --襷讌襷伎螳 覃...
begin
with t_recursive(seq, question_num, parent_num, cust_id, title, contents, level, sort)
as
(
--Root..
select seq, question_num, parent_num, cust_id, title, contents, 0 as level, cast(seq as varchar(8000)) sort
from bbs
where parent_num = 0 --root 譟郁唄
and question_num between @begin and @end
union all
select a.seq, a.question_num, a.parent_num, a.cust_id, a.title,
a.contents, b.level + 1 as level, cast(b.sort as varchar(4000)) + cast(a.seq as varchar(4000))
from bbs a inner join t_recursive b --蠏貎朱Μ
on a.parent_num = b.seq
and a.question_num between @begin and @end
)
select top(@page_row_cnt)
seq
, question_num
, cust_id
, case when parent_num > 0 then '[' + cast(parent_num as varchar) + ']' else '' end +
cast(replicate(' RE: ', level) + cast(title as varchar) as varchar(250)) as tree_title
, contents
from t_recursive a
order by
question_num desc
, case when level = 0 then '0' else sort end
option(maxdop 1);
end else --襷讌襷伎企..
begin
with t_recursive(seq, question_num, parent_num, cust_id, title, contents, level, sort)
as
(
--Root..
select seq, question_num, parent_num, cust_id, title, contents, 0 as level, cast(seq as varchar(8000)) sort
from bbs
where parent_num = 0 --root 譟郁唄
and question_num between @begin and @end
union all
select a.seq, a.question_num, a.parent_num, a.cust_id, a.title,
a.contents, b.level + 1 as level, cast(b.sort as varchar(4000)) + cast(a.seq as varchar(4000))
from bbs a inner join t_recursive b --蠏貎朱Μ
on a.parent_num = b.seq
and a.question_num between @begin and @end
)
select
seq
, question_num
, cust_id
, case when parent_num > 0 then '[' + cast(parent_num as varchar) + ']' else '' end +
cast(replicate(' RE: ', level) + cast(title as varchar) as varchar(250)) as tree_title
, contents
, row_number() over(order by question_num desc, case when level = 0 then '0' else sort end) rownum
into #temp
from t_recursive a
order by
question_num desc
, case when level = 0 then '0' else sort end
option(maxdop 1);
set @cnt = @@rowcount;
select
seq
, question_num
, cust_id
, tree_title
, contents
from #temp
where rownum > (@cnt / @page_row_cnt) * @page_row_cnt;
end
go
