for xml path 巧用

今天技术研究部（TRD）的一位DBA同事对我们系统提了一个调优BUG，用到了for xml path语句，我开始看了半天都不知道什么意思，后来看了下 http://www.cnblogs.com/doubleliang/archive/2011/07/06/2098775.html  后才知道他的用法，这里先附上调优的原文，大家先看看：

-------------------------------------------------------------------------------

程序调用中：

 

在sql中用类似下面的语句替换：  

Select BillCode, isnull(list, '') as 导购员
From PosBill
  left join (
Select m.billno,(select '['+oprName+'] ' from ShopOpr s where s.OprID in (select oprid from POSSaleman p where p.billno=m.billno)  for xml path('')) as list From POSSaleman m group by m.BillNo
) a on a.billno=posbill.billno

总结：C#中本来foreachDataTable的行就慢，如果在里面再加对DataTable的Select就慢上加慢。

-------------------------------------------------------------------------------  

各位是不是觉得有点晕呼呼，我开始也看得有点晕，因为那段C#代码也不是我写的，后来看了SQL后才明白。

实际上，C#代码中他是想通过一个单据的billno去找到导购员的名称，因为单据和导购员又是1对多的关系，需求要求如果有多个导购员必须用[名称1]，[名称2]这种格式来，并且一个单据只显示成一行，而不是多行，自然不能用简单的inner join或left join搞定。所以才有了上面C#的那种写法。

关于上面的SQL语句，博客  http://www.cnblogs.com/doubleliang/archive/2011/07/06/2098775.html 已经讲的很清楚 关于for xml path 的用法，在这里，我也就只补充几句。  

1. SELECT '[' + employeename+ '],' FROM dbo.EMPLOYEETREE  FOR XML PATH ('')
与  
SELECT '[' + employeename+ '],' A FROM dbo.EMPLOYEETREE  FOR XML PATH ('')
结果是完全不同的，有列名时，他会以XML的方式输出，没有列名时，他会相当于执行循环累加字符串的操作，我们常常会不给他指定列名，让他实现累加的效果。  

2. 如果出现多个字段，他会按每个字段以字符串的方式遍历想加，例如：  
SELECT '[' + employeename+ '],' + '!' + employeename + '!'  FROM dbo.EMPLOYEETREE  FOR XML PATH ('')
与  
SELECT '[' + employeename+ '],' , '!' + employeename + '!' FROM dbo.EMPLOYEETREE  FOR XML PATH ('')

结果是一样的。

---------------------------------------------------------------------------------

现在回过头再看了看那句SQL，其实可以将子查询改成inner join，并且用CTE表达式书写，经测试这样效率更高，代码更加清晰，感谢 @冲杀 同学提供的思路

 

WITH list AS
(
    Select m.billno,(
        select '['+oprName+'] ' from ShopOpr s INNER JOIN dbo.POSSaleman p ON p.billno = m.BillNo for xml path('')) list    
    From POSSaleman m group by m.BillNo
)
Select BillCode,isnull(list, '') as 导购员 From PosBill left join list a on a.billno=posbill.billno