_ | 覦覈襦 | 豕蠏手 | 殊螳 | 譯殊碁
FrontPage › 螻豸牛蟆

Contents

1 襷: 2007-07-07
2
3 ろ蟆郁骸


1 襷: 2007-07-07 #


覓碁 伎貎朱Μ 螳讌 蠍磯れ 襾碁Μ ~ 讌螳. not in企 order by 襯 伎 覦覯, 伎 豌覯讌 Row襯 谿城 覦覯煙 螳. 伎蟇 伎覯瑚 讀螳 襦 れ 伎 貎朱Μ襯 螳 覦覯 蟾 螳. 蠏碁 sql server 2005 cte襯 伎企慨蠍磯 . cte 蠏貎朱Μ 焔レ 譬讌 螻 朱, 蠏語 谿語譴襷覩襦 郁鍵襦 . 螻豸牛企蟾...

蠍一ヾ 蟆 伎 蟲覦覯螻朱 るゴ蟆 伎覯瑚 企 貎朱Μ 焔レ 觜訣. る 朱朱 蟆覓殊 襷 10螳 伎 給 襴讌 る 螳. 覓朱 蟇一 覈 蟆覓殊 襴 10螳 危狩螻..危語 蟆曙 伎螳 蟆 襷 豌螳 襷螳 襴 襴 蟆企..覘..伎 碁蟶 螳...覘..碁讌 矩.....50 襷 蟇 j ろ誤企慨...碁..伎蟇 ろ ろ瑚 蠍磯 ..襷...譟磯 覃...豢 碁殊...碁煙るゼ 譬 覺 蟆蟲....2007 07 10......襷讌襷 伎 襷れ企願鍵螳 麹 觜′蟲襷...

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

3 ろ蟆郁骸 #

3覯讌 page襯 10螳 蟆覓殊 覲願 蟆曙

exec usp_select_bbs 3, 10

bbs01.jpg
企 'bbs'. 蟆 1, 朱Μ 所鍵 3, 覓朱Μ 所鍵 0, 覩碁Μ 所鍵 0
企 'Worktable'. 蟆 2, 朱Μ 所鍵 77, 覓朱Μ 所鍵 0, 覩碁Μ 所鍵 0
企 'bbs'. 蟆 17, 朱Μ 所鍵 68, 覓朱Μ 所鍵 0, 覩碁Μ 所鍵 0

10000覯讌 page襯 10螳 蟆覓殊 覲願 蟆曙

exec usp_select_bbs 10000, 10

bbs02.jpg
企 'bbs'. 蟆 1, 朱Μ 所鍵 3, 覓朱Μ 所鍵 0, 覩碁Μ 所鍵 0
企 'Worktable'. 蟆 2, 朱Μ 所鍵 83, 覓朱Μ 所鍵 0, 覩碁Μ 所鍵 0
企 'bbs'. 蟆 18, 朱Μ 所鍵 72, 覓朱Μ 所鍵 0, 覩碁Μ 所鍵 0
蠍 蠍郁鍵..
企: : るジ讓曙 襦螻豺 企Ν 譯殊語. 襦螻豺
EditText : Print : Mobile : FindPage : DeletePage : LikePages : Powered by MoniWiki : Last modified 2018-04-13 23:12:53

螻 覦覯 麹. 覦螻 豎 螳 蟆 れ 螳螳 覈 襯 襷り鍵 覓語企. (譟 貊磯ゴ)