mysql计算每个用户的书数并在每一行中显示

Question

例如我的表格如下:

user_id | book     | 
------------------
 1      | aaaa     |
 1      | bbbb     | 
 2      | aaaa     |
 3      | cccc     |

现在我想要选择每行中所有用户书籍总和的数据

user_id | book     | count(book)
---------------------------------
 1      | aaaa     | 2
 1      | bbbb     | 2
 2      | aaaa     | 1
 3      | cccc     | 1

有人可以帮助我吗？

Answer 1

使用子选择来做到这一点

select user_id, book, (select count(1) from table as table2 where user_id = table.user_id ) as count_book
form table