SQL树形数据的一种解决方法

业务需求：

一份电梯购买合同包含多个设备，合同和设备信息分布存储于合同表和设备表，每一个合同有一个业绩归属人，每一个设备有一个业绩归属人，一般情况下

合同的业绩所属人跟所辖的设备业绩所属人都是一样的，但是存在一些情况合同里面的设备业绩所属人各个不同。

现在要查询合同基本信息，但是要附加一列统计合同里面 设备业绩所属人+设备数目的信息，例如合同号13J001/010  设备号是13J001到13J010  

其中3台业绩人是张三，5台是李四，2台是王五，那么该列信息应该显示 张三3,李四5,王五2

原始解决方法：

很容易想到的一种解决方法就是写一个标量值函数，传进去一个合同ID，然后select出这些设备信息，使用游标遍历拼出来这样一个字符串，返回。

代码我就不写了，实在是很简单。写完之后部署到服务器上，虽然合同数据只有1万条左右，但是出奇的慢，慢到无法加载数据。我想这个函数计算

逻辑也不是很复杂啊，怎么慢到这种程度呢？

唉，无奈，优化下。

想了下并不是所有合同中的设备业绩所属人都是不一致的，那我查询之前加个判断，如果你存在这种多个情况我就调用函数，不存在我就不调用呗。

修改之，部署到测试环境，果然快了很多，但是还是要10s左右的加载速度，不满意还要继续优化。

优化方案：

之前方案为什么会这么慢呢？很肯定的原因就是我用了函数和游标，SQL执行时候是无法优化函数语句的，游标的遍历执行也是很耗费资源，那么我们能不能

利用连接查询之类的方法实现需求呢？答案是肯定的，首先看下我们现在设备表的数据：

前面是合同ID，后面是 设备业绩人和数目，我们要的最终效果是下面这样的：

看一下第一张图，其实按照合同ID去group的话，我们可以按照顺序给name编出次序1 2 3等，就像下面的这个样子：

这样的话就很像一棵树了，我们要做的就是根据树的ID先分出类来，然后根据level_num一个个的把name加到前一个name中去。

下面是最终的SQL，明白上面的道理应该很同意就能看懂：

;WITH EquSm AS (SELECT constractid,(sm.name+CAST(COUNT(Salesmanid) AS VARCHAR(10))) as nameFROM EquipmentForConstract efc LEFT JOIN SalesMan sm on efc.salesmanid=sm.idWHERE stateforupdate='正常'GROUP BY constractid,Salesmanid,sm.name ),EquSmLevel AS(  --构造层级树   SELECT constractid,name,ROW_NUMBER()OVER(PARTITION BY constractid ORDER BY constractid) AS level_num   FROM EquSm),EquSmLs AS(   SELECT constractid,CAST(name AS NVARCHAR(100)) AS name,level_num    FROM EquSmLevel WHERE level_num=1       UNION ALL       SELECT m.constractid,CAST(m2.name+','+m.name AS NVARCHAR(100)) AS name,m.level_num    FROM EquSmLevel AS m INNER JOIN EquSmLs AS m2 ON m.constractid=m2.constractid AND m.level_num=m2.level_num+1),EquSmMaxLv AS(   SELECT constractid,MAX(level_num) AS level_num FROM EquSmLs  GROUP BY constractid),SignmanTb AS(SELECT A.constractid,A.name as signmanFROM EquSmLs AS A INNER JOIN EquSmMaxLv AS B ON A.constractid=B.constractid AND A.level_num=B.level_num )select * from SignmanTb

参考：http://www.cnblogs.com/dataadapter/archive/2012/07/19/2598506.html