SQL练习题--某书第三章联结

20190422更新要注意6和7题后面举例说明了内联结外联结还有and和where的不同效果 

 

#1. 为每个雇员行生成5个副本 
SELECT E.empid,E.firstname,E.lastname,N.n
  FROM HR.Employees AS E
 CROSS JOIN dbo.Nums AS N
 WHERE N.n <=5
 ORDER BY n,empid;
 
#2. 返回美国客户，并为每个客户返回订单总数的总数量->

SELECT c.custid,
	   COUNT(DISTINCT o.orderid) AS numorders
       SUM(d.qty) AS totalqty
  FROM Sales.Customers c
  JOIN Sales.Orders o
    ON c.custid = o.custid
  JOIN Sales.OrderDetails d
    ON o.orderid = d.orderid
 WHERE c.country = 'USA'
  GROUP BY c.custid;
  
#3. 返回客户及其订单 包括没有下订单的客户 
SELECT c.customers，
       c.companyname,
	   o.orderid,
       o.orderdate
  FROM Sales.Customers c
  LEFT JOIN Sales.Orders o
    ON c.custid = o.custid;

#4. 返回没有下订单的客户 
SELECT c.customers，
       c.companyname
  FROM Sales.Customers c
  LEFT JOIN Sales.Orders o
    ON c.custid = o.custid
 WHERE o.orderid IS NULL;
 
#5. 返回2007年2月12日下订单的客户以及他们的订单alter
SELECT c.customers，
       c.companyname,
	   o.orderid,
       o.orderdate
  FROM Sales.Customers c
  JOIN Sales.Orders o
    ON c.custid = o.custid
  WHERE o.orderdate = '2007-02-12'
  
#6. 返回20070212下订单的客户订单还有没下订单的客户订单

SELECT c.customers，
       c.companyname,
	   o.orderid,
       o.orderdate
  FROM Sales.Customers c
  JOIN Sales.Orders o
    ON c.custid = o.custid
  WHERE o.orderdate = '2007-02-12'
UNOIN
SELECT c.customers，
       c.companyname,
	   o.orderid,
       o.orderdate
  FROM Sales.Customers c
  LEFT JOIN Sales.Orders o
    ON c.custid = o.custid
 WHERE o.orderid IS NULL;
 
 ############答案的做法(把订单日期看做是匹配条件！)
 ############这个时候比较麻烦的是当客户没有订单时在第二个表的日期是NULL，
 ############所以要么用ON+日期（首选），要么就两个表合并
 SELECT c.custid,c.companyname,o.orderid,o.orderdate
   FROM Sales.Customers AS c
   LEFT JOIN Sales.Orders AS o
     ON o.custid = c.custid
	AND o.orderdate = '20070212'
    
# 7.还是要用 
SELECT c.customers，
       c.companyname,
       CASE WHEN o.orderid IS NULL THEN 'NO'
       ELSE 'YES'
        END AS HashOrderOn70212
  FROM Sales.Customers c
  LEFT JOIN Sales.Orders o
    ON c.custid = o.custid
    AND o.orderdate = '2007-02-12'

重点：第6+7题

CREATE TABLE SalesCustomers
(
custid        INT            NOT NULL PRIMARY KEY,
comapanyname  VARCHAR(50)    NOT NULL
);

CREATE TABLE SalesOrders
(
custid        INT            NOT NULL PRIMARY KEY,
orderid       VARCHAR(50)    NOT NULL,
orderdate      DATE          NOT NULL
);


INSERT INTO SalesCustomers VALUES(1,'Linda');
INSERT INTO SalesCustomers VALUES(2,'Mary');
INSERT INTO SalesCustomers VALUES(3,'Kylin');
INSERT INTO SalesCustomers VALUES(4,'kally');
INSERT INTO SalesCustomers VALUES(5,'Leslie');
INSERT INTO SalesCustomers VALUES(6,'Hanmeu');
INSERT INTO SalesCustomers VALUES(7,'Hua');
INSERT INTO SalesCustomers VALUES(8,'Gem');


INSERT INTO SalesOrders VALUES(1,'1001','2008-01-01');
INSERT INTO SalesOrders VALUES(2,'1002','2008-02-01');
INSERT INTO SalesOrders VALUES(3,'1003','2008-01-01');
INSERT INTO SalesOrders VALUES(4,'1004','2008-02-01');
INSERT INTO SalesOrders VALUES(5,'1005','2008-01-01');

#看看不同的join
SELECT sc.custid,sc.comapanyname,so.orderid,so.orderdate
  FROM SalesCustomers AS sc
  JOIN SalesOrders    AS so
    ON sc.custid = so.custid;

#left join 当sc.custid = so.custid中so没有custid但是sc中有时，
-- 左表的id仍然有，只是右表不匹配是显示NULL
SELECT sc.custid,sc.comapanyname,so.orderid,so.orderdate
  FROM SalesCustomers AS sc
  LEFT JOIN SalesOrders    AS so
    ON sc.custid = so.custid;

-- 查询2008-01-01下订单的客户和没下订单的客户 
SELECT sc.custid,sc.comapanyname,so.orderid,so.orderdate
  FROM SalesCustomers AS sc
  LEFT JOIN SalesOrders    AS so
    ON sc.custid = so.custid
   AND so.orderdate = '2008-01-01';

-- 曲折（这个where相当于过滤，无法展示所有数据 是个大bug）
SELECT sc.custid,sc.comapanyname,so.orderid,so.orderdate
  FROM SalesCustomers AS sc
  LEFT JOIN SalesOrders    AS so
    ON sc.custid = so.custid
 WHERE so.orderdate = '2008-01-01';

 

-- 7.
SELECT sc.custid,sc.comapanyname,
       CASE WHEN so.orderdate IS NULL Then 'NO' ELSE 'Yes' END AS Hash20080101
  FROM SalesCustomers AS sc
  LEFT JOIN SalesOrders    AS so
    ON sc.custid = so.custid
   AND so.orderdate = '2008-01-01';