SQL练习题--某书第四章子查询

下面着重讲一下 相关子查询

应用1：

为什么说相关呢，因为两个表是有连接关系在的，比如

SQL练习题--某书第四章子查询

这里注意对于O1的每一个orderid，我在O2里面保证首先orderid一样，然后寻找满足条件后的MAX （orderid）

所以这个查询是：返回每个客户最大订单ID的订单状况

延伸：

SQL练习题--某书第四章子查询

返回每个订单当前订单值占该客户（体现：o1.custid=o2.custid）所有订单值的占百分比

应用2：上一个（返回小于当前值的最大值）和下一个（大于当前值的最小值）

返回小于当前值的最大值

SQL练习题--某书第四章子查询

大于当前值的最小值

SQL练习题--某书第四章子查询

应用3：逐行累加：聚合

SQL练习题--某书第四章子查询

###################################################课后习题

SQL练习题--某书第四章子查询

#1. 返回orders表中可以查到的活动最后一天所下的所有订单 
SELECT so.orderid,so.orderdate,so.custid,so.empid
  FROM Sales.Orders AS so
 WHERE so.orderdate = (
                        SELECT MAX(orderdate) 
                          FROM Sales.Orders);
                          
#2. 返回订单数量最多的客户的所有订单。注意,在一个以上的客户可能具有相同数量的订单 
SELECT so.custid,so.orderid,so.orderdate,so.empid
  FROM Sales.Orders AS so
 WHERE so.custid IN (
						SELECT tempt1.custid
                          FROM(
                               SELECT custid,COUNT(orderid) AS number 
                                 FROM Sales.Orders
								GROUP BY custid) AS tempt1
                        WHERE tempt1.number =(
                        SELECT MAX(tempt.number)
                          FROM(
                               SELECT custid,COUNT(orderid) AS number 
                                 FROM Sales.Orders
								GROUP BY custid) AS tempt
						)
                         );
#3.返回2008年5月1日或之后没有下订单的雇员 
SELECT he.empid,he.Firstname,he.lastname
  FROM HR.Employees AS he
 WHERE he.empid NOT IN (
                        SELECT s.empid
                          FROM Sales.Orders
						 WHERE s.orderdate >= '20180501');

#4.返回有客户但是没有雇员的国家alter
SELECT DISTINCT sc.country
  FROM Sales.Customers AS sc
 WHERE sc.country NOT IN (
							SELECT he.country
                              FROM HR.Employees);
                              
#5.返回每个客户活动最后一天下的所有订单 
SELECT so.custid,so.orderid,so.orderdate,so.empid
  FROM Sales.Orders AS so
 WHERE so.orderdate = (SELECT MAX(so2.orderdate) 
                         FROM Sales.Orders AS so2
						WHERE so2.custid = so.custid);
                        
#6.返回2007年下订单但是2008年没下订单的客户 
SELECT sc.custid,sc.companyname
  FROM Sales.Customers AS sc
 WHERE sc.orderid IN (SELECT orderid
						FROM Sales.Orders
					   WHERE orderdate between ('20070101','20071230')
                         AND orderdate NOT IN ('20080101','20081230')
                          );    
# 答案做法 
#7.返回订购了产品12的客户 
SELECT sc.custid,sc.companyname
  FROM Sales.Customers AS sc
  JOIN Sales.Orders AS so
    ON sc.custid = so.custid
  JOIN Sales.OrderDetails AS sod
    ON so.orderid = sod.orderid
  WHERE sod.productid = 12;
  
# 答案做法：使用exist子查询 
SELECT sc.custid,sc.companyname
  FROM Sales.Customers AS sc
  WHERE EXISTS（SELECT * FROM Sales.Orders so
               WHERE sc.custid = so.custid
                 AND EXISTS(
                 SELECT * FROM Sales.OrderDetails AS sod 
                  WHERE so.orderid = sod.orderid
					AND sod.productid = 12));
                    
#8.查询每个客户及其月度的采购总量 
# 答案做法 

SELECT O1.custid,O1.ordermonth,O1.qty,(
                              SELECT SUM(O2.qty)
								FROM Sales.CustOrders AS O2
							   WHERE O2.custid = O1.custid
                                 AND O2.ordermonth <= O1.ordermonth) AS runqty
  FROM Sales.Customers AS O1
 ORDER BY custid,ordermonth;
  
  
  
#################自己反正做错啦 

SELECT tempt.custid,tempt.ordermonth,tempt.qty,
       (SELECT SUM(tempt2.qty) FROM Sales.Customers AS O2
                                      WHERE tempt2.ordermonth <= (
																SELECT MIN(tempt2.ordermonth) FROM tempt2
																WHERE tempt2.ordermonth > tempt.ordermonth)
  FROM
	FROM Sales.Customers AS O1

重点：7，8

SQL练习题--某书第四章子查询

相关推荐