oracle sql 左连接和计数，求和，分组依据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27910455/
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
oracle sql left join and count, sum, group by
提问by milesmiles55
i want to calculate the project count, amount, and percent total for each question restricted to a given funder and fiscal year. for some reason when i run the query below, i'm not seeing the null values from the left join.
我想计算仅限于给定资助者和财政年度的每个问题的项目数量、金额和百分比。出于某种原因，当我运行下面的查询时，我没有看到左连接中的空值。
the relationship between projects and questions is that a question can have many projects. the questions table is linked by the objectives table.
项目和问题之间的关系是一个问题可以有多个项目。问题表由目标表链接。
UPDATED
更新
select
q.sp_question_id,
count(p.project_id) as projectCount,
sum(p.funding) as amount,
round(sum(p.funding)/sum(sum(p.funding)) over() *100) as percentTotal
from questions q
left join projects p on p.fiscal_year = q.fiscal_year
join objectives o on o.sp_objective_id = p.sp_objective_id
and o.sp_question_id = q.sp_question_id
and o.fiscal_year = p.fiscal_year
and o.fiscal_year = 2014
join funders f on p.funder_id = f.funder_id
where f.funder_short_name ='foo'
and q.fiscal_year = 2014
group by q.sp_question_id
order by q.sp_question_id;
questionId projectCount amount percentTotal
q1 14 54510 4
q2 29 1083598.72 76
q3 1 19900 1
q4 5 145631 10
q5 1 124999 9
q6 1 0 0
expected result
预期结果
questionId projectCount amount percentTotal
q1 14 54510 4
q2 29 1083598.72 76
q3 1 19900 1
q4 5 145631 10
q5 1 124999 9
q6 1 0 0
q7 <null> <null> <null>
query with left joins all the way down
使用左连接查询一直向下
select
q.sp_question_id,
count(p.project_id) as projectCount,
sum(p.funding) as amount,
round(sum(p.funding)/sum(sum(p.funding)) over() *100) as percentTotal
from questions q
left join projects p on p.fiscal_year = q.fiscal_year
left join objectives o on o.sp_objective_id = p.sp_objective_id
and o.sp_question_id = q.sp_question_id
and o.fiscal_year = p.fiscal_year
and o.fiscal_year = 2014
left join funders f on p.funder_id = f.funder_id
where f.funder_short_name ='foo'
and q.fiscal_year = 2014
group by q.sp_question_id
order by q.sp_question_id;
result
结果
questionId projectCount amount percentTotal
na 51 1428638.72 11
q1 51 1428638.72 11
q2 51 1428638.72 11
q3 51 1428638.72 11
q4 51 1428638.72 11
q5 51 1428638.72 11
q6 51 1428638.72 11
q7 51 1428638.72 11
qother 51 1428638.72 11
query with left joins all the way down and where clauses adjusted
一直使用左连接查询并调整 where 子句
select
q.sp_question_id,
count(p.project_id) as projectCount,
sum(p.funding) as amount,
round(sum(p.funding)/sum(sum(p.funding)) over() *100) as percentTotal
from questions q
left join projects p on p.fiscal_year = q.fiscal_year
left join objectives o on o.sp_objective_id = p.sp_objective_id
and o.sp_question_id = q.sp_question_id
and o.fiscal_year = p.fiscal_year and o.fiscal_year = 2014
left join funders f on p.funder_id = f.funder_id and f.funder_short_name ='foo'
where q.fiscal_year = 2014
group by q.sp_question_id
order by q.sp_question_id;
result
结果
questionId projectCount amount percentTotal
na 1225 299628985.01 11
q1 1225 299628985.01 11
q2 1225 299628985.01 11
q3 1225 299628985.01 11
q4 1225 299628985.01 11
q5 1225 299628985.01 11
q6 1225 299628985.01 11
q7 1225 299628985.01 11
qother 1225 299628985.01 11
采纳答案by milesmiles55
i ended up doing something ghastly like below by left joining two subqueries. if someone has a better way of doing this, please post.
我通过左加入两个子查询，最终做了一些可怕的事情，如下所示。如果有人有更好的方法来做到这一点，请张贴。
select t1.sp_question_id, t2.projectCount, t2.amount, t2.percentTotal
from (select
q.sp_question_id
from questions q
where q.fiscal_year = 2014) t1
left join
(select
q.sp_question_id,
count(p.project_id) as projectCount,
sum(p.funding) as amount,
round(sum(p.funding)/sum(sum(p.funding)) over() *100) as percentTotal
from questions q
left join projects p on p.fiscal_year = q.fiscal_year
join objectives o on o.sp_objective_id = p.sp_objective_id
and o.sp_question_id = q.sp_question_id
and o.fiscal_year = p.fiscal_year
join funders f on p.funder_id = f.funder_id
where f.funder_short_name ='foo'
and q.fiscal_year = 2014
group by q.sp_question_id
order by q.sp_question_id) t2
on t1.sp_question_id = t2.sp_question_id
回答by Sebas
The problem you're having relates to one of the most unknown problems of sql: the transitivity of relationships.
您遇到的问题与 sql 中最不为人知的问题之一有关：关系的传递性。
You're doing an optional (outer) join between projects and questions, but you're asking for a mandatory (inner join) relationship between projects and objectives (and funders):
您正在项目和问题之间进行可选（外部）连接，但您要求项目和目标（和资助者）之间存在强制性（内部连接）关系：
The transitivity system which calculates the intermediary resultsets gives prevalence to the inner join, which as a result basically means the outer join in the middle is ignored. To be exact, it is not ignored, but it becomes an inner join instead.
计算中间结果集的传递系统普遍采用内连接，结果基本上意味着中间的外连接被忽略。确切地说，它没有被忽略，而是变成了一个内连接。
What you get is the result of an inner join all the way down, while you're actually expecting the left join to behave as is and return null rows for the questions not related to any projects... But the engine does not work like this, as described above.
你得到的是内部联接的结果，而你实际上期望左联接按原样运行，并为与任何项目无关的问题返回空行......但引擎不像这，如上所述。
Out of my head, I just think you could use left joins all the way down. But then you probably would face more null rows than you expect. It really depends what you're doing with the data.
在我的脑海里，我只是认为你可以一直使用左连接。但是，您可能会遇到比您预期更多的空行。这实际上取决于您对数据的处理方式。
Also, it seems there's a broad join in your query that should be modified. Please try this:
此外，您的查询中似乎有一个广泛的联接需要修改。请试试这个：
select
q.sp_question_id,
count(p.project_id) as projectCount,
sum(p.funding) as amount,
round(sum(p.funding)/sum(sum(p.funding)) over() *100) as percentTotal
from questions q
left join objectives o on
o.sp_question_id = q.sp_question_id
and o.fiscal_year = 2014
left join projects p on o.fiscal_year = p.fiscal_year and o.sp_objective_id = p.sp_objective_id
left join funders f on p.funder_id = f.funder_id and f.funder_short_name ='foo'
where q.fiscal_year = 2014
group by q.sp_question_id
order by q.sp_question_id;
