包含3个表的sql计数

7rfyedvj 于 2021-06-20 发布在 Mysql

关注(0)|答案(4)|浏览(246)

我需要一个sql选择混合3个表的内容的帮助，这些表是：

Clients
client_id   | name    | lastname
1           | Johan   | Doe
2           | Monique | Patricks

Orders
id_order  | client_id 
1001      | 1         
1002      | 1         
1003      | 2         

Order_lines
id_oline | id_order | id_product
1        | 1001     | 13
2        | 1001     | 21
3        | 1001     | 312
4        | 1002     | 33
5        | 1003     | 41
5        | 1003     | 11

我可以做所有简单的查询，但我需要一个稍微困难一点的，这给了我想要的结果：

QUERY RESULT
client_id  | name     | lastname    | Num_products (Count rows)
1          | Johan    | Doe         | 4
2          | Monique  | Patricks    | 2

我不知道´我甚至不知道从哪里开始。。。任何帮助都将是宝贵的

sql mysql count

来源：https://stackoverflow.com/questions/50728418/sql-count-with-3-tables

4条答案

按热度按时间

jyztefdp1#

SELECT C.client_id,name, lastname,COUNT(OL.id_order) as num_products FROM  Orders AS O 
INNER JOIN Order_lines AS OL ON O.id_order=OL.id_order 
INNER JOIN Clients AS C ON C.client_id=O.client_id
GROUP BY OL.id_order

赞(0）回复(0）举报 2021-06-20

cld4siwp2#

为此，使用group by子句进行聚合：

SELECT client_id, name, lastname, count(order_lines.id_oline) as Num_Products
FROM Clients
    INNER JOIN Orders on clients.client_id = orders.order_id
    INNER JOIN Order_Lines on Orders.id_order = Order_Lines.id_order
GROUP BY client_id, name, lastname

赞(0）回复(0）举报 2021-06-20

ovfsdjhp3#

这段代码完全没有经过测试，但它应该完全按照您的需要来做。我建议大家阅读分组发言。
希望这有帮助！

SELECT c.client_id, c.name, c.lastname, COUNT(*)
FROM Clients AS c
    INNER JOIN Orders AS o ON c.client_id = o.client_id
    INNER JOIN Order_lines AS ol ON o.id_order = ol.id_order
GROUP BY c.client_id, c.name, c.lastname;

赞(0）回复(0）举报 2021-06-20

omtl5h9j4#

http://sqlfiddle.com/#!9/af49f1/1

SELECT c.client_id, c.name, c.lastname, 
       count(DISTINCT order_lines.id_product) as Num_Products
FROM Clients c
LEFT JOIN Orders o
ON c.client_id = o.client_id
LEFT JOIN Order_Lines 
ON o.id_order = Order_Lines.id_order
GROUP BY c.client_id

赞(0）回复(0）举报 2021-06-20

我来回答

包含3个表的sql计数

4条答案

相关问题

热门标签

最新问答