mysql必知必会 第12章

1．编写SQL语句，返回Customers表中的顾客名称（cust_name）和Orders表中的相关订单号（order_num），并按顾客名称再按订单号对结果进行排序。实际上是尝试两次，一次使用简单的等联结语法，一次使用INNERJOIN。

select cust_name,order_num
from customers,orders
where customers.cust_id=orders.cust_id
order by cust_name;

select cust_name,order_num
from customers
inner join orders
on customers.cust_id=orders.cust_id
order by cust_name;

上面是标准答案，下面是我自己的学习
因为我现在看到的大部分查询都是在from和where后面连接，但是也有在select后面插入查询语句的，我不知道两者区别，这里就是一个实验。

select cust_name,(
select order_num 
from orders
where orders.cust_id=customers.cust_id) 
as o
from customers
order by cust_name;

这个是查询语句是错误的，返回错误是“ERROR 1242 (21000): Subquery returns more than 1 row”。
错误在于，子查询返回的两个结果，同时对应主查询返回结果的某行两次，但是主查询不能重复，就显示错误，可以在子查询语句中’limit 1’来限定子查询结果，但是这样就显示不准确了。

下面是查询语句的正解

select (
select cust_name from customers
where orders.cust_id=customers.cust_id)
as name,order_num
from orders
order by name;

2．我们来让上一题变得更有用些。除了返回顾客名称和订单号，添加第三列OrderTotal，其中包含每个订单的总价。有两种方法可以执行此操作：使用OrderItems表的子查询来创建OrderTotal列，或者将OrderItems表与现有表联结并使用聚合函数。提示：请注意需要使用完全限定列名的地方。

select orders.order_num,customers.cust_name,
sum(orderitems.quantity*orderitems.item_price) OrderTotal
from customers,orders,orderitems
where customers.cust_id=orders.cust_id and 
orders.order_num = orderitems.order_num
group by order_num;

select distinct orders.order_num,customers.cust_name,(
select sum(orderitems.quantity*orderitems.item_price)
from orderitems
where orders.order_num = orderitems.order_num
group by order_num
) as OrderTotal
from customers,orders,orderitems
where customers.cust_id=orders.cust_id; 

注意这个，distinct 不写的话会重复

3．我们重新看一下第11课的挑战题2。编写SQL语句，检索订购产品BR01的日期，这一次使用联结和简单的等联结语法。输出应该与第11课的输出相同。

select order_date,cust_id
from orderitems,orders
where orderitems.order_num=orders.order_num
and prod_id='BR01'
order by order_date;

4．很有趣，我们再试一次。重新创建为第11课挑战题3编写的SQL语句，这次使用ANSI的INNER JOIN语法。在之前编写的代码中使用了两个嵌套的子查询。要重新创建它，需要两个INNER JOIN语句，每个语句的格式类似于本课讲到的INNER JOIN示例，而且不要忘记WHERE子句可以通过prod_id进行过滤。

select customers.cust_id,cust_name,cust_email
from orderitems inner join orders on orderitems.order_num=orders.order_num
inner join customers on orders.cust_id=customers.cust_id
where orderitems.prod_id='BR01';

5．再让事情变得更加有趣些，我们将混合使用联结、聚合函数和分组。准备好了吗？回到第10课，当时的挑战是要求查找值等于或大于1000的所有订单号。这些结果很有用，但更有用的是订单数量至少达到这个数的顾客名称。因此，编写SQL语句，使用联结从Customers表返回顾客名称（cust_name），并从OrderItems表返回所有订单的总价。
提示：要联结这些表，还需要包括Orders表（因为Customers表与OrderItems表不直接相关，Customers表与Orders表相关，而Orders表与OrderItems表相关）。不要忘记GROUP BY和HAVING，并按顾客名称对结果进行排序。你可以使用简单的等联结或ANSI的INNER JOIN语法。或者，如果你很勇敢，请尝试使用两种方式编写。

SELECT cust_name, SUM(item_price*quantity) AS total_price
FROM Customers, Orders, OrderItems
WHERE Customers.cust_id = Orders.cust_id
AND Orders.order_num = OrderItems.order_num
GROUP BY cust_name HAVING SUM(item_price*quantity) >= 1000
ORDER BY cust_name;

---上面是标准答案，下面是我自己写的
select c.cust_id,c.cust_name
from customers as c,orders as os
where os.cust_id=c.cust_id
and os.order_num in(
select order_num
from orderitems
group by order_num
having sum(quantity*item_price)>=1000
);