子选择示例

您可以使用子选择的各种子句来构建查询。

示例

下面的子选择示例说明了如何使用子选择的各个子句来构建查询。

  • 示例1：显示 表DSN8C10.EMP 的所有行。

       SELECT * FROM DSN8C10.EMP;
  • 示例 2：显示 DSN8C10.EMP 中每组行的工作代码、最高工资和最低工资，这些行具有相同的工作代码，但仅限于多行且最高工资大于 50000 的组。
       SELECT JOB, MAX(SALARY), MIN(SALARY)
     FROM DSN8C10.EMP
     GROUP BY JOB
     HAVING COUNT(*) > 1 AND MAX(SALARY) > 50000;
  • 示例 3：对于 部门 E11 中的每位员工，从表 DSN8C10.EMPPROJACT 中获取以下信息：员工编号、活动编号、活动开始日期和活动结束日期。 使用CHAR函数，将开始日期和结束日期转换为美国格式。 从 DSN8C10.EMP表中获取所需部门信息。
       SELECT EMPNO, ACTNO, CHAR(EMSTDATE,USA), CHAR(EMENDATE,USA)
     FROM DSN8C10.EMPPROJACT
     WHERE EMPNO IN (SELECT EMPNO FROM DSN8C10.EMP
                     WHERE WORKDEPT = 'E11');

  • 示例4：显示 所有部门中最高工资低于所有员工平均工资的部门的部门编号和最高工资。 （在这个例子中，子查询只执行一次。）

       SELECT WORKDEPT, MAX(SALARY)
     FROM DSN8C10.EMP
     GROUP BY WORKDEPT
     HAVING MAX(SALARY) < (SELECT AVG(SALARY)
                             FROM DSN8C10.EMP);
  • 示例5：显示 所有部门中最高工资低于其他所有部门员工平均工资的部门的部门编号和最高工资。 （与示例4不同，此语句中的子查询包含关联引用，需要针对每个组执行。）
       SELECT WORKDEPT, MAX(SALARY)
     FROM DSN8C10.EMP Q
     GROUP BY WORKDEPT
     HAVING MAX(SALARY) < (SELECT AVG(SALARY)
                             FROM DSN8C10.EMP
                             WHERE NOT WORKDEPT = Q.WORKDEPT);
  • 示例6 ：对于同年雇用的每组员工，请显示雇用年份和当前平均薪资。 （本示例演示了如何在 FROM 子句中使用 AS 子句来命名要在 GROUP BY 子句中引用的派生列。）
       SELECT HIREYEAR, AVG(SALARY)
     FROM (SELECT YEAR(HIREDATE) AS HIREYEAR, SALARY
             FROM DSN8C10.EMP) AS NEWEMP
     GROUP BY HIREYEAR;

  • 示例 7 ：有关如何在 SELECT 子句中根据表达式对查询结果进行分组而不必重新输入表达式的示例，请参阅示例 4 中的 CASE 表达式。

  • 示例8 ：获取 DSN8C10.EMP 中所有员工的员工编号和员工姓名。 按租用日期排序。

       SELECT EMPNO, FIRSTNME, LASTNAME
     FROM DSN8C10.EMP
     ORDER BY HIREDATE;

  • 例9 ：选择表 T1 和 T2 中的所有行，并排序，使表 T1 中的行排在最前面，按列 C1 排序，然后是表 T2 中的行，按列 C2 排序。 T1 的行由一个子选择检索，该子选择与另一个子选择的检索结果相关联，后者从 T2 检索行。 每个子选择指定了引用表中行的排序。 请注意，两个子选择都需要用括号括起来，因为每个子选择都不是最外层的全选。 更改开始因为两个 ORDER BY 子句都出现在带括号的子选择中，所以两个 ORDER BY 子句都不会为最外层结果表提供排序。更改结束

    (SELECT * FROM T1 ORDER BY C1)
UNION
(SELECT * FROM T2 ORDER BY C2);

  • 示例10 ：指定ORDER BY子句，以便在union中使用结果表的第二列对结果排序。 在这个例子中，第二个ORDER BY子句适用于最外层fullselect的结果（即并集的结果），而不是第二个子选择的结果。 如果打算将第二个ORDER BY子句应用于第二个子选择，则应将第二个子选择括在括号内，如例9所示。

    (SELECT * FROM T1 ORDER BY C1)
UNION
SELECT * FROM T2 ORDER BY C2

  • 示例11：检索 表 T1 的所有行（无特定排序），并将结果表与表 T2 的行连接起来，这些行已按表 T2 的第一列排序。 fullselect中的ORDER BY ORDER OF子句指定了合并结果表中行的顺序将继承最终结果。

    SELECT *                             
  FROM (SELECT * FROM T1             
         UNION ALL                   
        (SELECT * FROM T2 ORDER BY 1)
       ) AS UTABLE                   
  ORDER BY ORDER OF UTABLE;
  • 示例 12：以下示例 使用查询将表中的数据与嵌套表表达式的结果表连接起来。 查询使用ORDER BY ORDER OF子句，根据嵌套表表达式的行顺序对结果表的行进行排序。
    SELECT T1.C1, T1.C2, TEMP.Cy, TEMP.Cx
  FROM T1,
    (SELECT T2.C1, T2.C2 FROM T2 ORDER BY 2) AS TEMP(Cx, Cy)
  WHERE Cy = T1.C1
  ORDER BY ORDER OF TEMP;
  • 示例13：使用 EMP_ACT表，找出项目编号中员工工资在所有员工中排名前三的项目编号。
    SELECT EMP_ACT.EMPNO, PROJNO
  FROM EMP_ACT
  WHERE EMP_ACT.EMPNO IN
    (SELECT EMPLOYEE.EMPNO
     FROM EMPLOYEE
     ORDER BY SALARY DESC
     FETCH FIRST 3 ROWS ONLY);

  • 例14：假设 存在一个名为ADDYEARS的外部函数。 对于给定的日期，该函数会添加给定的年数并返回新的日期。 （函数两个输入参数的数据类型分别为日期和整数。） 获取过去五年内所有员工的员工编号和姓名。

       SELECT EMPNO, FIRSTNME, LASTNAME
     FROM DSN8C10.EMP
     WHERE ADDYEARS(HIREDATE, 5) > CURRENT DATE;

    为了区分不同类型的连接、显示嵌套表表达式以及演示如何组合连接列，其余示例使用了以下两个表：

    The PARTS table                         The PRODUCTS table
PART      PROD#   SUPPLIER              PROD#     PRODUCT        PRICE
=======   =====   ============          =====     ===========    =====
WIRE      10      ACWF                  505       SCREWDRIVER    3.70
OIL       160     WESTERN_CHEM          30        RELAY          7.55
MAGNETS   10      BATEMAN               205       SAW            18.90
PLASTIC   30      PLASTIK_CORP          10        GENERATOR      45.75
BLADES    205     ACE_STEEL

  • 示例 15： 连接 PROD# 列上的表以获取零件及其供应商和使用这些零件的产品的表：

    SELECT PART, SUPPLIER, PARTS.PROD#, PRODUCT
   FROM PARTS, PRODUCTS
   WHERE PARTS.PROD# = PRODUCTS.PROD#;

    或

    SELECT PART, SUPPLIER, PARTS.PROD#, PRODUCT
  FROM PARTS INNER JOIN PRODUCTS
    ON PARTS.PROD# = PRODUCTS.PROD#;
    以下两个语句中的任何一个都会得出这样的结果：
    PART        SUPPLIER       PROD#     PRODUCT
=======     ============   =====     ==========
WIRE        ACWF           10        GENERATOR
MAGNETS     BATEMAN        10        GENERATOR
PLASTIC     PLASTIK_CORP   30        RELAY
BLADES      ACE_STEEL      205       SAW

    请注意以下关于这个例子：

    • 零件表（OIL）中有一个零件（#160）未在产品表中列出。 有一个产品（螺丝刀，编号505）的零件表中未列出任何零件。 在连接的结果中，既没有“油”，也没有“螺丝刀”。

      然而， 外部连接包括连接列中的值不匹配的行。

    • 有明确的语法来表示这种熟悉的连接不是外部连接，而是内部连接。 您可以在 FROM 子句中使用 INNER JOIN 代替逗号。 当您在FROM子句中明确连接表时，请使用ON。

    您可以指定更复杂的连接条件，以获得不同的结果集。 例如，从零件、供应商、产品编号和产品表中删除以字母A开头的供应商：

    SELECT PART, SUPPLIER, PARTS.PROD#, PRODUCT
  FROM PARTS INNER JOIN PRODUCTS
    ON PARTS.PROD# = PRODUCTS.PROD#
    AND SUPPLIER NOT LIKE 'A%';

    查询结果为所有供应商名称不以A开头的行：

    PART        SUPPLIER       PROD#     PRODUCT
=======     ============   =====     ==========
MAGNETS     BATEMAN        10        GENERATOR
PLASTIC     PLASTIK_CORP   30        RELAY

  • 示例 16： 连接 PROD# 列上的表格以获取所有零件和产品的表格，并显示供应商信息（如果有）。

    SELECT PART, SUPPLIER, PARTS.PROD#, PRODUCT
  FROM PARTS FULL OUTER JOIN PRODUCTS
    ON PARTS.PROD# = PRODUCTS.PROD#;

    结果为：

    PART        SUPPLIER       PROD#     PRODUCT
=======     ============   =====     ==========
WIRE        ACWF           10        GENERATOR
MAGNETS     BATEMAN        10        GENERATOR
PLASTIC     PLASTIK_CORP   30        RELAY
BLADES      ACE_STEEL      205       SAW
OIL         WESTERN_CHEM   160       (null)
(null)      (null)         (null)    SCREWDRIVER

    FULL OUTER JOIN子句包含两个表中未匹配的行。 结果表中连续的缺失值用空值填充。

  • 示例17： 将“产品编号”列中的表格合并，得到一个包含所有零件的表格，显示这些零件用于哪些产品（如果有）：

    SELECT PART, SUPPLIER, PARTS.PROD#, PRODUCT
  FROM PARTS LEFT OUTER JOIN PRODUCTS
    ON PARTS.PROD# = PRODUCTS.PROD#;

    结果为：

    PART        SUPPLIER       PROD#     PRODUCT
=======     ============   =====     ==========
WIRE        ACWF           10        GENERATOR
MAGNETS     BATEMAN        10        GENERATOR
PLASTIC     PLASTIK_CORP   30        RELAY
BLADES      ACE_STEEL      205       SAW
OIL         WESTERN_CHEM   160       (null)

    LEFT OUTER JOIN子句包含之前标识的表中的行，其中连接列中的值与之后标识的表中的连接列中的值不匹配。

  • 示例18： 将PROD#列的表格合并，得到一个包含所有产品的表格，显示该产品使用的零件（如果有）和供应商。

    SELECT PART, SUPPLIER, PRODUCTS.PROD#, PRODUCT
  FROM PARTS RIGHT OUTER JOIN PRODUCTS
    ON PARTS.PROD# = PRODUCTS.PROD#;

    结果为：

    PART        SUPPLIER       PROD#     PRODUCT
=======     ============   =====     ===========
WIRE        ACWF           10        GENERATOR
MAGNETS     BATEMAN        10        GENERATOR
PLASTIC     PLASTIK_CORP   30        RELAY
BLADES      ACE_STEEL      205       SAW
(null)      (null)         505       SCREWDRIVER

    RIGHT OUTER JOIN子句包括在其后标识的表中的行，其中连接列中的值与之前标识的表中的连接列中的值不匹配。

  • 示例19： 示例16 （完全外部连接）的结果显示螺丝刀的产品编号为空，尽管产品表中包含螺丝刀的产品编号。 这是因为查询的 SELECT 列表中未列出 PRODUCTS.PROD #。 使用COALESCE修改查询，以便显示两个表中的所有零件编号。

    SELECT PART, SUPPLIER,
      COALESCE(PARTS.PROD#, PRODUCTS.PROD#) AS PRODNUM, PRODUCT
      FROM PARTS FULL OUTER JOIN PRODUCTS
        ON PARTS.PROD# = PRODUCTS.PROD#;

    结果，请注意AS子句（AS PRODNUM）为COALESCE函数的计算结果提供了一个名称：

    PART        SUPPLIER       PRODNUM   PRODUCT
=======     ============   =======   ===========
WIRE        ACWF           10        GENERATOR
MAGNETS     BATEMAN        10        GENERATOR
PLASTIC     PLASTIK_CORP   30        RELAY
BLADES      ACE_STEEL      205       SAW
OIL         WESTERN_CHEM   160       (null)
(null)      (null)         505       SCREWDRIVER

  • 示例20 ：对于产品编号小于200的所有零件，显示零件、零件供应商、产品编号和产品名称。 使用嵌套表格表达式。

    SELECT PART, SUPPLIER, PRODNUM, PRODUCT
  FROM (SELECT PART, PROD# AS PRODNUM, SUPPLIER
                FROM PARTS
                   WHERE PROD# < 200) AS PARTX
            LEFT OUTER JOIN PRODUCTS
                ON PRODNUM = PROD#;

    结果为：

    PART        SUPPLIER         PRODNUM     PRODUCT
=======     ============     =======     ==========
WIRE        ACWF             10          GENERATOR
MAGNETS     BATEMAN          10          GENERATOR
PLASTIC     PLASTIK_CORP     30          RELAY
OIL         WESTERN_CHEM     160         (null)

  • 示例21 ：在子选择中多次指定DISTINCT的语句示例：

       SELECT DISTINCT COUNT(DISTINCT A1), COUNT(A2)
     FROM T1;
       SELECT COUNT(DISTINCT A))
     FROM T1
     WHERE A3 > 0
     HAVING AVG(DISTINCT A4) >1;

  • 示例22 ：使用交叉连接将所有客户的信息与所有州的信息进行合并。 交叉连接将两个表中的所有行组合在一起，形成笛卡尔积。 假设存在以下表格：

    Customer:
---------------------
ACOL1     |  ACOL2
---------------------
A1        |  AA1
A2        |  AA2
A3        |  AA3
---------------------
    States:
---------------------
BCOL1     |  BCOL2
---------------------
B1        |  BB1
B2        |  BB2
---------------------

    以下两个精选语句会产生相同的结果：

    SELECT * FROM customer CROSS JOIN states
    SELECT * FROM A, B

    以下为这两个选择语句的结果表：

    ------------------------------------------
ACOL1     |  ACOL2  |  BCOL1   |  BCOL2
------------------------------------------
A1        |  AA1    |  B1      |  BB1
A1        |  AA1    |  B2      |  BB2
A2        |  AA2    |  B1      |  BB1
A2        |  AA2    |  B2      |  BB2
A3        |  AA3    |  B1      |  BB1
A3        |  AA3    |  B2      |  BB2
------------------------------------------

  • 示例22： 在引用通用表函数时使用类型相关子句。 在以下选定语句中， 'tf6' 是使用CREATE FUNCTION（外部表）语句定义的通用表函数。 类型相关子句用于定义结果表的列名和数据类型。

    SELECT c1, c2
	FROM T1(tf6('abcd'))
		AS z (c1 int, c2 varchar(100));