SQL Server 2005中的CTE递归查询得到一棵树

版权声明:本文为博主原创文章,遵循 CC 4.0 by-sa 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/bin_520_yan/article/details/5998349

       感觉这个CTE递归查询蛮好用的,先举个例子:

 

上面的SQL语句再次插入一条数据:

insert Tree values('网络1班','网络工程')

     运行结果如下图:

图1 运行结果

     注意点:貌似在递归成员处所选择的字段都必须Tree表的数据,而不能是CTE结果集中的除了Tree中没有而CTE中有的字段在这里才可以引用,比如字段TE。

    首先看下,遍历的第1条记录的SQL语句:

   获取的结果为:

   Name  Parent   TE    Levle

-------------------------------------

     大学    NULL   大学     0

   递归第2次所获取的结果集合的类SQL语句为:

   上面的CTE子查询的结果就是第一次递归查询的结果集,上面SQL运行结果为:

   同样的,将第二次递归查询的上面三条记录作为第三次查询的‘定位成员’:

  【这里要注意,上面的三条记录是从最后一条开始依次作为第三次递归的输入的,即第一条是ID=9的记录,接下来是7和2,关于第四次递归也类似】

   第三次递归类SQL语句

   结果如下:

其实每次递归的类SQL可为如下所示:

 第四次递归一次类推,最后所查询的结果为上面所有递归的union。

 续:在上面的SQ语句查询结果中,ID为10的记录应该要放在ID为4的后面。

 往数据表中再次添加两条记录:

insert Tree values('计科','计算机学院')
insert Tree values('我','网络1班') 

再次修改上面的SQL语句:

最后的结果为:

图2 运行结果

这样,无论用户插入多少条记录都可以进行按部门,按规律进行查询。

展开阅读全文

sql 2005 递归查询的奇怪问题

07-24

http://www.microsoft.com/china/msdn/library/data/sqlserver/05TSQLEnhance.mspx?mfr=truern给出了使用 CTE 处理材料清单层次结构rnrnCREATE TABLE Itemsrn(rn itemid VARCHAR(5) NOT NULL PRIMARY KEY,rn itemname VARCHAR(25) NOT NULL,rn /* other columns, e.g., unit_price, measurement_unit */rn)rnCREATE TABLE BOMrn(rn itemid VARCHAR(5) NOT NULL REFERENCES Items,rn containsid VARCHAR(5) NOT NULL REFERENCES Items,rn qty INT NOT NULLrn /* other columns, e.g., quantity */rn PRIMARY KEY(itemid, containsid),rnrn CHECK (itemid <> containsid)rn)rnSET NOCOUNT ONrnINSERT INTO Items(itemid, itemname) VALUES('A', 'Item A')rnINSERT INTO Items(itemid, itemname) VALUES('B', 'Item B')rnINSERT INTO Items(itemid, itemname) VALUES('C', 'Item C')rnINSERT INTO Items(itemid, itemname) VALUES('D', 'Item D')rnINSERT INTO Items(itemid, itemname) VALUES('E', 'Item E')rnINSERT INTO Items(itemid, itemname) VALUES('F', 'Item F')rnINSERT INTO Items(itemid, itemname) VALUES('G', 'Item G')rnINSERT INTO Items(itemid, itemname) VALUES('H', 'Item H')rnINSERT INTO Items(itemid, itemname) VALUES('I', 'Item I')rnINSERT INTO Items(itemid, itemname) VALUES('J', 'Item J')rnINSERT INTO Items(itemid, itemname) VALUES('K', 'Item K')rnINSERT INTO BOM(itemid, containsid, qty) VALUES('E', 'J', 1)rnINSERT INTO BOM(itemid, containsid, qty) VALUES('C', 'E', 3)rnINSERT INTO BOM(itemid, containsid, qty) VALUES('A', 'C', 2)rnINSERT INTO BOM(itemid, containsid, qty) VALUES('H', 'C', 4)rnINSERT INTO BOM(itemid, containsid, qty) VALUES('C', 'B', 2)rnINSERT INTO BOM(itemid, containsid, qty) VALUES('B', 'F', 1)rnINSERT INTO BOM(itemid, containsid, qty) VALUES('B', 'G', 3)rnINSERT INTO BOM(itemid, containsid, qty) VALUES('A', 'B', 2)rnINSERT INTO BOM(itemid, containsid, qty) VALUES('A', 'D', 2)rnINSERT INTO BOM(itemid, containsid, qty) VALUES('H', 'I', 1)rnrn以下 CTE 计算数量的累积乘积:rnrnWITH BOMCTE(itemid, containsid, qty, cumulativeqty)rnASrn(rn SELECT *, qtyrn FROM BOMrn WHERE itemid = 'A'rn UNION ALLrn SELECT BOM.*, BOM.qty * BOMCTE.cumulativeqtyrn FROM BOMrn JOIN BOMCTErn ON BOM.itemid = BOMCTE.containsidrn)rnSELECT * FROM BOMCTErnrn完全没有问题,但我将 qty 改为numeric(18, 4),就出现“Types don't match between the anchor and the recursive part in column "cumulativeqty" of recursive query "BOMCTE".”错误。如何解决? 论坛

代码中使用SQL Server CTE超时

01-16

rnWITH Invent ASrn(rn SELECT i.ItemId, d.ConfigIdrn , CAST(SUM(t.Qty) AS FLOAT) Qtyrn FROM AX4.dbo.InventTable irn INNER JOIN AX4.dbo.InventTrans trn ON t.ItemId = i.ItemIdrn AND t.DataAreaId = i.DataAreaIdrn LEFT JOIN AX4.dbo.InventDim drn ON d.InventDimId = t.InventDimIdrn AND d.DataAreaId = t.DataAreaIdrn WHERE i.DimGroupId <> N'SER'rn AND t.DatePhysical > N'1900-01-01'rn AND t.DatePhysical <= @Datern AND t.DataAreaId = @Companyrn GROUP BY i.ItemId, d.ConfigIdrn), ShipIssue ASrn(rn SELECT t.ItemId, t.ConfigIdrn , CAST(CAST(SUM(t.Qty) AS NUMERIC(20, 2)) AS FLOAT) Qtyrn FROMrn (rn SELECT t.DataAreaId, t.ItemId, d.ConfigId, t.DatePhysical TransDatern , CAST(CAST(t.Qty * -1 AS NUMERIC(20, 2)) AS FLOAT) Qtyrn FROM AX4.dbo.InventTrans trn INNER JOIN AX4.dbo.InventDim drn ON d.InventDimId = t.InventDimIdrn AND d.DataAreaId = t.DataAreaIdrn WHERE ((t.TransType = 0 AND StatusIssue = 1)rn OR (t.TransType = 8 AND StatusIssue = 2))rn ) trn INNER JOIN AX4.dbo.InventTable irn ON i.ItemId = t.ItemIdrn AND i.DataAreaId = t.DataAreaIdrn WHERE i.DimGroupId <> N'SER'rn AND t.TransDate >= DATEADD(DAY, 1, DATEADD(YEAR, -1, @Date))rn AND t.TransDate < DATEADD(DAY, 1, @Date)rn AND t.DataAreaId = @Companyrn GROUP BY t.ItemId, t.ConfigIdrn HAVING SUM(t.Qty) <> 0rn), TurnOver ASrn(rn SELECT i.ItemId, i.ConfigIdrn , i.Qty InventQtyrn , s.Qty ShipIssueQtyrn , CASErn WHEN ISNULL(s.Qty, 0) = 0 THEN NULLrn ELSE CAST(i.Qty / s.Qty AS NUMERIC(10, 4))rn END ITOSrn FROM Invent irn LEFT JOIN ShipIssue srn ON s.ItemId = i.ItemIdrn AND s.ConfigId = i.ConfigIdrn), TurnOverLevel ASrn(rn SELECT *,rn CASErn WHEN InventQty < 0 OR ShipIssueQty < 0 THEN N'Others'rn WHEN ITOS > 5 OR (ShipIssueQty IS NULL AND InventQty > 0) THEN N'>5'rn WHEN ITOS <= 5 AND ITOS > 2 THEN N'>2 - 5'rn WHEN ITOS <= 2 AND ITOS > 0.5 THEN N'>0.5 - 2'rn WHEN ITOS <= 0.5 AND ITOS > 0.2 THEN N'>0.2 - 0.5'rn WHEN ITOS <= 0.2 AND ITOS > 0.05 THEN N'>0.05 - 0.2'rn WHEN ITOS <= 0.05 AND ITOS > 0 THEN N'>0 - 0.05'rn WHEN ITOS = 0 OR InventQty = 0 THEN N'0'rn END [Level]rn FROM TurnOverrn), TurnOverCost ASrn(rn SELECT t.*rn , CASErn WHEN i.ItemDimCostPrice = 1 THEN CAST(c.CostPrice + c.Markup / CASE WHEN c.PriceQty = 0 THEN 1 ELSE c.PriceQty END AS MONEY)rn ELSE CAST(m.Price AS MONEY)rn END CostPricern , i.ItemName, e.ElementNameENG ItemType, i.ItemGroupId ItemGroup, f.ItemStatusrn FROM TurnOverLevel trn INNER JOIN AX4.dbo.InventTable irn ON i.ItemId = t.ItemIdrn AND i.DataAreaId = @Companyrn INNER JOIN dbo.AxEnum ern ON e.EnumName = N'ItemType'rn AND e.ElementValue = i.ItemTypern LEFT JOIN AX4.dbo.ConfigTable frn ON f.ItemId = t.ItemIdrn AND f.ConfigId = t.ConfigIdrn AND f.DataAreaId = i.DataAreaIdrn LEFT JOIN AX4.dbo.InventDimCombination crn ON c.ItemId = i.ItemIdrn AND c.ConfigId = t.ConfigIdrn AND c.DataAreaId = i.DataAreaIdrn AND i.ItemDimCostPrice = 1rn LEFT JOIN AX4.dbo.InventTableModule mrn ON m.ItemId = t.ItemIdrn AND m.DataAreaId = @Companyrn AND i.ItemDimCostPrice = 0rn AND m.ModuleType = 0 --库存rn)rnSELECT ItemId, ItemName, ConfigId, ItemType, ItemGroup, ItemStatusrn , CostPrice, InventQtyrn , CAST(InventQty * CostPrice AS MONEY) InventValuern , ShipIssueQtyrn , CAST(ShipIssueQty * CostPrice AS MONEY) ShipIssueValuern , ITOS, [Level]rnFROM TurnOverCostrnrn出现超时错误, 而在分析器中只需要1-2秒中就出结果了 论坛

SQL Server 2005中的CLR

06-05

CLR集成概述rnrnSQL Server现有编程模型rn- Transact SQL(T-SQL)rn- 扩展存储过程(XP)rnrnCLR集成模型rn- SQL Server 2005新增的编程模型rn- 用任何托管代码编写存储过程、触发器和函数rn- 可创建自定义的数据类型和聚合函数rnrn注册和执行数据库中托管代码rn- 托管代码编写staticshared)的类方法,生成程序集rn- 程序集上载到SQL Server 2005,用Create Assembly数据定义语言(DDL)将其存储到系统目录(部署)rn- 创建T-SQL对象,并将其绑定到已经上载的程序集的入口点,用Create Procedure/Function/Trigger/Type/Aggregatern- 使用,应用程序可以象T-SQL例程一样的调用rnrn构建和部署rn- VS.NET 2005提供的“SQL Server项目”的代码模板rn- 部署过程自动创建程序集中定义的例程、类型和聚合rnrn调试rn- 不受客户端到服务器连接类型的影响rn- 跨语言无缝集成,例如从T-SQL过程进入CLR过程rnrn演示代码rnrnSQL Queryrnrnuse SQLDemornrnEXEC sp_configure 'show advanced options','1';rngornrnreconfigure;rngornrnEXEC sp_configure 'clr enabled','1'rngornrnselect dbo.ClrSplitter('Hello;world',1)rnrnCS Codernrnusing System;rnusing System.Data;rnusing System.Data.Sql;rnusing System.Data.SqlTypes;rnusing System.Datarnusing Microsoft.SqlServer.Server;rnrnpublic partial class UserDefinedFunctionsrnrn [Microsoft.SqlServer.Server.SqlFunction]rn public static SqlString Function1()rn rn return new SqlString("Hello");rn rnrn [SqlFunction]rn public static string ClrSplitter(string list, int element)rn rn return list.Split(';')[element];rn rn;rnrnCLR与T-SQL:编程模型rnrnT-SQLrn- 过程代码内部嵌入查询语言rnrnCLRrn- in-proc ADO.NET数据访问(System.Data.SqlServer)rn- 产生冗长代码rn- 容易在个层之间移动代码和利用现有技术rnrnCLR与T-SQL:性能rnrnT-SQLrn- 数据访问方面性能好rn- 适合编写数据访问密集的代码rnrnCLRrn- 过程代码,计算rn- 通用规则,用CLR编写计算和逻辑密集代码rnrnCLR与T-SQL:语句的提交、返回rnrnT-SQLrn- 过程代码SQL语句往返,T-SQL具有优势rn- select查询语句,返回客户端rnrnCLRrn- 额外的代码层,造成性能降低rn- SqlPipe对象将结果发送到客户端rnrnCLR与T-SQL:导航rnrnT-SQLrn- 通过只进、只读光标实现rn- 可更新光标当前位置行rn- 适合与,执行一系列语句(insert/update/delete/select),带有几个或不带返回到客户端的行,并且不导航产生的行rnrnCLRrn- SqlDataAdapterReader实现rn- 适合每行都有复杂的处理rnrnCLR与扩展存储过程(XP)rnrn- 粒度控制(权限SAFE/EXTERNAL_ACCESS/UNSAFE)rn- 可靠性rn- 数据访问rn- 性能rn- 可伸缩性rnrn代码位置:数据库与中间层rnrn数据验证rn- 封装数据与逻辑rn- 避免不同层中重复验证逻辑rnrn减少网络流量rn- 需要处理大量数据而产生很少结果集的任务rn- 例如,数据分析中的需求预测、数值分析等rnrn处理常见数据库变成任务和问题rnrn- 使用Framework库进行数据验证rn- 产生结果集rn- SqlPipern- 表值函数rn- 可组合性rn- 数据源rn- 副作用操作rn- 强类型化和返回的结果集的数量rnrn用户自定义类型(UDT)rnrn何时创建UDTrn- 需要定义自己的标量类型rn- 对已有类型(Framework)进行封装,如自定义日期/时间、货币rn- 简单类型rnrn何时不创建UDTrn- 不要使用UDT对所有业务建模rn- UDT不适合数据建模抽象,的确需要的项目,请设计中间层的O/R影射rnrn小结rnrn- SQL Server 2005通过集成CLR,支持任何托管代码编写存储过程、触发器和函数等例程rn- 扩展数据库系统中的类型系统和聚合函数rnrn相关白皮书rnrn- SQL Server 2005中使用CLR白皮书rn http://www.microsoft.com/china/msdn/library/data/sqlserver/sqlclrguidance.mspx?pf=truernrn 论坛

没有更多推荐了,返回首页