100分求帮忙优化SQL,多谢!!!
初学SQL, 为了达到项目目的,写了以下SQL,用了4个游标嵌套,忘大哥们帮忙优化一下以提高性能,谢谢!
代码如下:
说明:用到的主表:ORGGroupDeptH,存储的是人事组织架构.
最后达到的目的是要这样的结构:
編號 第二層第三層 第四層 第五層 序號 級別 名稱
fn0101000000 系統資訊系
fn0101000001 1 董事 高級經理
fn0101000002 軟件開發部
fn0101000000 1 集團董事 經理
fn0101000000 2 集團董事 系統分析員
fn0101000000 3 集團董事 高級應用程序員
fn0101000000 4 集團董事 應用程序員
fn0101000000 網絡系統部
fn0101000000 1 集團董事 網絡系統部
谢谢大家!
Create table #OrgInfo
(
intNo smallint IDENTITY(1,1), nvrFunctionNo nvarchar(12),twiceLvl nvarchar(100),thirdLvl nvarchar(100),
forthLvl nvarchar(100),fifthLvl nvarchar(100),orderNo varchar(10),titleClass nvarchar(100),funcTitle nvarchar(100)
)
--drop table #OrgInfo
--select * from #OrgInfo
SET NOCOUNT ON
--定义变量
DECLARE @functionNo varchar(12),@twiceLvl varchar(100),@thirdLvl varchar(100),
@forthLvl varchar(100),@fifthLvl varchar(100),@orderNo int,@titleClass varchar(100),@funcTitle varchar(100)
--定义第二层游标
DECLARE twiceLvlCursor CURSOR
FOR SELECT nvrFunctionNo FROM ORGGroupDeptH WHERE intDeptLv=2 ORDER BY bitIsDept,nvrInterface,intOrderNo
OPEN twiceLvlCursor
FETCH NEXT FROM twiceLvlCursor
INTO @functionNo
WHILE @@FETCH_STATUS = 0
BEGIN
print '第二層functionNo '+@functionNo
--查询出相关值
SELECT @funcTitle=nvrNameC FROM ORGGroupDeptH where nvrFunctionNo=@functionNo
--插入第二层部门
INSERT INTO #OrgInfo(nvrFunctionNo,twiceLvl,thirdLvl,forthLvl,fifthLvl,orderNo,titleClass,funcTitle)
VALUES(@functionNo,@funcTitle, ' ', ' ', ' ', ' ', ' ', ' ')
--定义第三层游标值
DECLARE thirdLvlCursor CURSOR
FOR SELECT nvrFunctionNo FROM ORGGroupDeptH WHERE (intDeptLv=3 or intDeptLv IS NULL) AND nvrPFunctionNo=@functionNo ORDER BY bitIsDept,intOrderNo
OPEN thirdLvlCursor
FETCH NEXT FROM thirdLvlCursor INTO @functionNo
WHILE @@FETCH_STATUS = 0
BEGIN
--set @functionNo= 'fn0201000000 '
--如果是职位
print '第三層functionNo '+@functionNo
IF (SELECT bitIsDept FROM ORGGroupDeptH WHERE (intDeptLv=3 or intDeptLv is null) AND nvrFunctionNo=@functionNo)=0
BEGIN
SELECT @funcTitle=h.nvrNameC,@orderNo=h.intOrderNo,@titleClass=t.nvrNameC FROM ORGGroupDeptH h
INNER JOIN TitleClass t ON t.nvrGenCode=h.nvrTitleClass
WHERE h.nvrFunctionNo=@functionNo
--插入第三层职位
INSERT INTO #OrgInfo(nvrFunctionNo,twiceLvl,thirdLvl,forthLvl,fifthLvl,orderNo,titleClass,funcTitle)
VALUES(@functionNo, ' ', ' ', ' ', ' ',@orderNo,@titleClass,@funcTitle)
END
--如果是部门
ELSE
BEGIN
SELECT @funcTitle=nvrNameC FROM ORGGroupDeptH where nvrFunctionNo=@functionNo
INSERT INTO #OrgInfo(nvrFunctionNo,twiceLvl,thirdLvl,forthLvl,fifthLvl,orderNo,titleClass,funcTitle)
VALUES(@functionNo, ' ',@funcTitle, ' ', ' ', ' ', ' ', ' ')
--************************
print '第三層部門 ' + @functionNo
--定义第四层游标值
DECLARE forthLvlCursor CURSOR
FOR SELECT nvrFunctionNo FROM ORGGroupDeptH WHERE (intDeptLv=4 or intDeptLv IS NULL) AND nvrPFunctionNo=@functionNo ORDER BY bitIsDept,intOrderNo
OPEN forthLvlCursor
FETCH NEXT FROM forthLvlCursor INTO @functionNo
WHILE @@FETCH_STATUS = 0
BEGIN
--set @functionNo= 'fn0201000000 '
--如果是职位
print '第四层functionNo '+@functionNo
IF (SELECT bitIsDept FROM ORGGroupDeptH WHERE (intDeptLv=4 or intDeptLv is null) AND nvrFunctionNo=@functionNo)=0
BEGIN
SELECT @funcTitle=h.nvrNameC,@orderNo=h.intOrderNo,@titleClass=t.nvrNameC FROM ORGGroupDeptH h
INNER JOIN TitleClass t ON t.nvrGenCode=h.nvrTitleClass
WHERE h.nvrFunctionNo=@functionNo
--插入第四层职位
INSERT INTO #OrgInfo(nvrFunctionNo,twiceLvl,thirdLvl,forthLvl,fifthLvl,orderNo,titleClass,funcTitle)
VALUES(@functionNo, ' ', ' ', ' ', ' ',@orderNo,@titleClass,@funcTitle)
END
--如果是部门
ELSE
BEGIN
SELECT @funcTitle=nvrNameC FROM ORGGroupDeptH where nvrFunctionNo=@functionNo
INSERT INTO #OrgInfo(nvrFunctionNo,twiceLvl,thirdLvl,forthLvl,fifthLvl,orderNo,titleClass,funcTitle)
VALUES(@functionNo, ' ', ' ',@funcTitle, ' ', ' ', ' ', ' ')
--************************
--定义第五層游标值
DECLARE fifthLvlCursor CURSOR
FOR SELECT nvrFunctionNo FROM ORGGroupDeptH WHERE (intDeptLv=5 or intDeptLv IS NULL) AND nvrPFunctionNo=@functionNo ORDER BY bitIsDept,intOrderNo
OPEN fifthLvlCursor
FETCH NEXT FROM fifthLvlCursor INTO @functionNo
WHILE @@FETCH_STATUS = 0
BEGIN
--set @functionNo= 'fn0201000000 '
--如果是职位
print '第五層 '+@functionNo
IF (SELECT bitIsDept FROM ORGGroupDeptH WHERE (intDeptLv=5 or intDeptLv is null) AND nvrFunctionNo=@functionNo)=0
BEGIN
SELECT @funcTitle=h.nvrNameC,@orderNo=h.intOrderNo,@titleClass=t.nvrNameC FROM ORGGroupDeptH h
INNER JOIN TitleClass t ON t.nvrGenCode=h.nvrTitleClass
WHERE h.nvrFunctionNo=@functionNo
--插入第五层职位
INSERT INTO #OrgInfo(nvrFunctionNo,twiceLvl,thirdLvl,forthLvl,fifthLvl,orderNo,titleClass,funcTitle)
VALUES(@functionNo, ' ', ' ', ' ', ' ',@orderNo,@titleClass,@funcTitle)
END
--如果是部门
ELSE
BEGIN
SELECT @funcTitle=nvrNameC FROM ORGGroupDeptH where nvrFunctionNo=@functionNo
INSERT INTO #OrgInfo(nvrFunctionNo,twiceLvl,thirdLvl,forthLvl,fifthLvl,orderNo,titleClass,funcTitle)
VALUES(@functionNo, ' ', ' ', ' ',@funcTitle, ' ', ' ', ' ')
END
FETCH NEXT FROM fifthLvlCursor INTO @functionNo
END
CLOSE fifthLvlCursor
DEALLOCATE fifthLvlCursor
--************************
END
FETCH NEXT FROM forthLvlCursor INTO @functionNo
END
CLOSE forthLvlCursor
DEALLOCATE forthLvlCursor
--*********************
END
FETCH NEXT FROM thirdLvlCursor INTO @functionNo
END
CLOSE thirdLvlCursor
DEALLOCATE thirdLvlCursor
FETCH NEXT FROM twiceLvlCursor INTO @functionNo
END
CLOSE twiceLvlCursor
DEALLOCATE twiceLvlCursor
select * from #orginfo
drop table #OrgInfo
[解决办法]
你的原數據是怎樣的？
[解决办法]
hehe ...
lz，你没了解鱼兄跟你说的，你要把你基础数据贴出来一些，别人才好给你看问题啊。
另外你写的存储过程太长了，一般都会自己重写一个的。
[解决办法]
俺来学习顺便接点分！
[解决办法]
都是高手
[解决办法]
可以把#OrgInfo表做成 实际表而不是临时表
使用临时表速度太慢（也许是没有主键索引的关系）
