MySQL计数为行为一

Question

我有一个如下所示的数据库。

mysql> select * from wants where itemname='lamp' order by location;
+------+--------------------+-------------+----------+----------+----------+
| sess | username           | room        | image    | item     | priority |
+------+--------------------+-------------+----------+----------+----------+
|   33 | user1@aol.com      | Family Room | DSC00649 | Lamp     |        1 |
|  235 | user2@yahoo.com    | Family Room | DSC00649 | Lamp     |        2 |
|   60 | user3@homtmail.com | Foyer       | DSC00527 | Lamp     |        1 |
|  197 | user4@gmail.com    | Foyer       | DSC00527 | Lamp     |        2 |
|  189 | user4@gmail.com    | Living Room | DSC00827 | Lamp     |        1 |
|  273 | user5@live.com     | Living Room | DSC00827 | Lamp     |        2 |
+------+--------------------+-------------+----------+----------+----------+
6 rows in set (0.00 sec)

我想做的是每一个房间都要清点每件物品。

当我运行查询时，应该如下所示：

2 Family Room Lamp user1@aol.com user2@yahoo.com 
2 Foyer Lamp user3@homtmail.com user4@gmail.com
2 Living Room Lamp user4@gmail.com user5@live.com

当前查询：

$result1 = mysql_query("SELECT username, location, image, itemname, COUNT( itemname ) x  FROM wants GROUP BY itemname HAVING x >1 order by location ASC")

这将提供以下输出：

6 Family Room Lamp luser1@aol.com user2@yahoo.com user3@homtmail.com user4@gmail.com user4@gmail.com user5@live.com

我试图使用Concat来获取前两行的计数，但这没有帮助，

在项目名称相同的情况下，我如何才能把这些内容分开阅读呢？

Answer 1

你需要group by的房间，而不是项目。此外，听起来您还在寻找group_concat来列出用户：

select count(*), 
       room,
       group_concat(username)
from wants
where item ='lamp'
group by room

• SQL Fiddle演示