mysql CONCAT与GROUP_CONCAT

连接字段

CONCAT(字段名)返回多个记录的字符串与limit 1配合使用，如果有一个NULL则返回NULL

示例

CONCAT(id,name)

1lucy

CONCAT(id,',',name,',',sex)

1,lucy,man

CONCAT_WS('_',id,name,NULL,sex) 如果参数有NULL则忽略

1_lucy_man

GROUP_CONCAT([DISTINCT] expr [,expr ...]

[ORDER BY {unsigned_integer | col_name | formula} [ASC | DESC] [,col ...]]

[SEPARATOR str_val])

SELECT locus,GROUP_CONCAT(distinct id ORDER BY id DESC SEPARATOR '_') FROM info WHERE locus IN('AB086827','AF040764') GROUP BY locus;

AB086827 | 2_1

AF040764 | 24_23

SELECT locus,GROUP_CONCAT(concat_ws(', ',id,journal) ORDER BY id DESC SEPARATOR '. ') FROM info WHERE locus IN('AB086827','AF040764') GROUP BY locus;

AB086827 | 2, Submitted (20-JUN-2002). 1, Unpublished

AF040764 | 24, Submitted (31-DEC-1997) . 23, Unpublished

本文为胖虎原创文章,转载无需和我联系,但请注明来自胖虎博客panghucat.cn