MS SQL视图。两个相同的领域 [英] MS SQL view. Two identical fields

问题描述

我被卡住



表格中有两个字段，类型相同，加上code字段另一张表，描述代码



请建议



如何创建视图以显示描述这两个字段来自链式表而不是代码。

 SELECT dbo.MFU.PrintFormatMaximum，dbo.MFU .PrintFormatMinimum 
 FROM dbo.MFU LEFT OUTER JOIN 
 dbo.PrintFormatTypes ON dbo.MFU.PrintFormatMinimum = dbo.PrintFormatTypes.Code AND dbo.MFU.PrintFormatMaximum = dbo.PrintFormatTypes.Code 

我的尝试：



论坛，
TSQL，
SQL查看，

MSDN

解决方案

引用：

即一个链式字典表< - 多个字段

你需要两个JOIN，o ne表示原始表中的每一列：

  SELECT  d1.field_description  AS  Field1，d2.field_description  AS  Field2  FROM  Table_data t 
  JOIN  Table_dictionary d1  ON  t.field1 = d1.field_code 
  JOIN  Table_dictionary d2  ON  t.field2 = d2.field_code 

   -   删除视图依赖关系表后，普通视图无效。 
   -   我们不能通过在视图中使用WITH SCHEMABINDING选项来删除视图依赖关系表。 
 
  CREATE   view  VName  WITH  SCHEMABINDING 
  AS  
  SELECT  
 MFU.PrintFormatMaximum，
 MFU.PrintFormatMinimum 
 
  FROM  dbo.MFU  LEFT   OUTER   JOIN  dbo.PrintFormatTypes 
 
  ON  dbo.MFU.PrintFormatMinimum = dbo.PrintFormatTypes.Code  AND  
 dbo.MFU.PrintFormatMaximum = dbo.PrintFormatTypes 。代码

I'm stuck

there are two fields in the table, of an equal type, joined by "code" field with another table, describing the code

Please suggest

how to create the view to show the description of these two fields from chained table instead of code.

SELECT        dbo.MFU.PrintFormatMaximum, dbo.MFU.PrintFormatMinimum
FROM            dbo.MFU LEFT OUTER JOIN
                         dbo.PrintFormatTypes ON dbo.MFU.PrintFormatMinimum = dbo.PrintFormatTypes.Code AND dbo.MFU.PrintFormatMaximum = dbo.PrintFormatTypes.Code

What I have tried:

forums,
TSQL,
SQL View,
MSDN

解决方案

Quote:

that is "one chained dictionary table <- many fields"

You need two JOINs, one for each column in the original table:

SELECT d1.field_description AS Field1, d2.field_description AS Field2 FROM Table_data t
JOIN Table_dictionary d1 ON t.field1 = d1.field_code
JOIN Table_dictionary d2 ON t.field2 = d2.field_code

--Normal view is invalid after dropping view dependency table.
--we can't drop view dependency table by using "WITH SCHEMABINDING" option in view.
    
CREATE view VName WITH SCHEMABINDING 
 AS 
SELECT  
     MFU.PrintFormatMaximum,
     MFU.PrintFormatMinimum

FROM  dbo.MFU LEFT OUTER JOIN dbo.PrintFormatTypes 
 
  ON dbo.MFU.PrintFormatMinimum = dbo.PrintFormatTypes.Code AND
     dbo.MFU.PrintFormatMaximum = dbo.PrintFormatTypes.Code

这篇关于MS SQL视图。两个相同的领域的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！