[edit]
1 襷: 2007-07-07 #
覓碁 伎貎朱Μ 螳讌 蠍磯れ 襾碁Μ ~ 讌螳. not in企 order by 襯 伎 覦覯, 伎 豌覯讌 Row襯 谿城 覦覯煙 螳. 伎蟇 伎覯瑚 讀螳 襦 れ 伎 貎朱Μ襯 螳 覦覯 蟾 螳. 蠏碁 sql server 2005 cte襯 伎企慨蠍磯 . cte 蠏貎朱Μ 焔レ 譬讌 螻 朱, 蠏語 谿語譴襷覩襦 郁鍵襦 . 螻豸牛企蟾...
蠍一ヾ 蟆 伎 蟲覦覯螻朱 るゴ蟆 伎覯瑚 企 貎朱Μ 焔レ 觜訣. る 朱朱 蟆覓殊 襷 10螳 伎 給 襴讌 る 螳. 覓朱 蟇一 覈 蟆覓殊 襴 10螳 危狩螻..危語 蟆曙 伎螳 蟆 襷 豌螳 襷螳 襴 襴 蟆企..覘..伎 碁蟶 螳...覘..碁讌 矩.....50 襷 蟇 ｊ ろ誤企慨...碁..伎蟇 ろ ろ瑚 蠍磯 ..襷...譟磯 覃...豢 碁殊...碁煙るゼ 譬 覺 蟆蟲....2007 07 10......襷讌襷 伎 襷れ企願鍵螳 麹 觜′蟲襷...
[edit]
2 #
*/
--企 覦 一危
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
[edit]
3 ろ蟆郁骸 #
3覯讌 page襯 10螳 蟆覓殊 覲願 蟆曙
exec usp_select_bbs 3, 10
企 'bbs'. 蟆 1, 朱Μ 所鍵 3, 覓朱Μ 所鍵 0, 覩碁Μ 所鍵 0
企 'Worktable'. 蟆 2, 朱Μ 所鍵 77, 覓朱Μ 所鍵 0, 覩碁Μ 所鍵 0
企 'bbs'. 蟆 17, 朱Μ 所鍵 68, 覓朱Μ 所鍵 0, 覩碁Μ 所鍵 0
10000覯讌 page襯 10螳 蟆覓殊 覲願 蟆曙
exec usp_select_bbs 10000, 10
企 'bbs'. 蟆 1, 朱Μ 所鍵 3, 覓朱Μ 所鍵 0, 覩碁Μ 所鍵 0
企 'Worktable'. 蟆 2, 朱Μ 所鍵 83, 覓朱Μ 所鍵 0, 覩碁Μ 所鍵 0
企 'bbs'. 蟆 18, 朱Μ 所鍵 72, 覓朱Μ 所鍵 0, 覩碁Μ 所鍵 0
-- 2020-02-24 16:37:50
襯 襦讌. 企蟆 讌 蟆 覃 譬蟆給.
蠏碁磯 襦讌 蟆曙磯 蟆蠍 螻る讌 蟇瑚? where question_num begin between end
螳 蟆曙, 豕 10蟾讌 讌企螻 1~10蟾讌 question_num 9覯 殊覃 1~8覯螻 10覯襷 豢ル 蟆 螳給. -- ! 2023-06-22 12:58:04
鏤