翟鹏的博客

说明文档:

http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat

GROUP_CONCAT(expr)

This function returns a string result with the
concatenated non-NULL values from a group.

It returns NULL if there are no non-NULL values.
The full syntax is as follows:

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

 [ORDER BY {unsigned_integer | col_name | expr}

[ASC | DESC] [,col_name ...]]

[SEPARATOR str_val])

mysql> SELECT student_name,
->     GROUP_CONCAT(test_score)
->     FROM student
->     GROUP BY student_name;
Or:
mysql> SELECT student_name,
->     GROUP_CONCAT(DISTINCT test_score
->     ORDER BY test_score DESC SEPARATOR ' ')
->     FROM student
->     GROUP BY student_name;
如产品询问单使用方法:
产品表 : products
ProID : 产品编号
ProName : 产品名称
订单表 : orders
OrderID : 订单编号
orderTime: 订单时间
索引表: indexs
OrderID  订单号
ProID   产品号
-----------------------
CREATE TABLE `products` (
`ProID` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`ProName` VARCHAR( 255 ) NOT NULL
) ENGINE = MYISAM ;
CREATE TABLE `orders` (
`OrderID` INT UNSIGNED NOT NULL AUTO_INCREMENT
         PRIMARY KEY ,
`RequestTime` DATETIME NOT NULL
) ENGINE = MYISAM ;
CREATE TABLE `indexs` (
`OrderID` INT UNSIGNED NOT NULL DEFAULT '0',
`ProID` INT UNSIGNED NOT NULL DEFAULT '0',
        INDEX ( `OrderID` , `ProID` )
) ENGINE = MYISAM ;
INSERT INTO `products` ( `ProID` , `ProName` )
 VALUES (1, 'pro1'), (2 , 'pro2'),(3 ,'pro3');
INSERT INTO `orders` ( `OrderID` , `RequestTime` )
VALUES (1, '0000-00-00 00:00:00' ),
       (2, '0000-00-00 00:00:00');
UNSERT INTO `indexs` ( `OrderID` , `ProID` )
VALUES ('1', '1'), ('1', '2'),( '2','1'),( '2','3'); 

--------------------- 

SELECT
      GROUP_CONCAT(
     products.ProName ORDER BY products.ProName SEPARATOR ',' )
 AS ProNames
FROM `indexs` , orders, products
WHERE indexs.OrderID = orders.OrderID
               AND indexs.ProID = products.ProID
GROUP BY orders.OrderID
结果为 ProNames
pro1,pro2

pro1,pro3
		

					
标签: