sql合并某一列

oralce写法：

select WM_CONCAT(A.title) as citys from tmpcity A

sql server写法：

方法一：用户自定义函数

CREATE FUNCTION FN_Merge (@Student NVARCHAR(50))

RETURNS NVARCHAR(50)

 

AS

      BEGIN

            DECLARE @Course NVARCHAR(50)

            SELECT @Course = ISNULL(@Course + ',','') + @Course

            FROM SC

            WHERE Student = @Student

            RETURN @COURSE

     END

 

SELECT DISTINCT [Student]

      ,dbo.FN_Merge([Student]) AS Course

 

FROM [dbo].[SC]

方法二：FOR XML PATH 

SELECT  DISTINCT [Student]

      ,STUFF(

                        (

                              SELECT ','+[Course]

                              FROM [dbo].[SC]

                              WHERE Student = A.Student

                              FOR XML PATH('')

                        )

                        ,1,1,''

                  )AS Course

  FROM [dbo].[SC] AS A

mysql写法：

select GROUP_CONCAT(A.title) as citys from tmpcity A;      --默认的逗号分隔
select GROUP_CONCAT(A.title SEPARATOR  ' ') as citys from tmpcity A;   --用空格分隔