oracle 使用具有多个别名的外部连接连接两个 SELECT 语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8144877/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Joining two SELECT statements using Outer Join with multiple alias
提问by user1048616
I have a complicated select statement that when it is executed, I give it a date or date range I want and the output comes out. The problem is I don't know how to join the same SQL statement with the first Statement having 1 date range and the second statement having another data range. Example below:
我有一个复杂的 select 语句，当它被执行时，我给它一个我想要的日期或日期范围，然后输出出来。问题是我不知道如何将相同的 SQL 语句与具有 1 个日期范围的第一个语句和具有另一个数据范围的第二个语句连接起来。下面的例子：
When I execute the select statement, I choose for the Month of November:
EMPLID NAME Current_Gross_Hours(November)
当我执行 select 语句时，我选择了 11 月：
EMPLID NAME Current_Gross_Hours(November)
When I execute the select statement again, I choose from January to November:
EMPLID NAME Year_To_Date_Hours(January - November)
当我再次执行select语句时，我选择从一月到十一月：
EMPLID NAME Year_To_Date_Hours(January - November)
What I want:
EMPLID NAME Current_Gross_Hours(November) Year_To_Date_Hours(January - November)
我想要的是：
EMPLID NAME Current_Gross_Hours(November) Year_To_Date_Hours(January - November)
The SQL Select statement runs correctly if execute by themselves. But I don't know how to join them. Here is the SQL code that I want to write, but I don't know how to write the SQL statement correctly. Any help or direction is greatly appreciated.
如果 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"
回答by Mark Byers
You are missing a SELECT ... FROMat the start. The ASkeyword only works when creating column aliases, not query block aliases. Quotation marks are only necessary for case-sensitive column names - they don't look incorrect in your example but they frequently cause mistakes.
你一SELECT ... FROM开始就缺少一个。该AS关键字仅在创建列别名时有效，在查询块别名时无效。引号仅对区分大小写的列名是必需的——它们在您的示例中看起来并不正确，但它们经常导致错误。
SELECT Q1.NAME, ...
FROM
(
SELECT ...
) Q1
JOIN
(
SELECT ...
) Q2
ON Q1.EMPLID=Q2.EMPLID
ORDER BY Q1.NAME
