当前位置：Gxlcms > 数据库问题 > SQL基础知识--多行查询结果拼接

SQL基础知识--多行查询结果拼接

时间：2021-07-01 10:21:17 帮助过：2人阅读

 1 select PERSON_NAME from S_PROJECT_MEMBER where PROJECT_ID = ‘2c9081473e2f8bda013e3028e4700049‘;  

技术分享

技术分享

先直接上代码吧！-------------------------有问题的，麻烦指出--------------------------------------------------------

  1. <span style="color: #0000ff;">select</span>  
  2.     <span style="color: #ff00ff;">CAST</span>(<span style="color: #ff00ff;">stuff</span><span style="color: #000000;">((
  3.         </span><span style="color: #0000ff;">select</span> <span style="color: #0000ff;">distinct</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">,</span><span style="color: #ff0000;">‘</span><span style="color: #808080;">+</span><span style="color: #000000;">PERSON_NAME 
  4.         </span><span style="color: #0000ff;">from</span><span style="color: #000000;"> S_PROJECT_MEMBER  
  5.         </span><span style="color: #0000ff;">where</span> PROJECT_ID <span style="color: #808080;">=</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">2c9081473e2f8bda013e3028e4700049</span><span style="color: #ff0000;">‘</span> <span style="color: #0000ff;">for</span> xml path(<span style="color: #ff0000;">‘‘</span>)),<span style="color: #800000; font-weight: bold;">1</span>,<span style="color: #800000; font-weight: bold;">1</span>,<span style="color: #ff0000;">‘‘</span>) <span style="color: #0000ff;">AS</span> <span style="color: #0000ff;">varchar</span>(<span style="color: #800000; font-weight: bold;">1000</span><span style="color: #000000;">)
  6.     ) 
  7.         </span><span style="color: #0000ff;">AS</span><span style="color: #000000;"> PERSON_NAMES 
  8. </span><span style="color: #0000ff;">from</span><span style="color: #000000;"> S_PROJECT_MEMBER 
  9. </span><span style="color: #0000ff;">where</span> PROJECT_ID <span style="color: #808080;">=</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">2c9081473e2f8bda013e3028e4700049</span><span style="color: #ff0000;">‘</span> <span style="color: #0000ff;">GROUP</span> <span style="color: #0000ff;">BY</span> PROJECT_ID;

这里用的知识点有：

for xml path() 函数：

  1. <span style="color: #008080;">--</span><span style="color: #008080;">这里用来做字符串拼接</span>

CAST()函数:

  1. <span style="color: #008080;">--</span><span style="color: #008080;">CAST函数用于将某种数据类型的表达式显式转换为另一种数据类型</span>
  2. <span style="color: #ff00ff;">cast</span>( "abcdefg" <span style="color: #0000ff;">as</span> <span style="color: #0000ff;">varchar</span>(<span style="color: #800000; font-weight: bold;">2000</span>));

STUFF()函数：

  1. <span style="color: #008080;">--</span><span style="color: #008080;">STUFF()函数用于删除指定长度的字符，并可以在制定的起点处插入另一组字符。</span>
  2. <span style="color: #008080;">--</span><span style="color: #008080;">select stuff(列名,开始位置,长度,替代字符串)</span>

DISTINCT 关键字：

  1. <span style="color: #008080;">--</span><span style="color: #008080;">这里可以理解为去重，具体可以百度</span>

AS 关键字：

  1. <span style="color: #008080;">--</span><span style="color: #008080;">取别名 <br></span>

GROP BY ：

  1. <span style="color: #008080;">--</span><span style="color: #008080;">用于分组</span>

下面通过具体的例子来说明吧：    来自于：http://www.cnblogs.com/doubleliang/archive/2011/07/06/2098775.html

  1. <span style="color: #0000ff;">create</span> <span style="color: #0000ff;">table</span><span style="color: #000000;"> hobby(
  2. hobbyId </span><span style="color: #0000ff;">varchar</span>(<span style="color: #800000; font-weight: bold;">32</span><span style="color: #000000;">),
  3. hName </span><span style="color: #0000ff;">varchar</span>(<span style="color: #800000; font-weight: bold;">32</span><span style="color: #000000;">)
  4. )
  5. </span><span style="color: #0000ff;">insert</span> <span style="color: #0000ff;">into</span> hobby <span style="color: #0000ff;">values</span>(<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">1</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">爬山</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">);
  6. </span><span style="color: #0000ff;">insert</span> <span style="color: #0000ff;">into</span> hobby <span style="color: #0000ff;">values</span>(<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">2</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">游泳</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">);
  7. </span><span style="color: #0000ff;">insert</span> <span style="color: #0000ff;">into</span> hobby <span style="color: #0000ff;">values</span>(<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">3</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">爬山</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">);
  8. </span>
  1. <span style="color: #008080;">--</span><span style="color: #008080;">将查询结果根据行输出成XML格式</span>
  2. <span style="color: #0000ff;">SELECT</span> <span style="color: #808080;">*</span> <span style="color: #0000ff;">FROM</span> hobby <span style="color: #0000ff;">FOR</span> XML PATH;
技术分享 
  1. <span style="color: #008080;">--</span><span style="color: #008080;">改变XML行节点的名称</span>
  2. <span style="color: #0000ff;">SELECT</span> <span style="color: #808080;">*</span> <span style="color: #0000ff;">FROM</span> hobby <span style="color: #0000ff;">FOR</span> XML PATH(<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">MyHobby</span><span style="color: #ff0000;">‘</span>);
技术分享 
  1. <span style="color: #008080;">--</span><span style="color: #008080;">改变XML列节点的名称</span>
  2. <span style="color: #0000ff;">SELECT</span> hobbyID <span style="color: #0000ff;">as</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">MyCode</span><span style="color: #ff0000;">‘</span>,hName <span style="color: #0000ff;">as</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">MyName</span><span style="color: #ff0000;">‘</span> <span style="color: #0000ff;">FROM</span> hobby <span style="color: #0000ff;">FOR</span> XML PATH(<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">MyHobby</span><span style="color: #ff0000;">‘</span>);
技术分享 
  1. <span style="color: #008080;">--</span><span style="color: #008080;">构建我们喜欢的输出方式</span>
  2. <span style="color: #0000ff;">SELECT</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">[ </span><span style="color: #ff0000;">‘</span><span style="color: #808080;">+</span>hName<span style="color: #808080;">+</span><span style="color: #ff0000;">‘</span><span style="color: #ff0000;"> ]</span><span style="color: #ff0000;">‘</span> <span style="color: #0000ff;">FROM</span> hobby <span style="color: #0000ff;">FOR</span> XML PATH(<span style="color: #ff0000;">‘‘</span>);
技术分享

现在我对例子进行应用：

  1. <span style="color: #008080;">--</span><span style="color: #008080;">应用</span>
  2. <span style="color: #0000ff;">select</span> <span style="color: #0000ff;">distinct</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">,</span><span style="color: #ff0000;">‘</span><span style="color: #808080;">+</span>hobbyId <span style="color: #0000ff;">from</span> hobby <span style="color: #0000ff;">for</span> xml path(<span style="color: #ff0000;">‘‘</span>);
技术分享 
  1. <span style="color: #008080;">--</span><span style="color: #008080;"> for xml path(‘‘)  解决连接问题</span>
  2. <span style="color: #0000ff;">select</span> hName,(<span style="color: #0000ff;">select</span> <span style="color: #0000ff;">distinct</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">+</span><span style="color: #ff0000;">‘</span><span style="color: #808080;">+</span>hobbyId <span style="color: #0000ff;">from</span> hobby h <span style="color: #0000ff;">where</span> h.hName <span style="color: #808080;">=</span> hName <span style="color: #0000ff;">for</span> xml path(<span style="color: #ff0000;">‘‘</span>)) <span style="color: #0000ff;">AS</span><span style="color: #000000;"> A 
  3. </span><span style="color: #0000ff;">from</span><span style="color: #000000;"> hobby 
  4. </span><span style="color: #0000ff;">group</span> <span style="color: #0000ff;">by</span> hName;
技术分享 
  1. <span style="color: #008080;">--</span><span style="color: #008080;"> stuff(String,1,1,‘‘) 去除开始的连接符  +1+2+3  --> 1+2+3</span>
  2. <span style="color: #0000ff;">select</span> hName,<span style="color: #ff00ff;">stuff</span>((<span style="color: #0000ff;">select</span> <span style="color: #0000ff;">distinct</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">+</span><span style="color: #ff0000;">‘</span><span style="color: #808080;">+</span>hobbyId <span style="color: #0000ff;">from</span> hobby  <span style="color: #0000ff;">for</span> xml path(<span style="color: #ff0000;">‘‘</span>)),<span style="color: #800000; font-weight: bold;">1</span>,<span style="color: #800000; font-weight: bold;">1</span>,<span style="color: #ff0000;">‘‘</span>) <span style="color: #0000ff;">AS</span><span style="color: #000000;"> A 
  3. </span><span style="color: #0000ff;">from</span><span style="color: #000000;"> hobby  
  4. </span><span style="color: #0000ff;">group</span> <span style="color: #0000ff;">by</span> hName;
技术分享

--需要查询结果
‘爬山‘ ‘1‘+‘3‘
‘游泳‘ ‘2‘

  1. <span style="color: #008080;">--</span><span style="color: #008080;">方法一</span>
  2. <span style="color: #0000ff;">select</span> hName,<span style="color: #ff00ff;">stuff</span>((<span style="color: #0000ff;">select</span> <span style="color: #0000ff;">distinct</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">+</span><span style="color: #ff0000;">‘</span><span style="color: #808080;">+</span>hobbyId <span style="color: #0000ff;">from</span> hobby <span style="color: #0000ff;">where</span> hName <span style="color: #808080;">=</span> B.hName <span style="color: #0000ff;">for</span> xml path(<span style="color: #ff0000;">‘‘</span>)),<span style="color: #800000; font-weight: bold;">1</span>,<span style="color: #800000; font-weight: bold;">1</span>,<span style="color: #ff0000;">‘‘</span>) <span style="color: #0000ff;">AS</span><span style="color: #000000;"> A 
  3. </span><span style="color: #0000ff;">from</span><span style="color: #000000;"> hobby B 
  4. </span><span style="color: #0000ff;">group</span> <span style="color: #0000ff;">by</span><span style="color: #000000;"> hName;<br>
  5. </span><span style="color: #008080;">--</span><span style="color: #008080;">方法二</span>
  6. <span style="color: #0000ff;">SELECT</span> B.hName,<span style="color: #808080;">LEFT</span>(StuList,<span style="color: #ff00ff;">LEN</span>(StuList)<span style="color: #808080;">-</span><span style="color: #800000; font-weight: bold;">1</span>) <span style="color: #0000ff;">as</span> hobby <span style="color: #0000ff;">FROM</span><span style="color: #000000;"> (
  7. </span><span style="color: #0000ff;">SELECT</span><span style="color: #000000;"> hName,
  8. (</span><span style="color: #0000ff;">SELECT</span> hobbyId<span style="color: #808080;">+</span><span style="color: #ff0000;">‘</span><span style="color: #ff0000;">,</span><span style="color: #ff0000;">‘</span> <span style="color: #0000ff;">FROM</span><span style="color: #000000;"> hobby 
  9.   </span><span style="color: #0000ff;">WHERE</span> hName<span style="color: #808080;">=</span><span style="color: #000000;">A.hName 
  10.   </span><span style="color: #0000ff;">FOR</span> XML PATH(<span style="color: #ff0000;">‘‘</span>)) <span style="color: #0000ff;">AS</span><span style="color: #000000;"> StuList
  11. </span><span style="color: #0000ff;">FROM</span><span style="color: #000000;"> hobby A 
  12. </span><span style="color: #0000ff;">GROUP</span> <span style="color: #0000ff;">BY</span><span style="color: #000000;"> hName
  13. ) B <br>结果：<br><img alt="技术分享" src="http://www.mamicode.com/data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAASYAAABgCAIAAAA/0EhIAAAHzklEQVR4nO2dP4/6NgCG/Z0yHV/mt/mDdOnKdJI3htMtVRcqMTNf3BlVJ6bTT6qiq5RUHdjcwRBM/CeB+G94H0UolwuxDXn02iYEIjzxy6/r/xQ2m03btvLxXv4EYLkQKAdATHJUDoAFE1w5AIBKWOUAAAOgHABRgXIARAXKARCVsMr9VSyp3xewWIIr5+v4MYFyIBxQzkDihJ1G6jMHPAiUM5D/CZ1/DYGNZMpRxo1LRYeLrxpORz+h9/v9fr/f3rLZvKV4y9oWypVMSuUYF+eFCWpZ3MoRQhx/Pox+QjM2rEbTNJSxFG9Z20K5ksko5fR8qyivqlnKETuOwzqU67qOfwv+Ld4/WkqhHLibZMqpuVHXdb++3+/lyul0YoxXFbUdodfGYZSqlm1dx6bc4XDgnNd1/f7RMsZnKkcIeeBfEihXLlko12smhNhut0KI0+nUdR1j/OXlxfj0Uan6Lb5SjnG+3W4Z45vNm1zuUq63yL1iY3A0KFcuyZTbbN4Oh0Nd1/tb5GktJyfcyunmGJUbXdeZMpZ7/2jPccc54/x4PI6/1reOqY+DHWxP7IFy5ZJSuX5dTTnGeNd1csi02bzZlJPY+pbqDr5S7uvrq2mapmk4F+8f7ftHq9a5qugU625eek0kpNwzkFfH8nQ6bbfbforCrZxbmzn7G5U7Ho83Ucy5bIZglDFe17XDukGU2YxCyj0DyZR7fX3lnMszuB8mycd+vMQYG+1YSpGMCeYIDXfQTelYnpUTQghxPB4rSquKVhW1vtB2rwjGcs9ESuWupy/jcr6k67rN5o1/C9l5e319dcxYqpCxSZTBoxubcjJ+m6bh3xflGBWUCiEoqxjjFbUqd33FkXLPTRbKyc5kP37rB0ujyqkpp24c7GNbseFQTvrGv286xl3XHY9Hxrkj5a6vOFLuuUmpXD8/qXYm5UTl6wWbcoOeobHHqO9je/oAm3K9b5xflKNU0Orr64uyalQ5cpmiHDWqtSeeBMqVS0rlXl5eqooOHtUVyZTS3R1L93adEeW4oLzqZ1ybpjkcDvv9Xs5bWl/oaWM5455IuSWBbxIYMCpX17X8QFwuZ+VoJWhV13VFK4dyxPnhm005x0YoVy5QzoBRuYrSHz9+VJTKRSp3/ij80jF+YCzXdywHWg66nVBuMUA5A1NOaCmYvoR/y9oWypUMlDOQ/wmdfw2BDShnwOPtEgAYAOUAiEo85X77/Q8sWLBEVc5XWUZ+/vwZ9PgPEK1KGbYd2IByAYFyQAfKBQTKAR0oFxAoB3SWq9znerVafw52+lyviLLVuE+4KvXsKCE+C4ZyBfGEyilneyLldpRQSj2WDOUK4vmUW63XlNCdfZ9wVbpWgu7EzqNzUK4gFq7cmp6vDL5xrDftsrKj12uI6U5up3RFCCGr9W59Xvk8H+G847gxZhOkcV51h3IFsWjliJZmimPm0753UhqlrqzWn+oTJihjNOFsnFfnoFxBLFo5fcx23bijg7jrY84Ug4qKKiPKmEy4PYQn56BcQTytcpfO42r9KXaU3GaXS7lzcD5SJaFk3LCKs4ByBfG8yp1HcGflLh5I+2zKCcW5CfrpJgwk8+UclCuIZ1buZqRGpnQshVCmWh6YPlHi9FqDO2JzckEgX5arXAbg6hOgA+UCAuWADpQLCJQDOlAuIFAO6EC5gEA5oBNVueD3TtJ+KjGHxwj4ehNBBGIr56s4nbZt//7n36yWODJAubJYlHKEkOSaqcv0X0GY2fAIpQBfLEq55I4h5cAoi1JuNOXkZSNT0gkpBwKRiXLKVY6PYks59cp/fYvRLl/Kjckw0urd4Mt+9oY/9IKBNCRXrr+80YNyDn/cj9FTbkKrr9djjpgJ5coiuXKSsCk3RTlVM9t65JS7MPJ9AyhXFotSbk7KhVBubCw3bLXZrbFvG0C5sliUctNTzjjAS55yxvtCYCy3MBal3F0pZ9wtVcqp9zvShngjMQflymJRyk1POd2oPFPOvb1v+KOvGUjAopR7IOWyHctNv+UDlCuLRSk3MeX+tnxYlzzl9H9L3Ld8gHJlkYlyHpiScg6FiJ1gKeet4RFKAb5YlHKjKWe0a1SbYCnnreERSgG+WJRyc/QIsSDlgM6ilEvuGFIOjLIo5ZByIH8WdSOGNvU9F/THOPh6E0EEFpVy4Q7+GHGqlGHDgQMoFxAoB3SgXECgHNB5IuXUyQz3xIb8vC5ClbwA5coivXLXi+jnXfJlO/OMpg2MMgo2XzyHDL5a7S4FZEhi5e77vTYnjjNvcLnJYEu/z+Nl31klj612lALyJH3K9cz8fcPpHUuHXbqKc5giw/xfdYRyZZGPcgFTTjLoYbq98mLdBBmQck9HLsr1P2j6MKNjOV2zQe7pPc/Bbr6q1DO/1VNKAVmRhXKhz7zeIjFhBmWg3JysG+1Iz2/1aCkgN9IrF+3M0ydOjL1Ho5/eq+Sr1e5SQIakVm5HPUyTCyHuUU7907iPvrPnKvlrtasUkCWJlRvc2SrQjOX0jqX+3xAdS4+tdpQC8iR1yvljyvSJ/qfxX451L1XyC5Qri+UrJ9EnRUJ/QjBaJV9AubJ4FuWSAOWADpQLCJQDOlAuIFAO6CztRgxPiK93EMQhqnK+ygKgXKAcAFGBcgBEBcoBEBUoB0BUoBwAUYFyAETlf9cVjERKXuwzAAAAAElFTkSuQmCC"></span>

第一次写笔记。。。。。2016.06.25

 

SQL基础知识--多行查询结果拼接

标签：

人气教程排行

本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！