mssql的 for xml path 与 mysql中的group_concat类似MSSQL For xml Path

最新推荐文章于 2025-01-19 21:41:57 发布

转载最新推荐文章于 2025-01-19 21:41:57 发布 · 189 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/q149072205/p/5585206.html

文章标签：

#数据库

本文介绍了使用SQL进行复杂查询的方法，包括如何利用FOR XML PATH实现数据的特定格式输出，以及如何使用group_concat函数聚合多个字段值。

/****** Script for SelectTopNRows command from SSMS ******/

SELECT D_ID,[D_Name] as Name FROM [LFBMP.LDS].[dbo].[LDS.Dictionary] Where '32,33,34,35' like '%'+convert(nvarchar(10),D_ID)+'%' FOR XML PATH

SELECT [D_Name]+',' FROM [LFBMP.LDS].[dbo].[LDS.Dictionary] Where '32,33,34,35' like '%'+convert(nvarchar(10),D_ID)+'%' FOR XML PATH('row')

SELECT [D_Name]+',' FROM [LFBMP.LDS].[dbo].[LDS.Dictionary] Where '32,33,34,35' like '%'+convert(nvarchar(10),D_ID)+'%' FOR XML PATH('')

SELECT [D_Name],D_ID FROM [LFBMP.LDS].[dbo].[LDS.Dictionary] Where ',32,33,34,35,' like '%'+convert(nvarchar(10),D_ID)+'%'

Select CompanyID,CompanyNameCN,CompanyNameEN,CompanyTypeIDStr
,(Select group_concat(TypeNameCN SEPARATOR ',') from com_companytype where concat(',',t1.CompanyTypeIDStr,',') like CONCAT('%,',CONCAT(CompanyTypeID),',%')) as CompanyTypeIDStrName
from com_company as t1

-- 类似MSSQL For xml Path
-- Select group_concat(TypeNameCN) from com_companytype where ',1,2,3,' like CONCAT('%',CONCAT(CompanyTypeID),'%')

转载于:https://www.cnblogs.com/q149072205/p/5585206.html