Server实用特出例句之一,树形表非循环递归查询

很多人可能想要查询整个树形表关联的内容都会通过循环递归来查...事实上在微软在SQL2005或以上版本就能用别的语法进行查询,下面是示例。

图片 1图片 2代码

在数据查询中,从2008开始SQL Server提供了一个新的数据类型hierarchyid,专门用来操作层次型数据结构。

 

SQLServer 中的回车符与换行符
制表符:CHAR(9)  换行符:CHAR(10)  回车符:CHAR(13)
1、将 varchar 值作为条件,查询Where Id in 列表的值 
declare @a varchar ( 100 )
set @a = '56,58'   
-- 正确的: 
select * from tb where charindex ( ','   ltrim (id)   ',' , ','   @a   ',' ) > 0 
-- 错误的: 
select * from tb where id in   @a 

hierarchyid  类型对层次结构树中有关单个节点的信息进行逻辑编码的方法是:对从树的根目录到该节点的路径进行编码。 

--通过子节点查询父节点
WITH 
TREE AS( 
    SELECT * FROM Areas 
    WHERE id = 6  -- 要查询的子 id 
    UNION ALL 
    SELECT Areas.* FROM Areas, TREE 
    WHERE TREE.PId = Areas.Id

SELECT Area FROM TREE

2、找出与某id相近的四条记录
Create table tb(id int ,cName char ( 10 ))
DECLARE @ID INT         
SET @ID = 7 
SELECT * FROM TB A WHERE id in 
( SELECT TOP 4 id FROM TB ORDER BY ABS (id - @id ))
ORDER BY ID 

这种路径在逻辑上表示为一个在根之后被访问的所有子级的节点标签序列。 表示形式以一条斜杠开头,只访问根的路径由单条斜杠表示。 对于根以下的各级,各标签编码为由点分隔的整数序列。 子级之间的比较就是按字典顺序比较由点分隔的整数序列。 每个级别后面紧跟着一个斜杠。 因此斜杠将父级与其子级分隔开。 例如,以下是长度分别为 1 级、2 级、2 级、3 级和 3 级的有效 hierarchyid 路径:

 

3、按名称,规格分组,将单价数据合并成一行,并计算数量
Create table [tb] (tName varchar(4), [tSize] varchar (7), [ tPrice ] int , [ tQty ] int )
insert [ tb ] 
select '高瓦' , '880*110'  , 22 , 1 union all select '高瓦' , '880*110' , 25 , 1 union all 
select '高瓦' , '880*110' , 22 , 1 union all select '高瓦' , '880*120' , 22 , 1 

• / 

--通过父节点查询子节点
WITH 
TREE AS( 
    SELECT * FROM Areas 
    WHERE id = 7  -- 要查询的子 id 
    UNION ALL 
    SELECT Areas.* FROM Areas, TREE 
    WHERE TREE.Id = Areas.PId

SELECT Area FROM TREE

select   tName, tSize,
  tPrice = stuff (( select ','   ltrim (tPrice) from tb  
     where tName = t.tName and tSize = t.tSize FOR XML PATH('')),1,1,''), sum(tQty) as tQty
from   tb t group by   tName, tSize 
---结果----------------------------- 
tName   tSize     tPrice     tQty
高瓦    880*110    22,25,22  3
高瓦    880*120    22        1 

• /1/ 

 

4、根据出生日期,计算出准确的年龄 
SET @A = '2008-08-12' 
DECLARE @A DATETIME 
SELECT 年龄 = 
case when datediff(day,dateadd(year,datediff(year,@A,getdate()),@A), getdate())>= 0 
      then datediff(year,@A,getdate()) else datediff(YY,@A,getdate())- 1 end 

• /0.3.-7/ 

 通过子节点查询父节点查询结果为:

5、找出某目录列表中所有下级目录,包括自己

• /1/3/ 

图片 3

图片 4图片 5代码

• /0.1/0.2/

修改代码为

DECLARE @FolderList varchar ( 800 )
SET @FolderList = '1' 
SET NOCOUNT ON 
    CREATE TABLE # Temp (FolderId int )    
    INSERT # Temp 
    SELECT FolderId FROM Doc_Folder
    WHERE CHARINDEX ( ','   LTRIM (FolderId)   ',' , ','   @FolderList   ',' ) > 0     
    WHILE @@Rowcount > 0 
    BEGIN 
        INSERT # Temp SELECT FolderId FROM Doc_Folder AS A WHERE 
            EXISTS ( SELECT 1 FROM # Temp AS B WHERE B. [ FolderId ] = A.ParentFolderId)
            AND NOT EXISTS(SELECT 1 FROM # Temp AS B WHERE B. [FolderId] = A.[FolderId])
    END 

在没有hierarchyid的日子里,我们通过CTE的方式来查询父以及全部的下级,但是,数据量多的情况下,CTE的方式将会变的很慢,后来,我们通过构造PATH的方式来加快速度。那么,有了hierarchyid类型后,自然得使用hierarchyid了。

 

 

现在,通过一个实际的例子来看看hierarchyid的威力。

--通过子节点查询父节点
declare @area varchar(8000);
WITH 
TREE AS( 
    SELECT * FROM Areas 
    WHERE id = 6  -- 要查询的子 id 
    UNION ALL 
    SELECT Areas.* FROM Areas, TREE 
    WHERE TREE.PId = Areas.Id
)
select @area=isnull(@area,'') Area from Tree order by id 
select Area= @area
则结果为:中国北京市丰台区

6、简单静态游标

 

 

图片 6图片 7代码

一:CTE方式

根据以上可以将这段代码封装为一个存储过程

DECLARE product_cursor CURSOR STATIC FOR 
SELECT cName FROM Product 
OPEN product_cursor
FETCH NEXT FROM product_cursor INTO @product
WHILE @@FETCH_STATUS = 0
BEGIN
 SELECT @message = ' '   @product
 PRINT @message
 FETCH NEXT FROM product_cursor INTO @product
END
CLOSE product_cursor
DEALLOCATE product_cursor

WITH CTEGetChild AS 

    SELECT * FROM EL_Organization.Organization WHERE ID='ecc43c7159924dca91e2916368f923f4' --and [State]=0 and AuditState=2
    UNION ALL 
     (
        SELECT A.* FROM EL_Organization.Organization AS A
        INNER JOIN CTEGetChild AS B ON a.PARENTID=B.ID  --and A.[State]=0 and A.AuditState=2
     ) 
)

 

 

查询出来4489行,需要25S。

-----存储过程,递归获取树形地区表字符串
if exists (select * from sysobjects where name='SP_GetAreaStr')
drop proc SP_GetAreaStr
go
create procedure SP_GetAreaStr 
 @id int
as
 declare @area varchar(8000)
begin
 WITH 
TREE AS( 
    SELECT * FROM Areas 
    WHERE id = @id  -- 要查询的子 id 
    UNION ALL 
    SELECT Areas.* FROM Areas, TREE 
    WHERE TREE.PId = Areas.Id
)
select @area=isnull(@area,'') Area from Tree order by id 
select Area= @area
end 
go
--exec sp_helptext 'SP_GetAreaStr'
--go
exec SP_GetAreaStr 28
go

7、要求是取得每个ID对应postId的前三条

看来CTE方式已经到了不能容忍的地步,那么,现在,我们就用它来进行优化。

 

图片 8图片 9代码

 

查询结果:中国安徽省宿州市灵璧县

CREATE TABLE [tb] (Id INT ,postId INT )
INSERT INTO [tb] 
SELECT 2788 , 45753530 UNION ALL 
SELECT 6417 , 46862065 UNION ALL 
SELECT 61773 , 47407456 UNION ALL
SELECT 61773 , 47436468 UNION ALL
SELECT 61773 , 47448259 UNION ALL 
SELECT 61773 , 47474393 UNION ALL 
SELECT 83604 , 41671947 UNION ALL 
SELECT 83604 , 45858681 UNION ALL
select id, postid from ( select * ,cid = row_number() over (partition by id order by id) from tb ) as t
where t.cid <= 3

二:hierarchyid

 

 

首先,我们得新建该字段,然后为其赋值,

所用表结构:

8、实现编号自动增长

create function f_cidname(@id varchar(50)) returns varchar(max) as
begin
declare @pids nvarchar(max);
declare @pNames nvarchar(max);
set @pids='';
set @pNames='';
with cte as
( select id,parentid,name from EL_Organization.Organization where id =@id--'00037fdf184e48d084b87c3499e3c0e5'
union all
select b.id,b.parentid,b.name from cte A ,EL_Organization.Organization B where a.parentid = b.id
)

select @pids=convert(varchar(32),Convert(int, Convert(varbinary(max), id)))  '/' @pids from cte
return '/' @pids

end
go

图片 10

图片 11图片 12代码

接着,我们需要Update全表:

部分数据:

--下面的代码生成长度为8的编号,编号以BH开头,其余6位为流水号。
-- 得到新编号的函数 
CREATE FUNCTION f_NextBH()
RETURNS char ( 8 )
AS 
BEGIN 
RETURN (SELECT 'BH'  RIGHT(1000001   ISNULL(RIGHT(MAX(BH),6),0),6) FROM tb WITH(XLOCK,PAGLOCK))
END 
GO 
-- 在表中应用函数 
CREATE TABLE tb(
BH char (8) PRIMARY KEY DEFAULT dbo.f_NextBH(),col int)
-- 插入资料 
BEGIN TRAN 
INSERT tb(col) VALUES ( 1 )
INSERT tb(col) VALUES ( 2 )
INSERT tb(col) VALUES ( 4 )
INSERT tb(BH,col) VALUES (dbo.f_NextBH(), 14 )
COMMIT TRAN 

UPDATE EL_Organization.Organization SET PIDS=dbo.f_cidname(id)

图片 13

9、表Age_AssignedNum中的字段CallRecordId,其值有三种:K、N、null,
   按PlanId分组统计出这三种值各有多少行

注意,id是guid的32位字符串,而hierarchyid字段不支持那么大的Path内路径,于是我们将GUID转为了整型:convert(varchar(32),Convert(int, Convert(varbinary(max), id))) 

图片 14图片 15代码

2.1 TIP

SELECT PlanId,sK=count(CASE CallRecordId WHEN 'K' then CallRecordId end),
    sN=count(CASE CallRecordId WHEN 'N' then CallRecordId end),
    sNull=count( CASE isnull(CallRecordId,'') WHEN '' then '1' end)
FROM Age_AssignedNum GROUP BY PlanId 

Exception message: DataReader.GetFieldType(4) returned null. Exception data: System.Collections.ListDictionaryInternal

 

注意,极有可能我们把字段更新上去后,我们的程序却出错了,如上。这个时候,我们需要把

 

C:Program FilesMicrosoft SQL Server100SDKAssembliesMicrosoft.SqlServer.Types.dll

这个DLL打包到我们的应用程序中去。原因不解释了。

看看效果吧,修改过后的代码为:

DECLARE @tmpIds hierarchyid
SELECT @tmpIds=Pids FROM EL_Organization.Organization WHERE ID='ecc43c7159924dca91e2916368f923f4';
WITH CTEGetChild AS  (
    SELECT * FROM EL_Organization.Organization WHERE ID='ecc43c7159924dca91e2916368f923f4'
    UNION ALL(
    SELECT * FROM EL_Organization.Organization WHERE Pids.IsDescendantOf(@tmpIds)=1
    )
)
SELECT * FROM CTEGetChild

现在,我们的时间到了1S内。

2.2 一切为了不动应用层代码

现在,既然,增加了一个字段,我们就要维护这个字段,如:本条记录在应用程序中被移动到了别的父级下,就需要更新这个字段。为了不动上层代码,唯一能做的就是创建触发器,即:原有的ParentId变动的时候,就需要更新这个PIds字段,于是,我们创建触发器如下:

create trigger UpdateOrgPIds
on EL_Organization.Organization
after update
as
if update ([ParentId])
begin
     declare @tmpId varchar(36)
     select @tmpId=id from inserted
     update EL_Organization.Organization set pids=dbo.f_cidname(@tmpId)
end
go
-- drop  trigger EL_Organization.UpdateOrgPIds

图片 16微信扫一扫,关注最课程,获取每日一练

本文由星彩网app下载发布于星彩彩票app下载,转载请注明出处:Server实用特出例句之一,树形表非循环递归查询

TAG标签:
Ctrl+D 将本页面保存为书签,全面了解最新资讯,方便快捷。