ORA- 00918 :列明确定义为00918。00000 -有什么建议吗？

Question

WITH MYCTE AS
(
    SELECT  
        ENGINEERING.ENG_ID, ENGINEERING.NAME,
        ENGINEERING.SALARY, 
        MARKETING.MARKET_ID, MARKETING.NAME,
        MARKETING.SALARY 
    FROM 
        ENGINEERING 
    JOIN 
        MARKETING ON MARKETING.MARKET_ID = ENGINEERING.ENG_ID
)
SELECT 
    EMPLOYEE_ID, SUM(SALARY) TOTAL_SAL
FROM 
    EMPLOYEES S
JOIN 
    MYCTE K ON K.ENG_ID = S.EMPLOYEE_ID
GROUP BY 
    EMPLOYEE_ID

Answer 1

在cte中，您有重复的薪资列名，您的查询返回错误的模糊别名，您engineering.salary为engineering_salary，marketing.salary为marketing_salary，或者运行此查询。

WITH MYCTE AS (
SELECT 
ENGINEERING.ENG_ID as eng_id,
ENGINEERING.NAME as engineering_name,
ENGINEERING.SALARY as engineering_salary
,MARKETING.MARKET_ID as market_id,
MARKETING.NAME as marketing_name,
MARKETING.SALARY as marketing_salary
FROM ENGINEERING
JOIN 
MARKETING
 ON MARKETING.MARKET_ID=ENGINEERING.ENG_ID) 

SELECT EMPLOYEE_ID,
SUM(marketing_salary + engineering_salary ) TOTAL_SAL
 FROM EMPLOYEES S 
JOIN 
MYCTE K 
ON K.ENG_ID=S.EMPLOYEE_ID
 GROUP BY EMPLOYEE_ID;