sql - MySQL 多表计数查询

Question

我有3张 table

products
    productid (int)
    name (varchar)
    price (float)

sales
    salesid (int)
    productid (int)
    time (datetime)

links
    linkid (int)
    productid (int)
    link (text)

现在我需要一个可以显示为的查询

ProductID      ProductName      Sales      Links
    1           ABC               10        8
    2           XYZ               15       12

我需要特定产品的所有销售计数和所有链接计数

我怎样才能做到这一点？

谢谢

Answer 1

SELECT p.productid, p.pname AS ProductName, 
       Count(DISTINCT s.salesid) as Sales, Count(DISTINCT l.linkid) as Links
 FROM products p
   LEFT JOIN sales s ON p.productid=s.productid
   LEFT JOIN links l ON p.products=l.productid 
GROUP BY p.productid