问题描述

SQL新手求教一个关于数据库的问题，着急！在线等！各位大牛们，感激不尽！

实习生，TeamLeader给了那么一个数据库题目：
给了如下两张独立的表：
Person

Orders

要求从这两张表中选出以下结果：

ItemCount是购买的总数！
求救！在线等！

解决方案

 select t.Id_p,t.LastName,COUNT(OrderNo) as ItemCount from
(
select t1.OrderNo,ISNULL(t1.Id_p,t2.Id_p) as Id_p,isnull(t2.LastName,'Unknown') as LastName from Orders t1
full join  Person t2
on t1.Id_p = t2.Id_p
) t group by t.Id_p,t.LastName

解决方案二：

ItemCount是购买的总数

解决方案三：

SELECT Person.*,(SELECT COUNT(*) FROM Orders WHERE Orders.ID_P=PERSON.ID_P) ItemCount from Person

解决方案四：

这个应该可以:

 SELECT p.lastname, o.ItemCount
from Person p
left join
(
SELECT id_p ,COUNT(*) as ItemCount FROM Orders group by id_p)
)o
 on o.ID_P=p.ID_P

解决方案五：

修改了一下：

SELECT isnull(p.lastname,'Unknow') lastname,
o.ItemCount
from Person p
full join
(
SELECT id_p ,COUNT(*) as ItemCount FROM Orders group by id_p)
)o
on o.ID_P=p.ID_P