1) Retrieves all customers with or without orders. For example, you want to determine the amount ordered by each customer and you need to see who has not ordered anything as well. The data you retrieve may include CompanyName, ContactName, OrderDate, ShipVia, Freight, ShipName, ShipAddress
2) Retrieves all orders with or without matching customer records. For example, you want to determine if there are any orders in the data with undefined CustomerID values. The data you retrieve may include CompanyName, ContactName, OrderDate, ShipVia, Freight, ShipName, ShipAddress.
3) Retrieves customers with orders only. For example, you want to determine the amount ordered by each customer and you only want to see those who have ordered something. The data you retrieve may include CompanyName, ContactName, OrderDate, ShipVia, Freight, ShipName, ShipAddress.
4) Retrieves customers with orders only. For example, you want to determine the amount ordered by each customer and you only want to see those who have ordered something. Divide the result into groups by company name and add all the freight for each group. In addition, you need to sort the result to find who owns the biggest order. The data you retrieve may include CompanyName and Freight.
2) Retrieves all orders with or without matching customer records. For example, you want to determine if there are any orders in the data with undefined CustomerID values. The data you retrieve may include CompanyName, ContactName, OrderDate, ShipVia, Freight, ShipName, ShipAddress.
3) Retrieves customers with orders only. For example, you want to determine the amount ordered by each customer and you only want to see those who have ordered something. The data you retrieve may include CompanyName, ContactName, OrderDate, ShipVia, Freight, ShipName, ShipAddress.
4) Retrieves customers with orders only. For example, you want to determine the amount ordered by each customer and you only want to see those who have ordered something. Divide the result into groups by company name and add all the freight for each group. In addition, you need to sort the result to find who owns the biggest order. The data you retrieve may include CompanyName and Freight.
select * from customers where customerid not in (select customerid from orders)
有订单的客户
select CompanyName, ContactName, OrderDate, ShipVia, Freight, ShipName, ShipAddress
from customers join orders on customers.customerid = orders.customerid
也就是说left join包含了join条件的全部列,和customers表中没有满足customers.customerid = orders.customerid条件的那些列。
select * from orders where customerid is null
有客户的订单
select CompanyName, ContactName, OrderDate, ShipVia, Freight, ShipName, ShipAddress from orders join customers on orders.customerid = customers.customerid
select CompanyName, ContactName, OrderDate, ShipVia, Freight, ShipName, ShipAddress
from customers join orders on customers.customerid = orders.customerid
select CompanyName, sum(Freight) as num
from customers join orders on customers.customerid = orders.customerid
group by CompanyName order by num desc
我们一起创业,我们一起发财,我们一起快乐,我们一起努力!