这个创建视图代码是如何工作的？-解网

问：

好的，这里是视图的示例代码。第二部分对我来说似乎很清楚。这是运行查询以使用视图时将显示的部分。不过，第一部分尚不清楚。select RT.RoleID[...]

据我所知，前两行是标准的，然后是整个部分。有人可以向我解释一下吗？从未见过的配方withwith "something" as (select)

    CREATE VIEW [dbo].[sviw_System_MyPermissions_CurrentDomain]
AS  

WITH MyDomainRoles AS (
  SELECT RM.Domain, RM.RoleID
    FROM stbl_System_RolesMembersDomains AS RM WITH (NOLOCK)
    WHERE RM.Domain = (SELECT CurrentDomain FROM stbl_System_Users WITH (NOLOCK) WHERE Login = SUSER_SNAME())
      AND RM.Login = SUSER_SNAME()
)

SELECT RT.RoleID, RT.TableName, DR.Domain, RT.GrantUpdate, RT.GrantInsert, RT.GrantDelete
  FROM stbl_System_RolesTables AS RT WITH (NOLOCK) 
    JOIN MyDomainRoles AS DR ON RT.RoleID = DR.RoleID

GO

sql 服务器 t-sql 公用表表达式

CREATE VIEW vSalesStaffQuickStats
AS
 SELECT E.EmployeeID, 
         EmployeeOrders = OS.NumberOfOrders, 
         EmployeeLastOrderDate = OS.MostRecentOrderDate, 
         E.ManagerID, 
         ManagerOrders = OM.NumberOfOrders, 
         ManagerLastOrderDate = OM.MostRecentOrderDate 
  FROM   HumanResources.Employee AS E 
       INNER JOIN    (
            SELECT SalesPersonID, COUNT(*) NumberOfOrders
                  , MAX(OrderDate) MostRecentOrderDate
            FROM Sales.SalesOrderHeader
            GROUP BY SalesPersonID
      ) AS OS 
         ON E.EmployeeID = OS.SalesPersonID 
       LEFT OUTER JOIN    (
            SELECT SalesPersonID, COUNT(*) NumberOfOrders
                 , MAX(OrderDate) MostRecentOrderDate
            FROM Sales.SalesOrderHeader
            GROUP BY SalesPersonID
      ) AS OM 
         ON E.ManagerID = OM.SalesPersonID

如您所见，您可以轻松地将其与子查询交换。但就您而言，您需要做两次（使用 CTE，您只做一次）。

编辑：

更新后使用新查询：

CREATE VIEW [dbo].[sviw_System_MyPermissions_CurrentDomain]
AS  
SELECT RT.RoleID, RT.TableName, DR.Domain, RT.GrantUpdate, RT.GrantInsert, RT.GrantDelete
FROM stbl_System_RolesTables AS RT WITH (NOLOCK) 
JOIN (
    SELECT RM.Domain, RM.RoleID
    FROM stbl_System_RolesMembersDomains AS RM WITH (NOLOCK)
    WHERE RM.Domain = (SELECT CurrentDomain FROM stbl_System_Users WITH (NOLOCK) WHERE Login = SUSER_SNAME())
      AND RM.Login = SUSER_SNAME()
) AS DR ON RT.RoleID = DR.RoleID

它只是嵌入式子选择的语法糖。性能（几乎）总是一样的。它只是使代码更具可读性。它并不像一个“函数”，因为 CTE 的范围只是下一个命令（SELECT/UPDATE/DELETE）......例如，您不能将其重用于多个 SELECT。这就像创建一个动态虚拟表供 SELECT 操作一样。

0赞 Milan 8/9/2017 #2

WITH关键字仅指公共表表达式（在此处阅读更多内容：https://learn.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql）基本上，WITH语句中的所有内容都可以被视为外部化的子查询，然后在主查询中使用结果集。

即：

WITH MyDomainRoles AS (

  --- sub-query Start
  SELECT RM.Domain, RM.RoleID
    FROM stbl_System_RolesMembersDomains AS RM WITH (NOLOCK)
    WHERE RM.Domain = (SELECT CurrentDomain FROM stbl_System_Users WITH (NOLOCK) WHERE Login = SUSER_SNAME())
      AND RM.Login = SUSER_SNAME()
  --- sub-query End

)

...上述子查询的结果集保存在名为 MyDomainRoles 的 CTE 中。然后，可以将 MyDomainRoles 引用为表。使阅读更简单，书写更清晰。

由于这个 CTE 非常简单，您可以轻松地将其重写如下，但它看起来并不那么整洁：

WITH MyDomainRoles AS (

)

SELECT RT.RoleID, RT.TableName, DR.Domain, RT.GrantUpdate, RT.GrantInsert, RT.GrantDelete
  FROM stbl_System_RolesTables AS RT WITH (NOLOCK) 
    JOIN (

SELECT RM.Domain, RM.RoleID
    FROM stbl_System_RolesMembersDomains AS RM WITH (NOLOCK)
    WHERE RM.Domain = (SELECT CurrentDomain FROM stbl_System_Users WITH (NOLOCK) WHERE Login = SUSER_SNAME())
      AND RM.Login = SUSER_SNAME()

) AS DR ON RT.RoleID = DR.RoleID

上一个：如何使用临时表设置触发器

下一个：在引导列中定位元素

这个创建视图代码是如何工作的？

How does this create view code work?

评论

评论