GROUP_CONCAT([DISTINCT] expr [,expr ...]
[ORDER BY {unsigned_integer | col_name | expr}
[ASC | DESC] [,col_name ...]]
[[SEPARATOR str_val])
*SEPARATOR是要用什麼為分割字
參考資料
例:
資料建立一個訂單明細的資料表。
CREATE TABLE products_table (
order_id bigint(20) NOT NULL default '0' COMMENT '關連訂單id',
product_name varchar(255) NOT NULL default '' COMMENT '產品名稱',
price bigint(20) NOT NULL default '0' COMMENT '價格'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO products_table(order_id,product_name,price)
VALUES
(1,"商品1",10),
(2,"商品1",10),
(3,"商品1",10),
(1,"商品2",20),
(1,"商品3",30),
(2,"商品4",40),
(2,"商品5",50),
(3,"商品6",60);
查詢語法1:(使用預設的)
SELECT p.order_id,
GROUP_CONCAT(p.product_name ) as '訂單產品明細'
FROM products_table AS p
GROUP BY p.order_id;
結果:
查詢語法2:(可修改設定,排序、分割子元。)
SELECT p.order_id,
GROUP_CONCAT(DISTINCT p.product_name
ORDER BY p.product_name DESC SEPARATOR ' ') as '訂單產品明細'
FROM products_table AS p
GROUP BY p.order_id;
結果:
留言列表