时间戳

首页 > 解决方案 > TSQL 2008 如何使视图的列唯一

tsql - TSQL 2008 如何使视图的列唯一

问题描述

我有一个查询，我试图将其保存为视图，但 SSMS 返回一个错误，指出有多个同名的列。

我试图将受影响的列重命名为别名，但没有成功。

每个视图或函数中的列名必须是唯一的。视图或函数“vwBlocks”中的列名“codemanQuestionID”被多次指定。

create view vwBlocks as


SELECT        

LUConstructionType.codemanOptionID AS LUConstruction_codemanOptionID

, LUFascias.codemanOptionID AS LUFascias_codemanOptionID
, Block.windowsID
, LUWindows.windows
, LUWindows.codemanQuestionID AS codemanQuestion_ID

, Block.externalDoorID
, LUExternalDoor.externalDoor
, LUExternalDoor.codemanOptionID as LUExternal_codemanOptionID

FROM            Block LEFT OUTER JOIN
                         LUOwnership ON Block.ownershipID = LUOwnership.ownershipID LEFT OUTER JOIN
                         LULocalAuthority ON Block.localAuthorityID = LULocalAuthority.authorityTypeID LEFT OUTER JOIN
                         LUConstructionType ON Block.constructionTypeID = LUConstructionType.constructionTypeID LEFT OUTER JOIN
                         LUTV ON Block.TVID = LUTV.TVID LEFT OUTER JOIN
                         LUSatellite ON Block.satelliteID = LUSatellite.satelliteID LEFT OUTER JOIN
                         LUPlayArea ON Block.playArea = LUPlayArea.playAreaID LEFT OUTER JOIN
                         LURoofCovering ON Block.roofCoveringID = LURoofCovering.roofCoveringID LEFT OUTER JOIN
                         LUFascias ON Block.fasciasID = LUFascias.fasciasID LEFT OUTER JOIN
                         LUWindows ON Block.windowsID = LUWindows.windowsID LEFT OUTER JOIN
                         LUExternalDoor ON Block.externalDoorID = LUExternalDoor.externalDoorID LEFT OUTER JOIN
                         LUcontractorInfo ON Block.contractorInfoID = LUcontractorInfo.contractorID LEFT OUTER JOIN
                         LUagentInfo ON Block.agentInfoID = LUagentInfo.agentID LEFT OUTER JOIN
                         LULandlord ON Block.LandlordID = LULandlord.landlordID LEFT OUTER JOIN
                         LUblockStatus ON Block.blockStatusID = LUblockStatus.blockStatusID LEFT OUTER JOIN
                         LUPropertyGroup ON Block.propertyGroup = LUPropertyGroup.propertyGroupID LEFT OUTER JOIN
                         LUCommunalBoilerType ON Block.communalBoilerType = LUCommunalBoilerType.communalBoilerID LEFT OUTER JOIN
                         LUExternalAreaManagedBy ON Block.externalAreaManagedBy = LUExternalAreaManagedBy.managedByID LEFT OUTER JOIN
                         LUgasBoilerMakeModel ON Block.CommBoilerMakeModelID = LUgasBoilerMakeModel.makeModelId LEFT OUTER JOIN
                         LUMaintenanceResp ON Block.maintenanceRepID = LUMaintenanceResp.maintenanceRepID

有人可以推荐一个解决方案吗？

感谢您提前提供任何帮助。

标签： tsqlview

解决方案

我没有看到 codemanQuestionID 两次。这里有一些建议可以帮助我避免和/或解决此类问题。

至少有四种方法可以给列取别名：

  1. (表达式) AS ((别名))

  2. ((别名)) = (表达式)

  3. WITH ((cte name)) ((alias1), (alias2),...

  4. FROM ((子查询)) AS ((alias1>),(alias2,...

AS是最差的IMO。它草率且令人困惑，尤其是当您不包含 AS 时。我通常采用的混叠样式是：

SELECT
   col1   = <expression>,
   col2   = <expression>,
   colABC = <expression>
FROM  schema.table1 AS t1
JOIN  schema.table2 AS t2;

这使得调试更容易。

推荐阅读