php - 你能在 mysql 中对不同的列进行分组和计数吗？

Question

用户有一个列表，其中包含不同类别的项目。我想知道用户在每个类别中有多少项。

User    |   item  | category____________________________Bill    |   car   | newBill    |   truck | usedBill    |   boat  | newBill    |   house | oldSue     |   car   | usedSue     |   truck | usedBill    |   bike  | newSue     |   viola | used

I'd like the results

User category__________________________Bill|  new   |  3Bill|  used  |  1Bill|  sold  |  1

I've tried

$SQL = "SELECT COUNT(DISTINCT CATEGORY) FROM LIST WHERE USER=Bill";

但这并没有让我走得太远。

谢谢

Answer 1

你需要使用group by语句:

select user, category, count(distinct item)
from mytable
where user = 'Bill'
group by user, category