drop table [tb]
GO
--示例数据
create table [tb]([id] int identity(1,1),[pid] int,name varchar(20))
insert [tb] select 0,'中国'
union all select 0,'美国'
union all select 0,'加拿大'
union all select 1,'北京'
union all select 1,'上海'
union all select 1,'江苏'
union all select 6,'苏州'
union all select 7,'常熟'
union all select 6,'南京'
union all select 6,'无锡'
union all select 2,'纽约'
union all select 2,'旧金山'
go
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_id]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_id]
GO
/*--树形数据处理
级别及排序字段
--邹建 2003-12(引用请保留此信息)--*/
/*--调用示例
--调用函数实现分级显示
select replicate('-',b.[level]*4)+a.name
from [tb] a,f_id()b
where a.[id]=b.[id]
order by b.sid
--*/
create function f_id()
returns @re table([id] int,[level] int,sid varchar(8000))
as
begin
declare @l int
set @l=0
insert @re select [id],@l,right(10000+[id],4)
from [tb] where [pid]=0
while @@rowcount>0
begin
set @l=@l+1
insert @re select a.[id],@l,b.sid+right(10000+a.[id],4)
from [tb] a,@re b
where a.[pid]=b.[id] and b.[level]=@l-1
end
return
end
go
--调用函数实现分级显示
select replicate('-',b.[level]*4)+a.name
from [tb] a,f_id()b
where a.[id]=b.[id]
order by b.sid
go
--删除测试
drop table [tb]
drop function f_id
go
/*--结果
中国
----北京
----上海
----江苏
--------苏州
------------常熟
--------南京
--------无锡
美国
----纽约
----旧金山
加拿大
--*/
树形结构统一使用下面的测试表与测试数据CREATE TABLE test_tree (
test_id INT,
pid INT,
test_val VARCHAR(10),
PRIMARY KEY (test_id)
)
INSERT INTO test_tree VALUES(1, NULL, '.NET')
INSERT INTO test_tree VALUES(2, 1, 'C#')
INSERT INTO test_tree VALUES(3, 1, 'J#')
INSERT INTO test_tree VALUES(4, 1, 'ASP.NET')
INSERT INTO test_tree VALUES(5, 1, 'VB.NET')
INSERT INTO test_tree VALUES(6, NULL, 'J2EE')
INSERT INTO test_tree VALUES(7, 6, 'EJB')
INSERT INTO test_tree VALUES(8, 6, 'Servlet')
INSERT INTO test_tree VALUES(9, 6, 'JSP')
INSERT INTO test_tree VALUES(10, NULL, 'Database')
INSERT INTO test_tree VALUES(11, 10,'DB2')
INSERT INTO test_tree VALUES(12, 10,'MySQL')
INSERT INTO test_tree VALUES(13, 10,'Oracle')
INSERT INTO test_tree VALUES(14, 10,'SQL Server')
INSERT INTO test_tree VALUES(15, 13,'PL/SQL')
INSERT INTO test_tree VALUES(16, 15,'Function')
INSERT INTO test_tree VALUES(17, 15,'Procedure')
INSERT INTO test_tree VALUES(18, 15,'Package')
INSERT INTO test_tree VALUES(19, 15,'Cursor')
INSERT INTO test_tree VALUES(20, 14,'T-SQL')
Oracle
使用 START WITH CONNECT BY
语句实现树状查询
SQL>ed
Wrote file afiedt.buf
1 SELECT
2LPAD(' ', 2*(LEVEL-1)) || test_val AS test_val
3 FROM
4test_tree
5 START WITH
6test_id IN (1, 6, 10)
7* CONNECT BY PRIOR test_id = pid
SQL>/
TEST_VAL
-----------------------------------------------------------
.NET
C#
J#
ASP.NET
VB.NET
J2EE
EJB
Servlet
JSP
Database
DB2
TEST_VAL
-----------------------------------------------------------
MySQL
Oracle
PL/SQL
Function
Procedure
Package
Cursor
SQL Server
T-SQL
20 rows selected.
SQL Server
使用 Common Table Expression (CTE) 来实现 递归调用。
1>WITH StepCTE
2>AS
3>(
4>SELECT
5> test_id,
6> pid,
7> test_val,
8> 1 as Lev
9>FROM
10> test_tree
11>WHERE
12> test_id IN (1,6,10)
13>UNION ALL
14>SELECT
15> T.test_id,
16> T.pid,
17> T.test_val,
18> CTE.Lev + 1
19>FROM
20> test_tree T INNER JOIN StepCTE CTE
21> ON T.pid = CTE.test_id
22>)
23>SELECT
24> test_id, pid, test_val, Lev
25>FROM StepCTE
26>go
test_id pid test_val Lev
----------- ----------- ---------- -----------
1NULL .NET 1
6NULL J2EE 1
10NULL Database 1
11 10 DB2 2
12 10 MySQL2
13 10 Oracle 2
14 10 SQL Server 2
20 14 T-SQL3
15 13 PL/SQL 3
16 15 Function 4
17 15 Procedure4
18 15 Package 4
19 15 Cursor 4
7 6 EJB 2
8 6 Servlet 2
9 6 JSP 2
2 1 C# 2
3 1 J# 2
4 1 ASP.NET 2
5 1 VB.NET 2
(20 行受影响)
CREATE PROCEDURE init_reportUrl()BEGIN
DECLARE s INT DEFAULT 0
DECLARE r_id bigint(10)
DECLARE report CURSOR FOR select distinct id as r_id FROM ReportHotLine
DECLARE CONTINUE HANDLER FOR NOT FOUND SET s=1
-- 打开游标
open report
-- 将游标中的值赋值给变量,注意:变量名不要和返回的列名同名,变量顺序要和sql结果列的顺序一致
fetch report into r_id
-- 当s不等于1,也就是未遍历完时,会一直循环
while s<>1 do
-- 执行业务逻辑
UPDATE wh_csyx . dy_pres30207800013_001_local a
JOIN ReportHotLine b ON a.gdId = b.ID
AND b.createtime = ( SELECT max( createtime ) FROM ReportHotLine WHERE ID = r_id )
SET blms = b.banliInfo where a.gdId =r_id
-- 将游标中的值再赋值给变量,供下次循环使用
fetch report into r_id
-- 当s等于1时表明遍历以完成,退出循环
end while
-- 关闭游标
close report
END
call init_reportUrl()
欢迎分享,转载请注明来源:内存溢出
评论列表(0条)