T-SQL学习中--子查询

SQLSELECT productid, productname, unitprice
FROM Production.Products
WHERE unitprice =
  (SELECT MIN(unitprice)
   FROM Production.Products);

检索Production.Products表中价格最低的产品的productid, productname, unitprice。

SQLSELECT productid, productname, unitprice
FROM Production.Products
WHERE supplierid IN
  (SELECT supplierid
   FROM Production.Suppliers
   WHERE country = N'Japan');

检索Production.Products表中由日本供货商供应的商品的productid, productname, unitprice。

SQLSELECT categoryid, productid, productname, unitprice
FROM Production.Products AS P1
WHERE unitprice =
  (SELECT MIN(unitprice)
   FROM Production.Products AS P2
   WHERE P2.categoryid = P1.categoryid);

检索Production.Products表中同类产品中价格最低的产品。相当于先检索SELECT categoryid ,MIN(unitprice) FROM production.products GROUP BY categoryid，然后再找出这些价格对应的产品的productid和productname。

SQLSELECT custid, companyname
FROM Sales.Customers AS C
WHERE EXISTS
  (SELECT *
   FROM Sales.Orders AS O
   WHERE O.custid = C.custid
     AND O.orderdate = '20070212');

检索在2007年2月12日下过订单的客户的客户编号、公司名。相当于下面这句SQL：

SQLSELECT custid, companyname 
FROM Sales.Customers AS C 
WHERE custid IN 
    (SELECT custid FROM Sales.Orders
    WHERE orderdate = '20070212');

SQLSELECT custid, companyname
FROM Sales.Customers AS C
WHERE NOT EXISTS
  (SELECT *
   FROM Sales.Orders AS O
   WHERE O.custid = C.custid
     AND O.orderdate = '20070212');

检索没有在2007年2月12日下过订单的客户的客户编号、公司名。相当于下面这句SQL：

SQLSELECT custid, companyname 
FROM Sales.Customers AS C 
WHERE custid NOT IN 
    (SELECT custid FROM Sales.Orders
    WHERE orderdate = '20070212');

SQLSELECT
  ROW_NUMBER() OVER(PARTITION BY categoryid
                    ORDER BY unitprice, productid) AS rownum,
  categoryid, productid, productname, unitprice
FROM Production.Products;

检索Production.Products表的categoryid, productid, productname, unitprice，并把它按商品类的分区块，取得每个商品在各自的商品类中的价格排名（同类商品中最便宜的排在前面）。

SQLSELECT categoryid, productid, productname, unitprice
FROM (SELECT
        ROW_NUMBER() OVER(PARTITION BY categoryid
                          ORDER BY unitprice, productid) AS rownum,
        categoryid, productid, productname, unitprice
      FROM Production.Products) AS D
WHERE rownum <= 2;

取得Production.Products表中同类产品价格最低和次低的商品的categoryid, productid, productname, unitprice。
下面的SQL语句与上一条SQL语句等阶

SQLWITH C AS
(
  SELECT
    ROW_NUMBER() OVER(PARTITION BY categoryid
                      ORDER BY unitprice, productid) AS rownum,
    categoryid, productid, productname, unitprice
  FROM Production.Products
)
SELECT categoryid, productid, productname, unitprice
FROM C
WHERE rownum <= 2;

SQLWITH EmpsCTE AS
(
  SELECT empid, mgrid, firstname, lastname, 0 AS distance
  FROM HR.Employees
  WHERE empid = 9

  UNION ALL

  SELECT M.empid, M.mgrid, M.firstname, M.lastname, S.distance + 1 AS distance
  FROM EmpsCTE AS S
    JOIN HR.Employees AS M
      ON S.mgrid = M.empid
)
SELECT empid, mgrid, firstname, lastname, distance
FROM EmpsCTE;

取得HR.Employees表中工号为9的员工及其上司、上司的上司……的工号，姓名，以及与之的层级关系distance。结果如下表：

empid	mgrid	firstname	lastname	distance
9	5	Zoya	Dolgopyatova	0
5	2	Sven	Buck	1
2	1	Don	Funk	2
1	NULL	Sara	Davis	3

而HR.Employee整个数据表的数据如下：

empid	mgrid	firstname	lastname
1	NULL	Sara	Davis
2	1	Don	Funk
3	2	Judy	Lew
4	3	Yael	Peled
5	2	Sven	Buck
6	5	Paul	Suurs
7	5	Russell	King
8	3	Maria	Cameron
9	5	Zoya	Dolgopyatova