合同设备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 name FROM EquipmentForConstract efc LEFT JOIN SalesMan sm on efc.salesmanid=sm.id WHERE 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 signman FROM EquSmLs AS A INNER JOIN EquSmMaxLv AS B ON A.constractid=B.constractid AND A.level_num=B.level_num ) select * from SignmanTb
文章结束给大家分享下程序员的一些笑话语录：
很多所谓的牛人也不过如此，离开了你，微软还是微软，Google还是Google，苹果还是苹果，暴雪还是暴雪，而这些牛人离开了公司，自己什么都不是。
---------------------------------
原创文章 By
合同和设备
---------------------------------
