mysql - 如何选择表和统计其他表

Question

假设我有两个表:

客户

  Name   |   id   |
-------------------
  Benny  |    1   |
  Wilson |    2   |
  Joe    |    3   |
  Austin |    4   |

订单

  Product  |   id   |
---------------------
  TV       |    1   |
  Hifi-set |    1   |
  HTPC     |    1   |
  CD       |    1   |
  DVD      |    1   |
  CD       |    1   |
  DVD      |    1   |

这就是我想要的结果:

  Name   | Orders |
-------------------
  Benny  |    7   |
  Wilson |    0   |
  Joe    |    0   |
  Austin |    0   |

我不熟悉SQL，但我试过:

SELECT c.Name FROM Customers AS c LEFT JOIN Orders AS o ON c.id=o.id GROUP BY c.Name

但是得到了一个错误的结果:

  Name   | Orders |
-------------------
  Benny  |    4   |
  Wilson |    1   |
  Joe    |    1   |
  Austin |    1   |

我该怎么办？

Answer 1

尝试:

select 
   c.Name, 
   (select count(1) from Orders where ID=c.ID) 
from 
   Customers as c