--测试数据
CREATE TABLE tb(ID char(3),PID char(3),Name nvarchar(10))
INSERT tb SELECT '001',NULL ,'山东省'
UNION ALL SELECT '002','001','烟台市'
UNION ALL SELECT '004','002','招远市'
UNION ALL SELECT '003','001','青岛市'
UNION ALL SELECT '005',NULL ,'四会市'
UNION ALL SELECT '006','005','清远市'
UNION ALL SELECT '007','006','小分市'
GO--广度搜索排序函数
CREATE FUNCTION f_Sort(@ID char(3)=NULL,@sort int=1)
RETURNS @t_Level TABLE(ID char(3),sort int)
AS
BEGIN
DECLARE tb CURSOR LOCAL
FOR
SELECT ID FROM tb
WHERE PID=@ID
OR(@ID IS NULL AND PID IS NULL)
OPEN TB
FETCH tb INTO @ID
WHILE @@FETCH_STATUS=0
BEGIN
INSERT @t_Level VALUES(@ID,@sort)
SET @sort=@sort+1
IF @@NESTLEVEL<32 --如果递归层数未超过32层(递归最大允许32层)
BEGIN
--递归查找当前节点的子节点
INSERT @t_Level SELECT * FROM f_Sort(@ID,@sort)
SET @sort=@sort+@@ROWCOUNT --排序号加上子节点个数
END
FETCH tb INTO @ID
END
RETURN
END
GO--显示结果
SELECT a.*
FROM tb a,f_Sort(DEFAULT,DEFAULT) b
WHERE a.ID=b.ID
ORDER BY b.sort
/*--结果
ID PID Name
------ --------- ----------
001 NULL 山东省
002 001 烟台市
004 002 招远市
003 001 青岛市
005 NULL 四会市
006 005 清远市
007 006 小分市
--*/
CREATE TABLE tb(ID char(3),PID char(3),Name nvarchar(10))
INSERT tb SELECT '001',NULL ,'山东省'
UNION ALL SELECT '002','001','烟台市'
UNION ALL SELECT '004','002','招远市'
UNION ALL SELECT '003','001','青岛市'
UNION ALL SELECT '005',NULL ,'四会市'
UNION ALL SELECT '006','005','清远市'
UNION ALL SELECT '007','006','小分市'
GO--广度搜索排序函数
CREATE FUNCTION f_Sort(@ID char(3)=NULL,@sort int=1)
RETURNS @t_Level TABLE(ID char(3),sort int)
AS
BEGIN
DECLARE tb CURSOR LOCAL
FOR
SELECT ID FROM tb
WHERE PID=@ID
OR(@ID IS NULL AND PID IS NULL)
OPEN TB
FETCH tb INTO @ID
WHILE @@FETCH_STATUS=0
BEGIN
INSERT @t_Level VALUES(@ID,@sort)
SET @sort=@sort+1
IF @@NESTLEVEL<32 --如果递归层数未超过32层(递归最大允许32层)
BEGIN
--递归查找当前节点的子节点
INSERT @t_Level SELECT * FROM f_Sort(@ID,@sort)
SET @sort=@sort+@@ROWCOUNT --排序号加上子节点个数
END
FETCH tb INTO @ID
END
RETURN
END
GO--显示结果
SELECT a.*
FROM tb a,f_Sort(DEFAULT,DEFAULT) b
WHERE a.ID=b.ID
ORDER BY b.sort
/*--结果
ID PID Name
------ --------- ----------
001 NULL 山东省
002 001 烟台市
004 002 招远市
003 001 青岛市
005 NULL 四会市
006 005 清远市
007 006 小分市
--*/
解决方案 »
- 求UPDATETEXT超长字符串的方法,开头的 标识符 太长。最大长度为 128
- 我的SQL数据库因停电引起出错,显示为“置疑”的灰色状态,不知道是否能恢复?
- SQL导出成access时,索引全部丢失,如何解决?
- 求SQL算法
- 求一SQL语句
- 我在我的程序里创建了一个临时表#t,然后我在sql server 2000查询分析器里"select * from #t"确得到提示“对象名 '#t' 无效。”why?
- 程序里这段代码怎么写(执行)?
- SQL SERVER7.0桌面版数据库服务器之间远程数据操作的问题。
- 【求助】CSV文件导入数据库的问题
- Anaconda打不开,显示这样的页面怎么处理?一直显示There is an anaconda alreadly running
- Sql2005群集中出现的问题
- 请教高手,在线等,数据库中,把列值(是不知道的)转化为另一个表的字段名该怎么做?
id areaCode field1 field2
1 1000 2.036 5000.00
2 1001 1.032 1000.00
3 1002 0.325 3000.00 表2:(该表是递归形式的)
id areaCode areaName belongArea areaGrade
1 1000 中国 1000 0
2 1001 湖南 1000 1
3 1002 长沙 1001 2 select * from 表1,表2 where 表1.areaCode in(select areaCode from 表2 start with areaCode='地区变量' connect by prior belongArea=areaCode); 假设“地区变量”=1002,表1中只有记录1和记录2,没有记录3。
我现在的要求是如果在表1中找不到areaCode=1002的,就继续向上级地区查,找areaCode=1001的,如果1001能够匹配到记录,则停止向上查找。如果1001在表1中也不存在,就再继续向上级地区查,找areaCode=1000的。 问题:现在有什么办法能够让它在匹配到记录的时候就停止向上查呢?如果让它继续向上查的话,会产生多条记录.-- 如果你的ORACLE DB是10G的话,把你表2中ID为1的BELONGAREA的1000改成NULL,试试看下面的语句:
SQL> SELECT * FROM T1; ID AREACODE FIELD1 FIELD2
---------- ---------- -------- ----------
1 1000 2.036 5000.00
2 1001 1.032 1000.00
3 1002 0.325 3000.00SQL> SELECT * FROM T2; ID AREACODE AREANAME BELONGAREA AREAGRADE
---------- ---------- ---------- ---------- ----------
1 1000 中国 0
2 1001 湖南 1000 1
3 1002 长沙 1001 2
5 1003 浙江 1000 1
6 1004 杭州 1003 2
7 1005 滨江 1004 36 rows selected-- AREACODE = 1002
SQL> SELECT *
2 FROM (SELECT T1.*, LEV
3 FROM (SELECT AREACODE, LEVEL LEV
4 FROM T2
5 START WITH AREACODE = '1002'
6 CONNECT BY NOCYCLE PRIOR BELONGAREA = AREACODE) TT,
7 T1
8 WHERE TT.AREACODE = T1.AREACODE
9 ORDER BY LEV) ZZ
10 WHERE ROWNUM = 1; ID AREACODE FIELD1 FIELD2 LEV
---------- ---------- -------- ---------- ----------
3 1002 0.325 3000.00 1-- AREACODE = 1003
SQL> SELECT *
2 FROM (SELECT T1.*, LEV
3 FROM (SELECT AREACODE, LEVEL LEV
4 FROM T2
5 START WITH AREACODE = '1003'
6 CONNECT BY NOCYCLE PRIOR BELONGAREA = AREACODE) TT,
7 T1
8 WHERE TT.AREACODE = T1.AREACODE
9 ORDER BY LEV) ZZ
10 WHERE ROWNUM = 1; ID AREACODE FIELD1 FIELD2 LEV
---------- ---------- -------- ---------- ----------
1 1000 2.036 5000.00 2
from (
select to_char(dept_cd) as id, to_char(upr_dept_cd) as paretnt_id, kor_nm as name, null as start_date,null as end_date,
null as proCd,null as proStart_date,null as proEnd_date,'dept' as type,null as total_file from dept_det
UNION
select to_char(emp_no) as id, to_char( dept_cd ) as paretnt_id ,kor_nm as name, null as start_date, null as end_date,
null as proCd, null as proStart_date, null as proEnd_date,'member' as type,null as total_file from emp_det
UNION
select id,paretnt_id,name,start_date,end_date,proCd,proStart_date,proEnd_date,type,total_file from
(select to_char(schedule_id) as id, to_char(b.prj_mem_id) as paretnt_id , schedule_nm as name, to_char(a.start_date,'YYYYMMDD') as start_date, to_char(a.end_date,'YYYYMMDD') as end_date,p.project_cd as proCd, to_char(p.start_date,'YYYYMMDD') as proStart_date, to_char(p.end_date,'YYYYMMDD') as proEnd_date,
'schedule' as type from schedule_det a ,member_det b, project_mstr p where a.charge_cd = b.PRJ_MEM_ID and a.PROJECT_ID=p.project_id ) m
left join
(select file_parent_id,file_type, (select count(*) from com_file where file_type = 0) as total_file from com_file where file_type=0 )c on c.file_parent_id=m.id
) a
start with a.id='JH005'
connect by prior a.id = a.paretnt_id
另外,本人韩企,非日企。其实中国软件外包中的60%业务都是对日外包,虽然你鄙视日企,你能保证你一生不做日本项目?不跟日企打交道?
from (
select to_char(dept_cd) as id, to_char(upr_dept_cd) as paretnt_id, kor_nm as name,null as teamCd, null as team_name , null as start_date,null as end_date,'dept' as type from dept_det
where dept_cd not in(select dept_cd from dept_det where kor_nm like #teamMark#)
UNION
select to_char( project_id) as id , to_char(dept_cd) as paretnt_id, project_nm as name, null as teamCd, null as team_name ,to_char(start_date,'YYYYMMDD') as start_date, to_char(end_date,'YYYYMMDD') as end_date, 'project' as type from project_mstr
UNION
select to_char(b.emp_no) as id, to_char(a.project_id) as paretnt_id, b.kor_nm as name, c.dept_cd as teamCd, c.kor_nm as team_name, null as start_date, null as end_date, 'member' as type
from member_det a,emp_det b, dept_det c
where a.prj_mem_id = b.emp_no and b.dept_cd = c.dept_cd
UNION
select to_char(schedule_id) as id, to_char(CHARGE_CD) as paretnt_id , schedule_nm as name, null as teamCd, null as team_name ,to_char(start_date,'YYYYMMDD') as start_date,
to_char(end_date,'YYYYMMDD') as end_date, 'schedule' as type from schedule_det
UNION
select to_char(flag_id) as id, to_char(project_id) as paretnt_id , flag_nm as name, null as teamCd, null as team_name ,to_char(flag_date,'YYYYMMDD') as start_date, null as end_date, 'flag' as type from flag_det
) a
start with a.id=#topId# connect by prior a.id = a.paretnt_id order siblings by team_name desc