使用表以外的对象创建查询 (Visual Database Tools)Create Queries using Something Besides a Table (Visual Database Tools)

适用于：Applies to: 是SQL ServerSQL Server（所有支持的版本）yesSQL ServerSQL Server (all supported versions) 是Azure SQL 数据库Azure SQL DatabaseYesAzure SQL 数据库Azure SQL Database 是Azure SQL 托管实例Azure SQL Managed InstanceYesAzure SQL 托管实例Azure SQL Managed Instance 是Azure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics 是并行数据仓库Parallel Data Warehouseyes并行数据仓库Parallel Data Warehouse适用于：Applies to: 是SQL ServerSQL Server（所有支持的版本）yesSQL ServerSQL Server (all supported versions) 是Azure SQL 数据库Azure SQL DatabaseYesAzure SQL 数据库Azure SQL Database 是Azure SQL 托管实例Azure SQL Managed InstanceYesAzure SQL 托管实例Azure SQL Managed Instance 是Azure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics 是并行数据仓库Parallel Data Warehouseyes并行数据仓库Parallel Data Warehouse

每次编写检索查询时，必须明确说明所需的列和行以及查询处理器应从何处查找原始数据。Whenever you write a retrieval query, you articulate what columns you want, what rows you want, and where the query processor should find the original data. 一般情况下，此类原始数据由一个表或几个联接在一起的表组成。Typically, this original data consists of a table or several tables joined together. 不过，原始数据也可以来自表以外的源。But the original data can come from sources other than tables. 事实上，它可以来自视图、查询、同义词或可返回表的用户定义函数。In fact, it can come from views, queries, synonyms, or user-defined functions that return a table.

使用视图代替表Using a View in Place of a Table

您可以从视图选择行。You can select rows from a view. 例如，假设数据库包含一个名为“ExpensiveBooks”的视图，在该视图中，每行描述一个价格超过 19.99 美元的书名。For example, suppose the database includes a view called "ExpensiveBooks," in which each row describes a title whose price exceeds 19.99. 视图定义可能类似以下形式：The view definition might look like this:

SELECT *  
FROM titles  
WHERE price > 19.99

这样，只需从 ExpensiveBooks 视图中选择心理学书籍，就可以选择较贵的心理学书籍。You can select the expensive psychology books merely by selecting the psychology books from the ExpensiveBooks view. 生成的 SQL 结果可能类似以下形式：The resulting SQL might look like this:

SELECT *  
FROM ExpensiveBooks  
WHERE type = 'psychology'

同样，视图也可以参与 JOIN 操作。Similarly, a view can participate in a JOIN operation. 例如，只需将 sales 表联接到 ExpensiveBooks 视图，就可以查找较贵的书籍的销售额。For example, you can find the sales of expensive books merely by joining the sales table to the ExpensiveBooks view. 生成的 SQL 结果可能类似以下形式：The resulting SQL might look like this:

SELECT *  
FROM sales   
         INNER JOIN   
         ExpensiveBooks   
         ON sales.title_id   
         =  ExpensiveBooks.title_id

有关向查询添加视图的详细信息，请参阅向查询中添加表 (Visual Database Tools)。For more information about adding a view to a query, see Add Tables to Queries (Visual Database Tools).

使用查询代替表Using a Query in Place of a Table

您可以从查询选择行。You can select rows from a query. 例如，假设已编写了一个查询，用于检索合著书籍（有一个以上作者的书）的书名和标识符。For example, suppose you have already written a query retrieving titles and identifiers of the coauthored books - the books with more than one author. 该 SQL 可能类似以下形式：The SQL might look like this:

SELECT   
     titles.title_id, title, type  
FROM   
     titleauthor   
         INNER JOIN  
         titles   
         ON titleauthor.title_id   
         =  titles.title_id   
GROUP BY   
     titles.title_id, title, type  
HAVING COUNT(*) > 1

您随后可以编写另一个基于此结果的查询。You can then write another query that builds on this result. 例如，您可以编写一个检索合著的心理学书籍的查询。For example, you can write a query that retrieves the coauthored psychology books. 若要编写这个新查询，则可以将现有查询用作这个新查询的数据源。To write this new query, you can use the existing query as the source of the new query's data. 生成的 SQL 结果可能类似以下形式：The resulting SQL might look like this:

SELECT   
    title  
FROM   
    (  
    SELECT   
        titles.title_id,   
        title,   
        type  
    FROM   
        titleauthor   
            INNER JOIN  
            titles   
            ON titleauthor.title_id   
            =  titles.title_id   
    GROUP BY   
        titles.title_id,   
        title,   
        type  
    HAVING COUNT(*) > 1  
    )   
    co_authored_books  
WHERE     type = 'psychology'

加粗的文本表示用作新查询的数据源的现有查询。The emphasized text shows the existing query used as the source of the new query's data. 请注意，新查询使用了现有查询的别名（“co_authored_books”）。Note that the new query uses an alias ("co_authored_books") for the existing query. 有关别名的详细信息，请参阅创建表别名 (Visual Database Tools) 和创建列别名 (Visual Database Tools)。For more information about aliases, see Create Table Aliases (Visual Database Tools) and Create Column Aliases (Visual Database Tools).

同样，查询也可以参与 JOIN 操作。Similarly, a query can participate in a JOIN operation. 例如，只需将 ExpensiveBooks 视图联接到检索合著书籍的查询，就可以查找较贵的合著书籍的销售额。For example, you can find the sales of expensive coauthored books merely by joining the ExpensiveBooks view to the query retrieving the coauthored books. 生成的 SQL 结果可能类似以下形式：The resulting SQL might look like this:

SELECT   
    ExpensiveBooks.title  
FROM   
    ExpensiveBooks   
        INNER JOIN  
        (  
        SELECT   
            titles.title_id,   
            title,   
            type  
        FROM   
            titleauthor   
                INNER JOIN  
                titles   
                ON titleauthor.title_id   
                =  titles.title_id   
        GROUP BY   
            titles.title_id,   
            title,   
            type  
        HAVING COUNT(*) > 1  
        )

有关向查询添加查询的详细信息，请参阅向查询中添加表 (Visual Database Tools)。For more information about adding a query to a query, see Add Tables to Queries (Visual Database Tools).

使用用户定义函数代替表Using a User-Defined Function in Place of a Table

在 SQL Server 2000 或更高版本中，可以创建返回表的用户定义函数。In SQL Server 2000 or higher, you can create a user-defined function that returns a table. 此类函数对执行复杂逻辑操作或逐步逻辑操作很有用。Such functions are useful for performing complex or procedural logic.

例如，假设 employee 表包含一个附加列 employee.manager_emp_id，且存在从 manager_emp_id 到 employee.emp_id 的外键。For example, suppose the employee table contains an additional column, employee.manager_emp_id, and that a foreign key exists from manager_emp_id to employee.emp_id. 在 employee 表的每个行中，在 manager_emp_id 列显示该雇员的老板。Within each row of the employee table, the manager_emp_id column indicates the employee's boss. 更确切地说，它显示该雇员的老板的 emp_id。More precisely, it indicates the employee's boss's emp_id. 您可以创建用户定义函数以返回这样的表：在某个特定高级经理的所辖部门层次结构中工作的每个雇员在该表中各占一行。You can create a user-defined function that returns a table containing one row for each employee working within a particular high-level manager's organizational hierarchy. 可以调用函数 fn_GetWholeTeam，并对其进行设计以获得输入变量（待检索部门的经理的 emp_id）。You might call the function fn_GetWholeTeam, and design it to take an input variable - the emp_id of the manager whose team you want to retrieve.

随后可以编写将 fn_GetWholeTeam 函数用作数据源的查询。You can write a query that uses the fn_GetWholeTeam function as a source of data. 生成的 SQL 结果可能类似以下形式：The resulting SQL might look like this:

SELECT *   
FROM   
     fn_GetWholeTeam ('VPA30890F')

“VPA30890F”是待检索部门的经理的 emp_id。"VPA30890F" is the emp_id of the manager whose organization you want to retrieve. 有关向查询添加用户定义函数的详细信息，请参阅向查询中添加表 (Visual Database Tools)。For more information about adding a user-defined function to a query, see Add Tables to Queries (Visual Database Tools). 有关用户定义函数的完整说明，请参阅 用户定义函数。For a complete description of user-defined functions, see User-Defined Functions.