IF EXISTS(SELECT name FROM sysobjects WHERE name='V_Affiche_AfficheType' AND type='V')
DROP VIEW V_Affiche_AfficheType
GO
CREATE VIEW V_Affiche_AfficheType
AS
SELECT A.[ID],A.[Update],A.Title,T.[ID] AS TID,T.[Name] AS TNAME,U.[NAME] AS UNAME,
A.Content,A.AuthorID,A.PopedomOrg,A.PopedomGroup,A.AccessLink,
A.Status2,A.ORG_GROUP_IDS,A.Status AS Status1,
DATENAME(YEAR,StartDate)+'-'+DATENAME(MONTH,StartDate)+'-'+DATENAME(DAY,StartDate) AS StartDate,
DATENAME(YEAR,EndDate)+'-'+DATENAME(MONTH,EndDate)+'-'+DATENAME(DAY,EndDate) AS EndDate,
CASE A.Status2
WHEN '1' THEN '<font color="red">!</font>'END
AS 'Status_2',
CASE A.Status
WHEN '0' THEN '<img src="../../images/gongwen1.gif" width="17" height="16" alt="停用">'
WHEN '1' THEN '<img src="../../images/gongwen.gif" width="17" height="16" alt="启用">'
END
AS 'Status_1',
Status_2+Status_1 AS 'Status_3'
FROM Affiche AS A
INNER JOIN AfficheType AS T ON A.TypeID=T.[ID]
LEFT JOIN webflow..USERS AS U ON A.AuthorID=U.USERID
GO
如上SQL语句,我要把列的别名 Status_1和Status_2合并连接,但提示说这两个列名无效..这是为何?有什么办法可以解决吗?
DROP VIEW V_Affiche_AfficheType
GO
CREATE VIEW V_Affiche_AfficheType
AS
SELECT A.[ID],A.[Update],A.Title,T.[ID] AS TID,T.[Name] AS TNAME,U.[NAME] AS UNAME,
A.Content,A.AuthorID,A.PopedomOrg,A.PopedomGroup,A.AccessLink,
A.Status2,A.ORG_GROUP_IDS,A.Status AS Status1,
DATENAME(YEAR,StartDate)+'-'+DATENAME(MONTH,StartDate)+'-'+DATENAME(DAY,StartDate) AS StartDate,
DATENAME(YEAR,EndDate)+'-'+DATENAME(MONTH,EndDate)+'-'+DATENAME(DAY,EndDate) AS EndDate,
CASE A.Status2
WHEN '1' THEN '<font color="red">!</font>'END
AS 'Status_2',
CASE A.Status
WHEN '0' THEN '<img src="../../images/gongwen1.gif" width="17" height="16" alt="停用">'
WHEN '1' THEN '<img src="../../images/gongwen.gif" width="17" height="16" alt="启用">'
END
AS 'Status_1',
Status_2+Status_1 AS 'Status_3'
FROM Affiche AS A
INNER JOIN AfficheType AS T ON A.TypeID=T.[ID]
LEFT JOIN webflow..USERS AS U ON A.AuthorID=U.USERID
GO
如上SQL语句,我要把列的别名 Status_1和Status_2合并连接,但提示说这两个列名无效..这是为何?有什么办法可以解决吗?
这个没要达到我的效果..我要的是 Status_1里面的值的组合
IF EXISTS(SELECT name FROM sysobjects WHERE name='V_Affiche_AfficheType' AND type='V')
DROP VIEW V_Affiche_AfficheType
GO
CREATE VIEW V_Affiche_AfficheType
AS
SELECT *, Status_2+Status_1 AS Status_3
From
(
SELECT A.[ID],A.[Update],A.Title,T.[ID] AS TID,T.[Name] AS TNAME,U.[NAME] AS UNAME,
A.Content,A.AuthorID,A.PopedomOrg,A.PopedomGroup,A.AccessLink,
A.Status2,A.ORG_GROUP_IDS,A.Status AS Status1,
DATENAME(YEAR,StartDate)+'-'+DATENAME(MONTH,StartDate)+'-'+DATENAME(DAY,StartDate) AS StartDate,
DATENAME(YEAR,EndDate)+'-'+DATENAME(MONTH,EndDate)+'-'+DATENAME(DAY,EndDate) AS EndDate,
CASE A.Status2
WHEN '1' THEN '<font color="red">!</font>'END
AS Status_2,
CASE A.Status
WHEN '0' THEN '<img src="../../images/gongwen1.gif" width="17" height="16" alt="停用">'
WHEN '1' THEN '<img src="../../images/gongwen.gif" width="17" height="16" alt="启用">'
END
AS Status_1
FROM Affiche AS A
INNER JOIN AfficheType AS T ON A.TypeID=T.[ID]
LEFT JOIN webflow..USERS AS U ON A.AuthorID=U.USERID
) A
GO
IF EXISTS(SELECT name FROM sysobjects WHERE name='V_Affiche_AfficheType' AND type='V')
DROP VIEW V_Affiche_AfficheType
GO
CREATE VIEW V_Affiche_AfficheType
AS
SELECT A.[ID],A.[Update],A.Title,T.[ID] AS TID,T.[Name] AS TNAME,U.[NAME] AS UNAME,
A.Content,A.AuthorID,A.PopedomOrg,A.PopedomGroup,A.AccessLink,
A.Status2,A.ORG_GROUP_IDS,A.Status AS Status1,
DATENAME(YEAR,StartDate)+'-'+DATENAME(MONTH,StartDate)+'-'+DATENAME(DAY,StartDate) AS StartDate,
DATENAME(YEAR,EndDate)+'-'+DATENAME(MONTH,EndDate)+'-'+DATENAME(DAY,EndDate) AS EndDate,
CASE A.Status2
WHEN '1' THEN '<font color="red">!</font>'END
AS Status_2,
CASE A.Status
WHEN '0' THEN '<img src="../../images/gongwen1.gif" width="17" height="16" alt="停用">'
WHEN '1' THEN '<img src="../../images/gongwen.gif" width="17" height="16" alt="启用">'
END
AS Status_1,
(CASE A.Status2
WHEN '1' THEN '<font color="red">!</font>'END)
+
(CASE A.Status
WHEN '0' THEN '<img src="../../images/gongwen1.gif" width="17" height="16" alt="停用">'
WHEN '1' THEN '<img src="../../images/gongwen.gif" width="17" height="16" alt="启用">'
END) AS Status_3
FROM Affiche AS A
INNER JOIN AfficheType AS T ON A.TypeID=T.[ID]
LEFT JOIN webflow..USERS AS U ON A.AuthorID=U.USERID
GO
'Status_3'是这个不要加引号吗?也不行啊,还是一样的错误
------------
不是這個原因造成的,錯誤的原因在於“計算列的列名不能直接拿來用”。用上面我寫的兩種方法修改都可以。
WHEN '0' Then '<font color="red">!</font>' + '<img src="../../images/gongwen1.gif" width="17" height="16" alt="停用">'
end
end
as 'Status_3'
DROP VIEW V_Affiche_AfficheType
GO
CREATE VIEW V_Affiche_AfficheType
AS
SELECT A.[ID],A.[Update],A.Title,T.[ID] AS TID,T.[Name] AS TNAME,U.[NAME] AS UNAME,
A.Content,A.AuthorID,A.PopedomOrg,A.PopedomGroup,A.AccessLink,
A.Status2,A.ORG_GROUP_IDS,A.Status AS Status1,
DATENAME(YEAR,StartDate)+'-'+DATENAME(MONTH,StartDate)+'-'+DATENAME(DAY,StartDate) AS StartDate,
DATENAME(YEAR,EndDate)+'-'+DATENAME(MONTH,EndDate)+'-'+DATENAME(DAY,EndDate) AS EndDate, CASE A.Status2
WHEN '1' THEN '<font color="red">!</font>'
END
+
CASE A.Status
WHEN '0' THEN '<img src="../../images/gongwen1.gif" width="17" height="16" alt="停用">'
WHEN '1' THEN '<img src="../../images/gongwen.gif" width="17" height="16" alt="启用">'
END AS 'Status_3' FROM Affiche AS A
INNER JOIN AfficheType AS T ON A.TypeID=T.[ID]
LEFT JOIN webflow..USERS AS U ON A.AuthorID=U.USERID
GO