数据库实验5.2详解（仅供校友参考）

同样的，我们先读题，题目非常简短，要求我们计算每一位顾客的订单数，那么我们自然会想到去count一下表shoporder中的ordid。有思路后，那我们就直接开始来答题。

解：

然后我们发现csurname被标了红色下曲线。

观察一下数据库关系图（直接在sql server中建立），原来csurname是在表custome中，那么我们自然会想到要对两表进行连接。

select customer.custid,csurname,count(ordid)“total orders”
from shoporder,customer
where shoporder.custid=customer.custid
group by customer.custid,csurname;

查询结果如下图：

外连接做法

和原题目对照一下，发现我们还是有些不一样是不是，有看上期讲解(5.1)的同学应该就想到了，使用外连接就可以处理这个问题，我们写一下：

select customer.custid,csurname,count(ordid)“total orders”
from shoporder right outer join customer on(shoporder.custid=customer.custid )
group by customer.custid,csurname;

派生表做法

虽然我们这样已经用右外连接完美解决这道题了，但我们这期讲解总不能没有新内容是吧。下面我介绍一种基于派生表查询的方法来完成这道题。

1.首先，我们先把表shoporder中的全部数据选出来

select
custid,COUNT(shoporder.ordid) “total orders”
from shoporder
group by custid

2.再把customer中的需要的数据选出来，

即把custid不在shoporder中却在customer中的数据给选出来

select
custid,0 “total orders”
from customer
group by custid
having custid not in (select custid from shoporder)

3.然后再把他们进行一下union，即

select
custid,COUNT(shoporder.ordid) “total orders”
from shoporder
group by custid
union
select
custid,0 “total orders”
from customer
group by custid
having custid not in (select custid from shoporder)

4.然后我们把这个union好的表写进from中，即作为派生表，命名为a

select a.custid,“total orders”
from (
select
custid,COUNT(shoporder.ordid) “total orders”
from shoporder
group by custid
union
select
custid,0 “total orders”
from customer
group by custid
having custid not in (select custid from shoporder)
) as a;

先查询一下看看，可以看到，我们已经大致实现了我们的想法，但还少查了csurname。

5.增加列csurname，我们直接利用custid连接customer表和a表(派生表)即可。

select a.custid,csurname,“total orders”
from customer,(
select
custid,COUNT(shoporder.ordid) “total orders”
from shoporder
group by custid
union
select
custid,0 “total orders”
from customer
group by custid
having custid not in (select custid from shoporder)
) as a
where customer.custid=a.custid;

最终查询结果如下图：

那么本期讲解就到这里了，我们下期再见！

case when做法

其实使用case when也可以做这道题，不过我写得有些乱，看上去逻辑没有很清晰，有兴趣的同学的自行了解一下
select
customer.custid,csurname,case when customer.custid not in (select
custid from shoporder) then 0 else COUNT(shoporder.ordid) end “total
orders” from shoporder,customer where (case when customer.custid not
in(select custid from shoporder) then 1 when
customer.custid=shoporder.custid then 1 end )=1 group by
customer.custid,csurname order by customer.custid;