合同设备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
合同和设备
---------------------------------