T-SQL学习中--子查询

最新推荐文章于 2022-02-24 01:00:00 发布

weixin_34199335

最新推荐文章于 2022-02-24 01:00:00 发布

阅读量99

点赞数

CC 4.0 BY-SA版权

原文链接：https://segmentfault.com/a/1190000002722918

本文深入探讨了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