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

王望旺

正如你所知，你不能通过自连接为视图编制索引。嗯，事实上，即使是同一张桌子的两个连接，即使不是技术上的自连接。微软的几个人提出了一个解决方案。但太复杂了，我不明白！！！
这里有解决问题的办法：http : //jmkehayias.blogspot.com/2008/12/creating-
indexed-view-with-self-
join.html
我想应用这项工作的视图是:
create VIEW vw_lookup_testWITH SCHEMABINDINGASselectcount_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 oon circt.circt_cstdn_user_id = o.circt_cstdn_user_id)join dbo.circt_cstdn ton dvc.circt_cstdn_user_id = t.circt_cstdn_user_idgroup byawc_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_test2WITH SCHEMABINDING  AS selectcount_big(*) as [countAll],awc_txt,city_nm,str_nm,stru_no,dvc.circt_nm,circt_cstdn_nm,data_orgtn_yr,dvc.circt_cstdn_user_idfrom dbo.dvc join dbo.circton dvc.circt_nm = circt.circt_nmjoin dbo.circt_cstdn oon circt.circt_cstdn_user_id = o.circt_cstdn_user_idgroup byawc_txt,city_nm,str_nm,stru_no,dvc.circt_nm,circt_cstdn_nm,data_orgtn_yr,dvc.circt_cstdn_user_idgo和
CREATE UNIQUE CLUSTERED INDEX [idx_vw_lookup_owner2_test1] ON [dbo].[vw_lookup_owner_test    [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_locasselectawc_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_yrfrom vw_lookup_owner_test2 o With (NOEXPAND)join circt_cstdn ton o.circt_cstdn_user_id = t.circt_cstdn_user_idgroup byawc_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然后，我可以根据需要在第一个视图上添加索引。我不确定解决方案（或解决方案）是否会真正提高性能，但我会让你知道。

解决方案:
                                                            这是我从博客文章中得到的
假设您想在dbo.circt_cstdn上加入2次，即您想要
                                 owner    techrowA    a.nm       b.nm
…与其将值分为两列，不如将值分为两行(以上每行为2行)，并添加另一列来说明哪一行是哪一行。请注意，第一.1行和第1.2行数据相同(名称和列除外)
                                 name forrow1.1 nm    owner
row1.2 nm    tech
…然后，您将重点放在所有者和技术人员的名称列的最大值上。-max函数只是用来欺骗的PIVOT(需要一些聚合函数)，如果只有一个技术行记录所有者nm nm，任何返回相同值的聚合函数都可以使用。
现在，如果我们为您查询
[ol]创建一个表d，像这样一个
i1
2与此交叉连接查询的第一部分
SELECT  count_big(*) as [count_all], awc_txt, city_nm, str_nm, stru_no, dvc.circt_nm, data_orgtn_yrFROM
                  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，使用所有者；如果Di2.使用技术行
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' ENDFROM
                  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_nmFROM
                  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_imedWITH 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
[/ol]如果有语法错误(一定有很多原因，因为我现在不能访问数据库)，请告诉我。

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

王望旺 LV1