mysql联合查询问题

[amu1433]

"Persons" 表：
Id_P LastName FirstName Address City
1 Adams John Oxford Street London
2 Bush George Fifth Avenue New York
3 Carter homas Changan Street Beijing


"Orders" 表：
Id_O OrderNo Id_P
1 77895 3
2 44678 3
3 22456 1
4 24562 1
5 34764 65


怎么查询Persons表id=1 对应Orders的数量

就是
Id_P(1) Adams 2
Id_P(2) Bush 0
Id_P(3) Carter 2

[ACMAIN_CHM]

select Id_P ,LastName,(select count(*) from Orders where Id_P=Persons.Id_P) from Persons

[shane_____]

这个很简单啊，因为你的 Orders表中有Persons的外键，所以你直接去Orders表中查就可以了，以下是我写的语句你可以参考以下 SELECT SUM(ORDERNO) FROM ORDERS GROUP BY ID_P HAVING ID_P=1 如果想一起交流，欢迎加我哦！^_^

[道玄希言]

select 
  a.Id_P, a.LastName, IFNULL(b.num, 0) as num
from Persons
left join
(select Id_P, count(Id_p) as num 
   from Orders group by Id_p) as b
ON a.Id_P =b.Id_P

[ccchallenge]

貌似 ACMAIN_CHM 的速度快。

[allen519]

select count(*) , p.id_p, p.last_name from orders o join persons p on o.id_p=p.id_p group by id_p;

[侯爵123]

select (select count(*) from orders o where o.Id_P=p.Id_P) ordersCount,p.xx,p.xx from persons p;