【SQL SERVER小品】内在联系与外在表现 -- 解答对递归查询的疑问

todaynew

　　几个月前写了一个关于SQL Server递归查询的帖子，前两日版友13601812106_01同志对这个查询胡琢磨了一番，希望能加一个字段，以得到一种树型结构顺序的排序。其要求如下图所示：



　　要得到这样的排序并不困难，可以在递归代入子件ID，并形成一个num字段，然后用这个字段进行排序即可。代码可以写为：

WITH 单据递归查询 (bomID,产品id,子件ID,单耗,损耗率,根产品,level,num) AS  
(     
         SELECT bomID,产品id,子件ID,单耗,损耗率,产品id as 根产品,0 as level,子件ID as num
         FROM bom表
         where 产品ID not in (SELECT distinct 子件id FROM bom表)
         UNION ALL
         SELECT a.bomID,a.产品id,a.子件ID,a.单耗,a.损耗率,b.根产品,b.level+1 as level, b.num + '_' +a.子件ID as num
         FROM bom表 AS a,单据递归查询 AS b
         WHERE a.产品ID=b.子件ID)

SELECT * FROM 单据递归查询 ORDER BY 根产品,num;

　　不过这个查询会跳出“定位点类型和递归部分的类型不匹配”错误。解决这个错误的方法是对字段进行强制类型转换，可以修改为：

WITH 单据递归查询 (bomID,产品id,子件ID,单耗,损耗率,根产品,level,num) AS  
(     
         SELECT bomID,产品id,子件ID,单耗,损耗率,产品id as 根产品,0 as level,cast(子件ID as varchar) as num
         FROM bom表
         where 产品ID not in (SELECT distinct 子件id FROM bom表)
         UNION ALL
         SELECT a.bomID,a.产品id,a.子件ID,a.单耗,a.损耗率,b.根产品,b.level+1 as level, cast(cast(b.num as varchar)+'_'+CAST(a.子件ID as varchar) as varchar) as num
         FROM bom表 AS a,单据递归查询 AS b
         WHERE a.产品ID=b.子件ID)

SELECT * FROM 单据递归查询 ORDER BY 根产品,num;

　　这个查询对于13601812106_01同志给定的特定数据，是可以得到所需要的结果的，问题在于这并没什么实际意义。为什么这样说呢？原因是13601812106_01同志给定的是极为特殊的一种情况，这种情况是根产品的子产品从第2层级开始必须每层级只能包含一个下一层级元素。只有这样一种情况数据之间的关系才能看得比较清晰和舒服。如果13601812106_01同志的BOM表数据都符合这种条件，那么这个递归查询有意义，反之则没什么实际意义。也就是不能以偏概全，不能将特殊当一般。

　　深入挖掘13601812106_01同志的错误思想根源，可以发现13601812106_01同志将数据的内在联系与外在表现混为一谈了。BOM数据是一种具有层次结构的数据，这个特性是数据间的内在联系。BOM数据可以用不同的格式进行存储和表现，只要能保持其数据特定的内在联系即可。二维的数据表可以用于存储BOM数据，XML也可以存储BOM数据。二维的数据表便于计算但对层次结构的视觉表现力并不强，XML则相反。正是由于这个原因，二维数据表通常是需要借助树形结构视图控件来呈现层次结构数据的。而想直接用二维数据表来直接呈现层次结构的努力，通常是徒劳的。

　　递归查询的结果依然是二维的数据表，它只能更好的归集数据，但还是无法在视觉上很清晰地表现出数据间的层次结构。所以，递归查询是用来归集的，不是用来表现的。递归查询归集数据的目的只是将原来由客户端所需要做的运算放到服务器端来完成，比如你需要知道某件产品需要多少种部件以及它们的数量所组成，你在客户端直接调用递归查询就可以得到了，而不必在客户端编写代码来处理BOM表。而将BOM表呈现为TreeView，则不需要递归查询，而直接使用BOM数据表中的数据，编写相应的代码即可。

风中漫步

呵呵,偷偷的问句,这是(WITH 单据递归查询 (bomID,产品id,子件ID,单耗,损耗率,根产品,level,num) AS  ...)存储过程?

todaynew

风中漫步 发表于 2015-7-22 13:01
呵呵,偷偷的问句,这是(WITH 单据递归查询 (bomID,产品id,子件ID,单耗,损耗率,根产品,level,num) AS  ...)存 ...

归类为视图，只是写法特殊一些。

风中漫步

todaynew 发表于 2015-7-22 14:23
归类为视图，只是写法特殊一些。

呵呵,看来是mssq特有的了,以前没见过.

todaynew

风中漫步 发表于 2015-7-22 15:14
呵呵,看来是mssq特有的了,以前没见过.

Oracle也有递归查询，只是写法与SQL SERVER不大一样。Oracle的递归查询比较易于理解一些，也是Select开始。SQL SERVER递归查询比较不易理解的是最终输出数据集的Select子句放在了最后。

风中漫步

todaynew 发表于 2015-7-22 15:49
Oracle也有递归查询，只是写法与SQL SERVER不大一样。Oracle的递归查询比较易于理解一些，也是Select开始 ...

谢斑竹指教

cpxie

学习啦

13601812106_01

谢谢老师提醒，其实，我本意为两张表，一个是BOM的主件表，另一个为主件的子件表，我想更好的体现BOM的分层与计算，看到老师的SQL处理方法，就多想了一些。现在我的解决办法跟老师的差不多，速度很快。我处理的代码如下：
WITH CTE AS (SELECT     0 AS fid, mid, 0 AS Fmid, cast(0 AS decimal(18, 6)) AS 用量, cast('' AS nvarchar(255)) AS 备注, cast('' AS nvarchar(max)) AS TE, ROW_NUMBER()
                                                      OVER (ORDER BY getdate()) AS OrderID, 0 AS Levle, mid AS 产品
FROM         tbl_bom
UNION ALL
SELECT     tbl_bomsub.fid, tbl_bomsub.mid, tbl_bomsub.Fmid, tbl_bomsub.用量, tbl_bomsub.备注, cast(replicate('-', len(CTE.TE)) + '-' AS nvarchar(MAX)) AS TE,
                      CTE.OrderID * 100 + ROW_NUMBER() OVER (ORDER BY GETDATE()) AS OrderID, CTE.Levle + 1 AS Levle, CTE.产品
FROM         tbl_bomsub INNER JOIN
                      CTE ON tbl_bomsub.Fmid = CTE.mid)
    SELECT     *
     FROM         CTE

13601812106_01

由于视图不能保存ORDER BY OrderID，就在前台引用了