mysql - 在 MySQL 中选择 2 个相关表

Question

我有这两张表:

表用户:

id | name
---------
1  | Joe
2  | Sara
3  | Michael

表格销售:

id |  product  | user_id
------------------------
1  |    Car    | 2
2  |   Truck   | 3
3  | motorcycle| 1
4  |    Car    | 2
5  |   Truck   | 1
6  |    Car    | 3
7  |    Car    | 2
8  |   Truck   | 3
9  |    Car    | 2
10 |    Car    | 3

我想要以下内容:

User Name  | Car | Truck | Motorcycle
Joe        |  0  |   1   |     1
Sara       |  4  |   0   |     0
Michael    |  2  |   2   |     0    

如有任何帮助，我们将不胜感激

Answer 1

--对于少数产品来说这有效，

Select username
, Sum(case when product = 'Car' then 1 else 0 End) Cars
, Sum(case when product = 'Truck' then 1 else 0 End) Truck
, Sum(case when product = 'motorcycle' then 1 else 0 End) motorcycle
From Sales s join users u on s.userid = u.userid
group by username