多表mysql查询

Question

我有4张桌子

Table: Category
    CategoryID (int)
    Name (varchar)
Table: Products
    ProductID (int)
    CategoryID (int)
    Name (varchar)
    Description (text)
Table: Sales
    SalesID (int)
    ProductID (int)
Table: Links
    LinkID (int)
    ProductID (int)

现在我需要将数据显示为：

CategoryName     Total Products     Total Sales     Total Links
    ABC                5                 12            50
    XYZ               12                 26            10

我如何才能做到这一点，可能是在单个查询中

感谢您的帮助

谢谢

Answer 1

  SELECT CategoryName, Count(distinct p.ProductId) TotalProducts, Count(distinct s.SalesId) TotalSales, 
        COUNT(distinct l.LinkId) TotalLinks
  FROM Products p JOIN SALES s on p.ProductId = s.ProductId
       JOIN Categories c ON c.CategoryId = p.CategoryId
       JOIN Links l ON p.ProductId = l.LinkId
  GROUP BY CategoryName

Answer 2

SELECT
       CAT.Name CategoryName,
       (SELECT COUNT(P.ProductsID) FROM Products P WHERE P.CategoryID=CAT.CategoryID) TotalProducts,
       (SELECT COUNT(S.SalesID) FROM Sales S JOIN Products P ON S.ProductID=P.ProductID WHERE P.CategoryID=CAT.CategoryID) TotalSales,
       (SELECT COUNT(L.LinkID) FROM Links L JOIN Products P ON L.ProductID=P.ProductID WHERE P.CategoryID=CAT.CategoryID) TotalLinks
FROM 
       CATEGORY CAT

Answer 3

select
    c.CategoryId,
    c.name as CategoryName,
    count(p.ProductId) as TotalProducts,
    (select count(s.salesid) from sales s where s.ProductId = p.ProductId) as TotalSales,
    (select count(l.linkid) from products l where l.ProductId = p.ProductId) as TotalLinks
from
  Category c
  left join Products p on p.CategoryId = c.CategoryId
group by
  c.CategoryId,
  c.Name