sql - 创建视图表

Question

我正在尝试使用UNION ALL创建视图表。但我有一个错误。下面是我的SQL代码：

CREATE VIEW dbo.v_DemographicInfo 
AS
(
SELECT 'HSA' AS HospitalName, HSA.*,P.*,S.*,E.*,
CASE WHEN DATEDIFF(YEAR, BIRTHDATE, CURRENT_TIMESTAMP)<18  THEN 'AGE<18'
     WHEN DATEDIFF(YEAR, BIRTHDATE, CURRENT_TIMESTAMP)>=18  AND DATEDIFF(YEAR, BIRTHDATE, CURRENT_TIMESTAMP)<40  THEN '18<=AGE<40'
     WHEN DATEDIFF(YEAR, BIRTHDATE, CURRENT_TIMESTAMP)>=40  AND DATEDIFF(YEAR, BIRTHDATE, CURRENT_TIMESTAMP)<60  THEN '40<=AGE<60'
ELSE '60<=AGE'
END AS AGE_CATEGORY
FROM  [HSA_Careman].[dbo].[ADMISSIONS] HSA
JOIN [HSA_Careman].[dbo].[PATIENTS] P ON HSA.PATIENTDBOID=P.PATIENTDBOID
JOIN [HSA_Careman].[dbo].[SEXES] S ON P.SEXDBOID=S.SEXDBOID
JOIN [HSA_Careman].[dbo].[ETHNICITIES] E ON E.ETHNICITYDBOID=P.ETHNICITYDBOID
 
UNION ALL
    
SELECT 'HTAR' AS HospitalName, HTAR.*,P.*,S.*,E.*,
CASE WHEN DATEDIFF(YEAR, BIRTHDATE, CURRENT_TIMESTAMP)<18  THEN 'AGE<18'
     WHEN DATEDIFF(YEAR, BIRTHDATE, CURRENT_TIMESTAMP)>=18  AND DATEDIFF(YEAR, BIRTHDATE, CURRENT_TIMESTAMP)<40  THEN '18<=AGE<40'
     WHEN DATEDIFF(YEAR, BIRTHDATE, CURRENT_TIMESTAMP)>=40  AND DATEDIFF(YEAR, BIRTHDATE, CURRENT_TIMESTAMP)<60  THEN '40<=AGE<60'
ELSE '60<=AGE'
END AS AGE_CATEGORY
FROM  [HTAR_Careman].[dbo].[ADMISSIONS] HTAR
JOIN [HTAR_Careman].[dbo].[PATIENTS] P ON HTAR.PATIENTDBOID=P.PATIENTDBOID
JOIN [HTAR_Careman].[dbo].[SEXES] S ON P.SEXDBOID=S.SEXDBOID
JOIN [HTAR_Careman].[dbo].[ETHNICITIES] E ON E.ETHNICITYDBOID=P.ETHNICITYDBOID
)  

执行代码后出现的错误：

Column names in each view or function must be unique. Column name 'PATIENTDBOID' in view or function 'v_DemographicInfo' is specified more than once.

标签： sqlsql-servercreate-view