代码之家 › 专栏 › 技术社区 › Caveatrob

模拟联合以生成索引视图

indexed-view tsql sql-server-2005 sql-server sql

Caveatrob · 技术社区 · 15 年前

我在SQLServer2005中得到了下面的表模式,我想将它非规范化为索引视图,以便进行一些我正在编写的查询,直到我可以永久地进行结构更改为止。

下表如下

Writing
(
DocumentSerial int
Grader1_ID int
Grade_1 int
Grader2_ID int 
Grade_2 int 
Grader3_ID int 
Grade_3 int
)

Users
(userID int,
firstname,
lastname
)

我想要一张表格,每种评分机/等级/文件组合都有一行,其中评分机是1号评分机、2号评分机或3号评分机。

我编写的视图使用联合,因此它不索引:

select documentSerial,grader1_id as grader_id, grade_1 as grade ,1 as sequence
 from Writing w inner join User U on w.grader1_id=u.userid
UNION
select documentSerial,grader2_id as grader_id, grade_2 as grade ,2 as sequence
 from Writing w inner join User U on w.grader2_id=u.userid
UNION
select documentSerial,grade31_id as grader_id, grade_3 as grade ,3 as sequence
 from Writing w inner join User U on w.grader3_id=u.userid

问题是-sql无法用union索引视图…

1 回复 | 直到 15 年前

OMG Ponies 15 年前

SELECT x.*
  INTO normalized_table
  FROM (select documentSerial,grader1_id as grader_id, grade_1 as grade ,1 as sequence
          from Writing w 
          join User U on w.grader1_id = u.userid
        UNION ALL
        select documentSerial,grader2_id as grader_id, grade_2 as grade ,2 as sequence
          from Writing w 
          join User U on w.grader2_id = u.userid
        UNION ALL
        select documentSerial,grade31_id as grader_id, grade_3 as grade ,3 as sequence
          from Writing w 
          join User U on w.grader3_id = u.userid) x