连接多个MySQL列和行

I am currently working on MySQL and am having trouble with combining data. I don't want to do this in the external layer.

我目前正在研究MySQL，并且在组合数据方面遇到了麻烦。我不想在外部层中这样做。

Basically, I have an item table. The table structure looks like this:

基本上，我有一个项目表。表结构如下所示：

=========================
 id    item_name   stock
=========================
 1     Soap        100
 2     Plate       89
 3     Scissor     72
=========================

I know, I could use MySQL GROUP_CONCAT() function:

我知道，我可以使用MySQL GROUP_CONCAT（）函数：

SELECT GROUP_CONCAT(item_name SEPARATOR  ', ') AS items FROM item WHERE id IN (1, 2, 3);

With results:

结果如下：

======================
 items
======================
 Soap, Plate, Scissor
======================

But, the desired result is:

但是，期望的结果是：

 Soap (100), Plate (89), Scissor (72)

2 个解决方案

#1

You can also do this using GROUP_CONCAT(), just constructing the each element from the item_name and stock before the aggregation:

您也可以使用GROUP_CONCAT（）执行此操作，只需在聚合之前构建item_name和stock中的每个元素：

SELECT GROUP_CONCAT(item_name, ' (', stock, ')' SEPARATOR  ', ') AS items
FROM item
WHERE id IN (1, 2, 3);

A little known fact is that GROUP_CONCAT() takes multiple arguments. You could also write this as:

一个鲜为人知的事实是GROUP_CONCAT（）接受多个参数。你也可以这样写：

SELECT GROUP_CONCAT(CONCAT(item_name, ' (', stock, ')') SEPARATOR  ', '
                   ) AS items
FROM item
WHERE id IN (1, 2, 3);

#2

Use GROUP BY:

使用GROUP BY：

select item_name, count(*) from item group by item_name

从item_name按item_name选择item_name，count（*）

You should understand Aggregate statements (the count(*)) and grouping in SQL:

您应该了解聚合语句（count（*））和SQL中的分组：

http://www.w3schools.com/sql/sql_groupby.asp

#1