在带有连接关系的 JOIN 和 top1 上嵌套查询

Question

我正在尝试使用以下 SQL 查询 1 的结果，以便我可以使用我的第二个查询结果对此进行另一个 JOIN，以在公共 ID - Project 上检索 Fundsrc。

查询 1-

SELECT top 1 with ties
t.project, r.rel_value AS "FundSrc" ,r.date_to
from atsproject t 
LEFT OUTER JOIN aglrelvalue r ON(t.client=r.client AND r.rel_attr_id='ZB18' AND r.attribute_id='B0' AND t.project=r.att_value) 
WHERE  r.date_To > '04/30/2020' and status='n'
ORDER BY row_number() over (partition by t.project order by t.project, r.rel_value)

我无法将 JOIN 放在上述查询中，因为它会弄乱结果。相反，如果我可以在上面做一个嵌套，那么我认为这应该可以解决问题。

我的第二个问题是——

SELECT 
t.project,t.work_order as activity,  r1.labor_funding_source2_fx AS "Designated Labour Funding"
FROM atsworkorder t 
LEFT OUTER JOIN afxactlaborfund r1 ON( t.work_order = r1.dim_value AND t.client = r1.client AND r1.attribute_id = 'BF') 
WHERE t.client='PC' and t.status = 'N'

输出应为 - t.project，t.work_order 来自查询 2 + 来自查询 1 的 Fundsrc，在项目 ID 上具有通用 ID。

对此的任何建议都非常感谢。

Answer 1

您可以将"子查询"括在括号中，然后连接它们。

你能试试这个吗？

SELECT *
FROM (
SELECT top 1 with ties t.project,
r.rel_value AS "FundSrc",
r.date_to
FROM atsproject t
LEFT OUTER JOIN aglrelvalue r
ON t.client=r.client
AND r.rel_attr_id='ZB18'
AND r.attribute_id='B0'
AND t.project=r.att_value
WHERE r.date_To > '04/30/2020' and status='n'
ORDER BY row_number() over (partition by t.project order by t.project, r.rel_value)
) AS TABLE_1
LEFT JOIN 
(
SELECT t.project,
t.work_order as activity,
r1.labor_funding_source2_fx AS "Designated Labour Funding"
FROM atsworkorder t
LEFT OUTER JOIN afxactlaborfund r1
ON t.work_order = r1.dim_value
AND t.client = r1.client
AND r1.attribute_id = 'BF'
WHERE t.client='PC' and t.status = 'N' 
) AS TABLE_2
ON TABLE_1.PROJECT = TABLE2.PROJECT

我很确定ORDER BY子句在子查询中不起作用。因此，这可能应该有效：

SELECT *
FROM (
SELECT t.project,
r.rel_value AS "FundSrc",
r.date_to,
row_number() over (partition by t.project order by t.project, r.rel_value) AS MY_RANKING
FROM atsproject t
LEFT OUTER JOIN aglrelvalue r
ON t.client=r.client
AND r.rel_attr_id='ZB18'
AND r.attribute_id='B0'
AND t.project=r.att_value
WHERE r.date_To > '04/30/2020' and status='n'
) AS TABLE_1
LEFT JOIN 
(
SELECT t.project,
t.work_order as activity,
r1.labor_funding_source2_fx AS "Designated Labour Funding"
FROM atsworkorder t
LEFT OUTER JOIN afxactlaborfund r1
ON t.work_order = r1.dim_value
AND t.client = r1.client
AND r1.attribute_id = 'BF'
WHERE t.client='PC' and t.status = 'N' 
) AS TABLE_2
ON TABLE_1.PROJECT = TABLE2.PROJECT
WHERE TABLE_1.MY_RANKING = 1

注意：在格式设置上，当单词引用代码时，将单词括在 ' 内。它们看起来像this. 将代码块包装在其中的三个(开头和结尾三个(。它看起来像上面的代码块。