欢迎您光临澳门贵宾厅vip官网官方网站!

cte递归向上统计

时间:2020-02-08 19:53

数据字典如下

澳门贵宾厅vip官网 1

澳门贵宾厅vip官网,通过sql可以得到如下结果

select yskm,SUM(je) as je from view_dj where swdjh='321311570376851' group by yskm

澳门贵宾厅vip官网 2  

现在项目有个要求,要统计出他们父级科目的金额。

那怎么向上推出他的父级科目代码的金额,

解决思路:

  1. 创建一张临时表来保存结果
  2. 用sqlserver cte 来查询出自己及父级所有的科目代码,代码如下澳门贵宾厅vip官网 3澳门贵宾厅vip官网 4

    1 with cte as 2  ( 3 select a.kmcode,a.kmname,a.pidkm from sys_km a  where kmcode in 4 ( select t1.yskm from view_dj t1 where t1.swdjh='321311570376851' )5 union all  6 select k.kmcode,k.kmname,k.pidkm from sys_km k inner join cte c on c.pidkm = k.kmcode 7  )8 select * from cte
    
View Code  

查询结果如下:![](data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAY0AAACdCAIAAADHZLOpAAATnklEQVR4nO2dzXLbthbH8TzZdZNh9QZ5g6wlj/oQ2XVX2Z7xAyRP4HEjxTO+m77DnfojG8/cfrf3ruqkseUo8vAuRFEQgAOAIA+BQ5zfcDoUBIHngOA/AE3+K07PznnjjTfeUt7E6dl5icnHjx9R24/CIJNCglxfkQs4B1inQhhkUkiQ6ytyAecAuk59+PABtf0oDDIpJMj1FbmAc4B1KoRBJoUEub4iF3AO9K5Ttyej0cktxpHwWtboLyn6hFz2m/6M1KusUwmCrlN3d3d7nwehU/0lRR+1r3T03ouqU+6Amd5B16m///577/MgdKq/pOij9pUPUXUqJGAGGXF6dv7ly3r5uMLY1uunv/77v732b46L4vj6cbV8fDcRQhTH1zfHRXEwKYQQojh6d1jtvF/WdYQQQkwW259XBQfzR6VEiE3Lu5KvD28SS+rmuCiODyd7Sc3rJDcllg5BTq2vvqqyq05i3XvKqWw2VLoMOPZVyaiI07Pz1Zf1w/IzxrZeP/3x51977V8fFcXR1XIxFsXseluy2Zd3iqOr5ftZIcbzzQ839XclV4eFmCwe5DrzA1H9Smq5OLpKKqnro0JsA9bD29WBOgQ3tZ4GwLaXqpOon+7NqWwwVDoOOPZVyajg6tTT09Pvf/ypDdNq5rB/kWs70GWsXNJ6iZDZDvEEk5K/2sUr1dF/i5xaTwNA7gFxMHeeSmevdh1w7KuSURGnZ+er1Zf7h0eMbf309Nvvf+61f3VUiMnbh5tZIcbzbUlxdKnv1B/l39Ylep26REze4qTTcVLV/mIsitmVKSn9t8ip9dRXxpMInkqPXu064NhXJaMSQ6fqEba56sDBJw3Q6mLelVzOCjFe3D/slyi/wrmwWyVl1qltkPOJsOgUfmo9DYBt5NVJ1LKrTmWDodJxwLGvSkZFnJ6df159+XS/xNjW66dffv19r/3Lw+fF4eX98tP98nJWCFF893Zbcqnt3M/H20XO+O3259Wip2pEKim0XxXfXSaWlFSz/upyJq3n5Dq2DkFJra++KvZOopymfCqbDZUuA459VTIq4vTs/PHz6p9PDxjbev308y+/4bUfZRtkUv311eXs+fPZj7EDswcc+6pkVMTp2fnj4+rjP/cY23q9/unnX/Haj7INMqn++urH2fPns3/HDswecOyrklERp2fny8fPHz5+wti+rNf/+ekXvPajbINMivtKDjj2VcmoiNOz8xuGYSRiX5WMyuZ5dERWqxVq+1EYZFJIkOur1WoV+6pkVMTp2fkdMkKIQf6XGSSxL0nGQKVTeAe4u7v71205sI1Hsz+0+opWtPnQh04JIaIrS7ebEAKvxwYGrSufVrT5wPOpkI1Hsz+0+opWtPnA86mQzT6f0r811pcLmzYYUEepsHnwW/lWaBgb8Y+8pHbl04o2H+w6dTEV04t2B3DNpy5eiOm324/fvtxeHy8vGtR5PVVKwtqpS168bjWfSkqnFMUxCpD8LVQO6ZozMNeVvzfGLrZncn/cuerURdsSn3aComXiAOnU7cnIcJ4DgOdTt998VR2i0o4fTp59dfKm+mr0zQ+edS5ebJXl25fi2avbwHZeT0VVsidnjeZT+nxEQa4p7whpOqO3WTafLkHldg21BOwTgxH4ytfG2M7As97zqXMx3X59MRWjk1uvdhpHy8QkpfnU66k8tZEmNdY6O33ZbkHtvHk1evbqdlPy5tXIPqVqP5/SdcqoR0Zp8zyosdxezV5ZaNMro7AqNJhPmXdddSpxcjapfQqJlolDpVOA9fC7SW3vG7q57k/ttENRinrfXufNq9H+4i6wHUnvbr/5Ski/Crk/5ZyY6Nd8/VGuBu0YD+oMRo9Nbx+qoDTiPG6Nv07Jfuj73ui2OrcnI0V8fNoJjZaJQ6VTgKXnYiwO5u0MEv3nU8E69ezl9NnmGnp5Ea5Tu/tToxcvR3adajOfki91/cpXyo0/h9o3Sobesl7u1BpjU/q+kR50ajSdyqs61qnh0YdOCez5lDQP0ldw/jpVb9V9LrT5VOkhH8bKliM6f2s5kHM+Ve7LmT01hT7mU7u7UWJ6wTo1QLY6Zbb0lKwmQ7ce7k8ZVnAB7exCqvQOaT6llBinV86fQ98aDyQLiv+cSClUVMmijAo93J9SVYnvTw2OPnTKcz5l+judT536262+BLWzu8/1eioi/b0PPEntdMp4IP1Xlpj1IFF0yvB3Op869bfbW1U+7QRGy8Sh0inAeng+FpPv2xm5Zvj8lFe/ux5ZchZCX9kbtM+nPCWvRNIpfn6KAah0Cs9Fl59Ht5e00Smf8uB9n5h91IrWlU8r2nzY6hSai25u7/d5aooiDco6y6dNH620zI+cUydIp/R2WKcYbCqdwnOGzXA+xcjQuvJpRZsPlU7hWbjmNp9iFGj1Fa1o86F+Hh0Lnk9lDq0rn1a0+dCH7/BdbINgjP8yQyX2JckY6MN/Cq/xWAwyKSRo9RWtaPOBdSqEQSaFBK2+ohVtPrBOhTDIpJCg1Ve0os0H1qkQ7En5POdZup5gqsud9+w92/Gs2Tm0BgCtaPMB1CngzYPGuE5849cmwkq0Nytaga1T+jOf+rOgwgXUDhQPEvEGAEa0TBzMOiV5j+k2ZM2AT3yQ7WxYyc7yEXc0+2hHXVPekWWldCmdLmrQV5bC3ogxADCiZWLiXvcpr5w3pcE/pw3sOJqVKIZE7Ydz+/mUrlOKYFlaaKo78rGMoolKvAGAES0TB7vv8Gr5+P6wEJNFK99hawDNbNICS3bzKclVrQVOnbLMpEpJlUptaSZMa7rSbz5lVDfPRvCINwAwomXiYPfz/Hx1WIji6Kqdn6c1gF50anfjYzSdtpoebmgzn7Iv9JrOp4z7PkLGOhUULRMHm061F6l0dEoujT6fKmFhaqRTSsvG3xrnbsaQkGCdYtoD+nlezgpRHF22M/Pc+HlaA+jj/pRE2z8LbMC4P1UC99HtJUq5ZekHtYYN359i2gPo1HwiWjsON9apBnaxje+jV7u7/yVlK5D+3qd8q+uL/z7UeNI61fEAwIiWiYPZd/j78d7V9Xx23cZ32BrAAJ+f8sEoQKVJofRySO/8tSxRneLnpxiAPnyHY+fYPZ2v+yw1fcqheRN0rD7VitYAoBVtPvThOxw7x+6xJOUpNIqOGNeGYc1Ccy5jTSiLDqE1AGhFmw99+A7HzrF7BpkUErT6ila0+dCH73DsHLtnkEkhQauvaEWbD334DsfOsXsGmRQStPqKVrT50JPvMMNQIfYlyRhg/6kQBpkUErT6ila0+cA6FcIgk0KCVl/RijYfWKdCGGRSSNDqK1rR5gPrVAjOpHyeTrK/8iKX+9RMFloDgFa0+eD2HW75MoLrxKf12oQnUFLGZyyVZy+FC6idkqBIlVgDAAvWqTQBdKo7o174xKdoO+uJRacsJfoT4ZYfQoXk6HoAxIqWiYlz3dfSdpiYrYcnneiUk7q+PuEiBM4AwIJ1Kk1cvsM3x4U4mIeaDkf1HUbEue6TV3Dyt0plY7nSjr2R9MEZAFiwTqWJy89TiPGcqJ8nIu3nU8Z9HyFjnUKFdSpNHP7oD8v3s6KVVLFO6fs+98jlOpms+1inGAjQd1g2IC5mNwR9hxHx0SloyWYstyz9oNaowPenmPaYdepyVojx4v7h8f7hZlaI8Zyi7zAidp3S9cV/X2nHUkIFnAGABetUmph9h2Xr4Tamw1F9hxGx30dXSkptclS61oDQ/sB1ip+fYgDYdzgE/6QgZbGsAfV90qs/WgOAVrT5wL7DIXSlU/JHaM5lub9OAloDgFa0+cC+wyEMMikkaPUVrWjzgX2HQxhkUkjQ6ita0eYD+w6HMMikkKDVV7SizQf2HWaYPWJfkowB9p8KYZBJIUGrr2hFmw+sUyEMMikkaPUVrWjzgXUqhEEmhQStvqIVbT6wToXgTMr+AKdSYnkeylkn/cepaA0AWtHmg0Ondr6eobhOfFqvTXgCJeXzrCb0lLnSjrOOsX6C2EeX6XTHhHUqTaw6dXsyEng6laLtrCf20eypKR3qlE+FWNhGl/mt45iwTqWJRaduT0aj6bTtCGown0rA1sMTT50S++jfQtV86kAHTQ2wr8DTHRPWqTSBfYcXB2Ly7vro6+LofbDpMJrvcGSc6z7/5V5wHeW40FfRgfoKPt0xYZ1KE8jPczEWB/Pl56vDojh8n57vcGT851OWQvvsyVlHPyLrVHtYp9LErFO1PLFOGbEkpSzxPNd9ATvKQaGvosM6xbTH6Od5Myv2LrD0fIcj49Qp58KtfR2lsr6fCHx/immPwx+9pTk6mu9wZCz3p0pAceQS510nnzrGcko6xX/vY7wBfYc32+WsSNJ3ODL+zyXg3VDXjwWVxIWfn2Law77DITS9j25UGZ8Zk72OzwwrOrQGAK1o84F9h0Pw0SloKWcp8fkV1L69nYjQGgC0os0H9h0OYZBJIUGrr2hFmw/sOxzCIJNCglZf0Yo2H9h3OIRBJoUErb6iFW0+sO8ww+wR+5JkDLD/VAiDTAoJWn1FK9p8YJ0KYZBJIUGrr2hFmw+sUyEMMikkaPUVrWjzgXUqBM+kfB5ictaxP1ElvwkoY2wkyhs2tAYArWjzAdKp3SsNLR09XSe+8Xszhjp1kfReK+r7Nz3olKI4RgHSH3zXyyFd84m/E6IMALRomThYdAr7kg7yHTbUuZhuv67c3PH9i31Gs6ciWHTKXk1RH8t8qk9V0okwAFCiZWIC6FR3b7A3+OfUx9dF39XHJr4/jHM0+6+wgnVKLrdX1qdXupbhEWEAIEbLxAHwHb45rh2okvId1uvcnowU8enBb8+elL4cs4iCc91XK5EuMZAAGcvLJurZIf0PAMxomTgAvsPzA1EcXS0/Pyzfzwoxnqfi52kcpqPpVF4cRNQpZZ6iL830+iUgGYoYQdrkE61RLoekU8oAwIyWiQPkj27wIE5Wp8TuhoWYXsSfT9U4taCNTpUe86lyX86gOqj0PwAwo2Xi4PDzbG/p2cPtCXVwJ3B/aoNdC+yzG2jJ5pwTKYXGpWJvIlX2cn+qQ5911qk0MevU5awQ48X9w+P9w2IsitlVMr7D5j/3bL7d/nOK71/cj07JOxad0idTiroZf+ITfydEGACI0TJxAH2Hvx9Xwz013+FhPD9lESb7R2jd59ly0jrFz08xAOw7HEJLnfIpD953Ts3KftWK1gCgFW0+sO9wCG10ymcxaJkfOadOkE4Z71tZIukKWgOAVrT5wL7DIQwyKSRo9RWtaPOBfYdDGGRSSNDqK1rR5gP7DocwyKSQoNVXtKLNB/YdZpg9Yl+SjAH2nwphkEkhQauvaEWbD6xTIQwyKSRo9RWtaPOBdSqEQSaFBK2+ohVtPrBOheCZlM8DSs469ueeoFdnjI3Yn71CgtYAoBVtPsA61dG7CK4Tn9ZrE570oFOK4hgFCHoG3fKrAbw3gwrrVJrY/NE78XKFT3yKtrOe+IxmT0XwfLfG8jZM6ZpP9alKOh0PgGjRMjEBdKq7K77BP6cJ2M564hzN/iusYJ2Sy+2V9emVcW2IRMcDABnWqTQx+w5fH30tJu/a2A1H9B3uAXtS+nLMIgrOdV+tRLrEQAJkLC+bqGeHdDsAsGGdShOzn+fVYVFMDiqL9Mki2Mwziu9wD0BJKfMUfWmm1y8ByVDECNImn2iNcsk6ZYR1Kk1AnSLkj96h7awnXd1Hb6NTpcd8qtyXs8TXfaxTDATgOzyf1F7D9HyH8elEp+yzG2jJ5pwTKYXGpWJvIlXy/SmmCyB/9MVYTN4+PN4/3MwKMZ6T8h3Gpx+dkncsOqVPphR1M/7EJ/5O6HgAIMM6lSag7/Cnt5NqvI/n5HyHsWmvUxZhsn+E1n2eLSetU/z8FAPAvsMhtNQpn/LgfefUrOxXrWgNAFrR5gP7DofQRqd8FoOW+ZHPXS1nm1BNDGgNAFrR5gP7DocwyKSQoNVXtKLNB/YdDmGQSSFBq69oRZsP7DscwiCTQoJWX9GKNh/Yd5hh9oh9STIG2H8qhEEmhQStvqIVbT6wToUwyKSQoNVXtKLNB9apEAaZFBK0+opWtPnAOhWCMynnY1Cl9clM/zq9PQYVDK0BQCvafDDrVO2iaHyLoRGuE5/WaxOeQEnJ79bVJSUgWAFPkEOkLFWWAQCc7piwTqWJx3zq9mTUYhjBjadoO+uJvcc8NaVDnfKpEAuwr8C3jmPCOpUmbp2SfFNCaDCfSsDWwxNPndqfk4LSo1fzqQMdNDXAvgJPd0xYp9LE7Du8226Oi+L4Ohnf4URwrvv8l3vBdZTjQl9FB+or+HTHhHUqTcx+nrKxZxszz879PBPBfz5lKbTPnpx19COyTrWHdSpN7Dq1GIuDeQuRylCnlCWe57ovYEc5KPRVdFinmPZAfp4dOA6j+A6ngVOnnAu39nWUyvp+IvD9KaY9Fp26mRXF7KqVSHXvO5wGlvtTJaA4conzrpNPHWM5JZ3iv/cx3lh9h9s5DiP5DqeA/3MJeDfU9WNBJXHh56eY9rDvcAhN76MbVcZnxmSv4zPDig6tAUAr2nxg3+EQfHQKWspZSnx+BbVvbycitAYArWjzgX2HQxhkUkjQ6ita0eYD+w6HMMikkKDVV7SizQf2HQ5hkEkhQauvaEWbD+w7zDA7Yl+PjBlxenYeOwaGYRgbrFMMw6QO6xTDMKnDOsUwTOr8H6/Hoxjxrq5sAAAAAElFTkSuQmCC)

      3.在通过cte和金额表关联把数据插入到临时表中代码如下

      

insert into #tmphjcx select * from ( select distinct a.kmcode,a.kmname,a.pidkm,b.hjje,b.guoshui,b.dishui from cte a   left join( select t2.kmcode,(t2.kmcode+'_'+t2.kmname) as kmname,hjje=sum(je), guoshui=sum(case when t1.ic='1' then je end),dishui=sum(case when t1.ic='2' then je end) from view_dj t1left join sys_km t2 on t2.kmcode=t1.yskm where t1.swdjh=@bmgroup by t2.kmcode,t2.kmname)bon b.kmcode=a.kmcode)c

查询结果如下

澳门贵宾厅vip官网 5

4.看上面的临时表的结果,可以看出科目代码顺序现在是对的,但是他们父级科目的金额都为空的,怎么根据下一级的科目金额获取上一级的科目金额呢

5,这时我们应该按科目代码的长度倒序排列,逐个更新金额,应该我们只有计算出上一级的金额,然后再上一级金额的基础上计算出下一级金额。(注意:逻辑思想很重要)

select kmcode,hjje from #tmphjcx order by len(kmcode) desc

查询结果如下
澳门贵宾厅vip官网 6

6。这时我们再更新上面临时表中父级科目金额(创建游标遍历金额表,和临时表关联来更新金额表的值)

declare @kmcode varchar(200)declare @hjje decimal(18,2)declare s cursor --declare 创建游标staticfor select kmcode,hjje from #tmphjcx order by len(kmcode) descopen s --打开游标fetch next from s into @kmcode,@hjje --提取上次提取行的下一行while(@@fetch_status = 0)begin if( @hjje is null)   begin    update a set a.hjje= b.hjje,a.guoshui=b.guoshui,a.dishui=b.dishui            from #tmphjcx a left join(select sum(hjje) as hjje,sum(dishui) as dishui,sum(guoshui) as guoshui,@kmcode as kmcode from #tmphjcx b where pidkm=@kmcode ) b  on b.kmcode=a.kmcode where a.kmcode=@kmcode   end  fetch next from s into @kmcode,@hjjeendclose s --关闭游标deallocate s --删除游标,释放资源

 

7.查询出我们想要的结果

 澳门贵宾厅vip官网 7

结果如下 最后:好的想法+技术可以解决一切难题。如要转载请保留原文地址

上一篇:没有了
下一篇:详细讲解