问题描述

我正在尝试使用具有父子关系的表并获取孩子的数量。我想通过利用创建一个孩子数量的索引视图COUNT_BIG(*)。

问题是，在我的索引视图中，我不想消除没有孩子的实体，而是希望这些实体Count为 0。

给定

> Id | Entity | Parent
> -: | :----- | :-----
>  1 | A      | null  
>  2 | AA     | A     
>  3 | AB     | A     
>  4 | ABA    | AB    
>  5 | ABB    | AB    
>  6 | AAA    | AA    
>  7 | AAB    | AA    
>  8 | AAC    | AA

我想创建一个返回的索引视图

> Entity | Count
> :----- | ----:
> A      |     2
> AA     |     3
> AB     |     2
> ABA    |     0
> ABB    |     0
> AAA    |     0
> AAB    |     0
> AAC    |     0

这是我的 SQL，但使用了 LEFT JOIN 和 CTE（索引视图中都不允许使用）

    DROP TABLE IF EXISTS Example
    CREATE TABLE Example (
      Id INT primary key,
      Entity varchar(50),
      Parent varchar(50)
    )
    
    
    INSERT INTO Example
    VALUES 
       (1, 'A', NULL)
      ,(2, 'AA',  'A')
      ,(3, 'AB','A')
      ,(4, 'ABA', 'AB')
      ,(5, 'ABB', 'AB')
      ,(6, 'AAA', 'AA')
      ,(7, 'AAB', 'AA')
      ,(8, 'AAC', 'AA')
    
    
    
    SELECT *
    FROM Example
    
    ;WITH CTE AS (
     SELECT Parent, COUNT(*) as Count
      FROM dbo.Example
      GROUP BY Parent
    )
      
    SELECT e.Entity, COALESCE(Count,0) Count
    FROM dbo.Example e
    LEFT JOIN CTE g
    ON e.Entity = g.Parent


GO

db<>小提琴演示

标签： sqlsql-servertsqlcommon-table-expressionindexed-view

解决方案

我不认为您可以使用 CTE 和 LEFT JOIN 来实现这一点，因为使用索引视图有很多限制。

解决方法

我建议将查询分为两部分：

创建索引视图而不是公用表表达式 (CTE)
创建一个执行 LEFT JOIN 的非索引视图

Entity除此之外，在 Table 的列上创建一个非聚集索引Example。

然后当您查询非索引视图时，它将使用索引

--CREATE TABLE
CREATE TABLE Example (
  Id INT primary key,
  Entity varchar(50),
  Parent varchar(50)
)

--INSERT VALUES
INSERT INTO Example
VALUES 
   (1, 'A', NULL)
  ,(2, 'AA',  'A')
  ,(3, 'AB','A')
  ,(4, 'ABA', 'AB')
  ,(5, 'ABB', 'AB')
  ,(6, 'AAA', 'AA')
  ,(7, 'AAB', 'AA')
  ,(8, 'AAC', 'AA')

--CREATE NON CLUSTERED INDEX
CREATE NONCLUSTERED INDEX idx1 ON dbo.Example(Entity);

--CREATE Indexed View

CREATE VIEW dbo.ExampleView_1
    WITH SCHEMABINDING
    AS 
 SELECT Parent, COUNT_BIG(*) as Count
  FROM dbo.Example
  GROUP BY Parent

CREATE UNIQUE CLUSTERED INDEX idx ON dbo.ExampleView_1(Parent);

--Create non-indexed view
CREATE VIEW dbo.ExampleView_2
    WITH SCHEMABINDING
    AS 
    SELECT e.Entity, COALESCE(Count,0) Count
    FROM dbo.Example e
    LEFT JOIN dbo.ExampleView_1 g
    ON e.Entity = g.Parent

因此，当您执行以下查询时：

SELECT * FROM dbo.ExampleView_2 WHERE Entity = 'A'

可以看到执行计划中使用了视图聚集索引和表非聚集索引：

附加信息

我没有找到其他解决方法来替换索引视图中的LEFT JOINorUNION或 or ，您可以查看许多类似的 Stackoverflow 问题：CTE

更新 1 - 拆分视图与笛卡尔连接

为了确定更好的方法，我尝试比较两种建议的方法。

--The other approach (cartesian join)
CREATE TABLE TwoRows (
    N INT primary key
)

INSERT INTO TwoRows
VALUES (1),(2)

CREATE VIEW dbo.indexedView  WITH SCHEMABINDING AS
    SELECT 
        IIF(T.N = 2, Entity, Parent) as Entity
        , COUNT_BIG(*) as CountPlusOne
        , COUNT_BIG(ALL IIF(T.N = 2, NULL, 1)) as Count
    FROM dbo.Example E1
    INNER JOIN dbo.TwoRows T
        ON 1=1
    WHERE IIF(T.N = 2, Entity, Parent) IS NOT NULL
    GROUP BY IIF(T.N = 2, Entity, Parent)
GO

CREATE UNIQUE CLUSTERED INDEX testIndex ON indexedView(Entity)

我在单独的数据库上创建了每个索引视图并执行了以下查询：