mysql 逗号分隔转行_mysql 逗号分隔符 行转列，列转行

行转列，

直接使用 group_concat  分组函数

select id,group_concat(name) from aa group by id;

由于这个是分组函数，还可以用distinct去重

select id,group_concat(distinct name) from aa group by id;

还可以指定分割符，默认是，耗

select id,group_concat(name separator ';') from aa group by id;

最后他还能排序

select id,group_concat(name order by name desc) from aa group by id;

列转行，把group_concat转过来的东西，再变回去，使用 find_in_set 需要做左联

SELECT

Books.BookId,

Books.Book,

BookAuthors.AuthorId,

BookAuthors.Author

FROM Books

LEFT JOIN BookAuthors ON (find_in_set(BookAuthors.AuthorId, Books.Authors) <> 0)

参考

http://stackoverflow.com/questions/14591779/how-to-represent-cross-apply-and-split-string-in-mysql

0.00 avg. rating (0% score) - 0 votes