mysql - 使用内连接从三个不同的表中选择内容

Question

嗯，我有三个表:

购物车:

ID | Product | Quantity
1    S1FG      3
1    C0K0      1
1    SLM1      6
2    S1FG      2
2    S94A      2

产品:

Code | Price
S1FG   10.00
C0K0   8.00
SLM1   19.50
S94A   2.00

和用户:

ID | Nickname
1    Mark
2    Steve

我想要这个:

Nickname | Products | Total
Mark       10         155.00
Steve      4          24.00

换句话说，我想要一个购物车中有商品的用户列表、他们的商品数量以及商品价格总和。

我尝试过这个:

SELECT DISTINCT b.Nickname, SUM(a.Quantity) as Products, SUM(a.Quantity*c.Price) as Total 
FROM Cart a 
INNER JOIN Users b ON (a.ID = b.ID) 
INNER JOIN Products c ON (a.Product = c.Code)

但是没有成功...

我能做什么？

Answer 1

您需要对昵称进行GROUP BY，否则您只会得到一行:

SELECT DISTINCT b.Nickname, SUM(a.Quantity) as Products, SUM(a.Quantity*c.Price) as Total 
FROM Cart a 
INNER JOIN Users b ON (a.ID = b.ID) 
INNER JOIN Products c ON (a.Product = c.Code)
GROUP BY b.Nickname