在自我加入问题上需要一些认真的帮助

一尘不染

在自我加入问题上需要一些认真的帮助

sql

如您所知，您无法通过自连接为视图编制索引。好吧，实际上即使是同一张表的两个联接，即使从技术上讲不是自联接。微软的几个人提出了一个解决方案。但是它是如此复杂，我不明白！！！

问题的解决方案在这里：http : //jmkehayias.blogspot.com/2008/12/creating-
indexed-view-with-self-
join.html

我想将这项工作应用到的视图是：

create VIEW vw_lookup_test
WITH SCHEMABINDING
AS
select
count_big(*) as [count_all],
awc_txt,
city_nm,
str_nm,
stru_no,
o.circt_cstdn_nm [owner],
t.circt_cstdn_nm [tech],
dvc.circt_nm,
data_orgtn_yr 
from 
((dbo.dvc 
join dbo.circt 
on dvc.circt_nm = circt.circt_nm) 
join dbo.circt_cstdn o
on circt.circt_cstdn_user_id = o.circt_cstdn_user_id)
join dbo.circt_cstdn t
on dvc.circt_cstdn_user_id = t.circt_cstdn_user_id
group by
awc_txt,
city_nm,
str_nm,
stru_no,
o.circt_cstdn_nm,
t.circt_cstdn_nm,
dvc.circt_nm,
data_orgtn_yr 
go

任何帮助将不胜感激！！！

提前非常感谢！

编辑：所以我发现这也将起作用。请注意，我在第一个索引视图中加入了该表一次，并在第二个非索引视图中第二次加入了该表。

alter VIEW vw_lookup_owner_test2
WITH SCHEMABINDING  
AS 
select
count_big(*) as [countAll],
awc_txt,
city_nm,
str_nm,
stru_no,
dvc.circt_nm,
circt_cstdn_nm,
data_orgtn_yr,
dvc.circt_cstdn_user_id
from dbo.dvc 
join dbo.circt
on dvc.circt_nm = circt.circt_nm
join dbo.circt_cstdn o
on circt.circt_cstdn_user_id = o.circt_cstdn_user_id
group by
awc_txt,
city_nm,
str_nm,
stru_no,
dvc.circt_nm,
circt_cstdn_nm,
data_orgtn_yr,
dvc.circt_cstdn_user_id
go

和

CREATE UNIQUE CLUSTERED INDEX [idx_vw_lookup_owner2_test1] ON [dbo].[vw_lookup_owner_test2] 
(
    [awc_txt] ASC,
    [city_nm] ASC,
    [str_nm] ASC,
    [stru_no] ASC,
    [circt_nm] ASC,
    [circt_cstdn_nm] ASC,
    [data_orgtn_yr] ASC,
    [circt_cstdn_user_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

和

create view vw_lookup_dvc_loc
as
select
awc_txt,
city_nm,
str_nm,
stru_no,
circt_nm,
o.circt_cstdn_nm as [owner],
--o.circt_cstdn_user_id,
t.circt_cstdn_nm as tech,
data_orgtn_yr
from vw_lookup_owner_test2 o With (NOEXPAND)
join circt_cstdn t
on o.circt_cstdn_user_id = t.circt_cstdn_user_id
group by
awc_txt,
city_nm,
str_nm,
stru_no,
circt_nm,
o.circt_cstdn_nm,
data_orgtn_yr,
t.circt_cstdn_nm
--o.circt_cstdn_user_id

然后，我可以根据需要在第一个视图上创建添加索引。我不确定该解决方案（或该问题的解决方法）是否会真正提高性能，但我会让您知道。

阅读 127

2021-05-30

共1个答案

一尘不染

这是我从博客文章中得到的

假设您想在dbo.circt_cstdn上加入2次，即您想要
```
         owner       tech
```
rowA a.nm b.nm
…
与其将值分成2列，不如将其分为2行（上面的每一行为2行），并添加另一列以说明哪一行是哪一列。请注意，第1.1行和第1.2行具有相同的数据（名称和列除外）
```
         name   for
```
row1.1 nm owner
row1.2 nm tech
…
然后，您将重点放在所有者和技术人员的“名称”列的最大值上。注意-max函数只是用来欺骗PIVOT（需要一些聚合函数），如果只有一个记录所有者技术行1 nm nm，则可以使用返回相同值的任何聚合函数。

现在，如果我们为您查询

创建一个表d，像这样一个
```
 i
```
1
2
与此交叉连接查询的第一部分
```
SELECT 
 count_big(*) as [count_all], 
 awc_txt, 
 city_nm, 
 str_nm, 
 stru_no, 
 dvc.circt_nm, 
 data_orgtn_yr
```
FROM
dbo.dvc
INNER JOIN dbo.circt on dvc.circt_nm = circt.circt_nm
CROSS JOIN dbo.d
GROUP BY
awc_txt, city_nm, str_nm, stru_no, dvc.circt_nm, data_orgtn_yr, d.i
现在，如果Di为1，则使用所有者行；如果Di为2，则使用技术行
```
SELECT 
 count_big(*) as [count_all], 
 awc_txt, 
 city_nm, 
 str_nm, 
 stru_no, 
 dvc.circt_nm, 
 data_orgtn_yr,
 Case 
     WHEN d.i = 1 THEN 'Owner'
     WHEN d.i = 2 THEN 'Tech'
 END
```
FROM
dbo.dvc
INNER JOIN dbo.circt on dvc.circt_nm = circt.circt_nm
CROSS JOIN dbo.d
GROUP BY
awc_txt, city_nm, str_nm, stru_no, dvc.circt_nm, data_orgtn_yr,
Case
WHEN d.i = 1 THEN ‘Owner’
WHEN d.i = 2 THEN ‘Tech’
END
现在添加nm列。要获得名称，您可以将circt_cstdn加入到circt，如果它是所有者行（di = 1），则加入dvc，如果它是技术行（di = 2）。注意-我在这里尝试了快捷方式，方法是将其置于联接条件中。如果不起作用，请尝试博客发布方式（对circt.circt_cstdn_user_id 或 dvc.circt_cstdn_user_id进行联接，然后使用WHERE子句进行筛选）
```
SELECT 
 count_big(*) as [count_all], 
 awc_txt, 
 city_nm, 
 str_nm, 
 stru_no, 
 dvc.circt_nm, 
 data_orgtn_yr,
 Case 
     WHEN d.i = 1 THEN 'Owner'
     WHEN d.i = 2 THEN 'Tech'
 END as PersonType,
 circt_cstdn_nm
```
FROM
dbo.dvc
INNER JOIN dbo.circt on dvc.circt_nm = circt.circt_nm
CROSS JOIN dbo.d
INNER JOIN dbo.circt_cstdn on circt_cstdn_user_id =
CASE
WHEN d.i = 1 THEN circt.circt_cstdn_user_id
WHEN d.i = 2 THEN dvc.circt_cstdn_user_id
END
GROUP BY
awc_txt, city_nm, str_nm, stru_no, dvc.circt_nm, data_orgtn_yr,
Case
WHEN d.i = 1 THEN ‘Owner’
WHEN d.i = 2 THEN ‘Tech’
END,
circt_cstdn_nm
使用该视图创建一个视图并创建索引
```
create VIEW vw_lookup_test_imed
```
WITH SCHEMABINDING
AS
<>
GO

spell to create INDEX
现在，您将PIVOT转换为PersonType列到Owner和Tech列
```
SELECT 
 count_all, 
 awc_txt, 
 city_nm, 
 str_nm, 
 stru_no, 
 dvc.circt_nm, 
 data_orgtn_yr,
 [Owner], 
 [Tech]
```
FROM
(
SELECT
count_all,
awc_txt,
city_nm,
str_nm,
stru_no,
dvc.circt_nm,
data_orgtn_yr,
PersonType,
circt_cstdn_nm
FROM dbo.vw_lookup_test_imed WITH (NOEXPAND)
) src
PIVOT
(
MAX(circt_cstdn_nm)
FOR PersonType IN ([Owner], [Tech])
) pvt

如果存在语法错误（肯定有很多原因，因为我现在无法访问数据库），请告诉我。

2021-05-30