使用带有多个别名的 Outer Join 连接两个 SELECT 语句

Question

我有一个复杂的选择语句，当执行它时，我给它一个我想要的日期或日期范围，然后输出就出来了。问题是我不知道如何将同一个 SQL 语句与具有 1 个日期范围的第一个语句和具有另一个数据范围的第二个语句连接起来。下面的示例：

当我执行 select 语句时，我选择 11 月：
EMPLID NAME Current_Gross_Hours(November)

当我再次执行 select 语句时，我选择从一月到十一月：
EMPLID NAME Year_To_Date_Hours（一月 - 十一月）

我想要什么：
EMPLID NAME Current_Gross_Hours(11 月) Year_To_Date_Hours(1 月 - 11 月)

如果单独执行 SQL Select 语句，则该语句将正确运行。但我不知道如何加入他们。 这是我想写的SQL代码，但我不知道如何正确编写SQL语句。非常感谢任何帮助或指导。

(SELECT DISTINCT
SUM("PSA"."AL_HOURS") AS "Current Gross Hours", "PSJ"."EMPLID","PSP"."NAME"
FROM
"PS_JOB" "PSJ", "PS_EMPLOYMENT" "PSE", "PS_PERSONAL_DATA" "PSP", "PS_AL_CHK_HRS_ERN" "PSA"
WHERE
((("PSA"."CHECK_DT" = TO_DATE('2011-11-01', 'YYYY-MM-DD')) AND
("PSJ"."PAYGROUP" = 'SK2') AND
(("PSJ"."EFFSEQ"= ( 
    SELECT MAX("INNERALIAS"."EFFSEQ")
    FROM "PS_JOB" INNERALIAS 
    WHERE "INNERALIAS"."EMPL_RCD_NBR" = "PSJ"."EMPL_RCD_NBR"
    AND "INNERALIAS"."EMPLID" = "PSJ"."EMPLID"
    AND "INNERALIAS"."EFFDT" = "PSJ"."EFFDT")
AND
"PSJ"."EFFDT" = ( 
    SELECT MAX("INNERALIAS"."EFFDT")
    FROM "PS_JOB" INNERALIAS 
    WHERE "INNERALIAS"."EMPL_RCD_NBR" = "PSJ"."EMPL_RCD_NBR"
    AND "INNERALIAS"."EMPLID" = "PSJ"."EMPLID"
    AND "INNERALIAS"."EFFDT" <= SYSDATE))))) 
AND
("PSJ"."EMPLID" = "PSE"."EMPLID" ) AND ("PSJ"."EMPLID" = "PSP"."EMPLID" ) AND ("PSJ"."FILE_NBR" = "PSA"."FILE_NBR" ) AND ("PSJ"."PAYGROUP" = "PSA"."PAYGROUP" ) AND ("PSE"."EMPLID" = "PSP"."EMPLID" )
GROUP BY
"PSJ"."EMPLID", "PSP"."NAME"
) AS "Q1"

LEFT JOIN


(SELECT DISTINCT
SUM("PSA"."AL_HOURS") AS "YEAR_TO_DATE Gross Hours", "PSJ"."EMPLID"
FROM
"PS_JOB" "PSJ", "PS_EMPLOYMENT" "PSE", "PS_PERSONAL_DATA" "PSP", "PS_AL_CHK_HRS_ERN" "PSA"
WHERE
((("PSA"."CHECK_DT" BETWEEN TO_DATE('2011-01-01', 'YYYY-MM-DD') AND TO_DATE('2011-11-01', 'YYYY-MM-DD'))  AND
("PSJ"."PAYGROUP" = 'SK2') AND
(("PSJ"."EFFSEQ"= ( 
    SELECT MAX("INNERALIAS"."EFFSEQ")
    FROM "PS_JOB" INNERALIAS 
    WHERE "INNERALIAS"."EMPL_RCD_NBR" = "PSJ"."EMPL_RCD_NBR"
    AND "INNERALIAS"."EMPLID" = "PSJ"."EMPLID"
    AND "INNERALIAS"."EFFDT" = "PSJ"."EFFDT")
AND
"PSJ"."EFFDT" = ( 
    SELECT MAX("INNERALIAS"."EFFDT")
    FROM "PS_JOB" INNERALIAS 
    WHERE "INNERALIAS"."EMPL_RCD_NBR" = "PSJ"."EMPL_RCD_NBR"
    AND "INNERALIAS"."EMPLID" = "PSJ"."EMPLID"
    AND "INNERALIAS"."EFFDT" <= SYSDATE))))) 
AND
("PSJ"."EMPLID" = "PSE"."EMPLID" ) AND ("PSJ"."EMPLID" = "PSP"."EMPLID" ) AND ("PSJ"."FILE_NBR" = "PSA"."FILE_NBR" ) AND ("PSJ"."PAYGROUP" = "PSA"."PAYGROUP" ) AND ("PSE"."EMPLID" = "PSP"."EMPLID" )
GROUP BY
"PSJ"."EMPLID"
) AS "Q2"
 ON "Q1"."EMPLID"="Q2"."EMPLID"
ORDER BY
"Q1"."NAME"

Answer 1

您在开始时缺少 SELECT ... FROM 。 AS 关键字仅在创建列别名时有效，在创建查询块别名时无效。仅对于区分大小写的列名称才需要引号 - 它们在您的示例中看起来并不不正确，但它们经常会导致错误。

SELECT Q1.NAME, ...
FROM
(
     SELECT ...
) Q1
JOIN
(
     SELECT ...
) Q2
ON Q1.EMPLID=Q2.EMPLID
ORDER BY Q1.NAME