链接联接未按预期过滤 [英] Chained join not filtering as expected
问题描述
我在这里有一个相关的问题:
I have a related question here:
当我发布帖子时，我忽略了第二个联接，认为它是微不足道的并且不会影响我的问题-在那里我错了.
When I posted I omitted the second join thinking it was trivial and not impact my question - I was wrong there.
SELECT TO_CHAR(activity_tasks.start_date, 'MON') AS month,
EXTRACT(MONTH FROM activity_tasks.start_date) AS month_num,
SUM(cost_planned) FILTER (WHERE activity_tasks.aasm_state IN ('open', 'planned' ) ) AS planned,
SUM(cost_actual) FILTER (WHERE activity_tasks.aasm_state IN ('closed' ) ) AS actual
FROM "activity_tasks"
INNER JOIN "activities"."id" = "activity_tasks"."activity_id"
WHERE "activities"."type" = 'My Type' AND activity_tasks.start_date >= '2020-01-01' AND activity_tasks.start_date <= '2020-12-31'
GROUP BY month, month_num
ORDER BY month_num
哪个会吸引我:
+-------+-----------+---------+--------+
| month | month_num | planned | actual |
+-------+-----------+---------+--------+
| NOV | 11 | NULL | 123 |
| DEC | 12 | 500 | NULL |
+-------+-----------+---------+--------+
相关的公认答案让我尝试了各种修改，例如添加:
The accepted answer on the related had me try various modifications like adding:
LEFT JOIN activities a
ON a.id = t.activity_id AND a.type = 'My Type'
这会导致(不只是滤除My Type):
which results in (not filtering out just My Type):
+-------+-----------+---------+--------+
| month | month_num | planned | actual |
+-------+-----------+---------+--------+
| JAN | 1 | NULL | NULL |
| FEB | 2 | NULL | NULL |
| MAR | 3 | NULL | NULL |
| APR | 4 | NULL | NULL |
| MAY | 5 | NULL | NULL |
| JUN | 6 | NULL | NULL |
| JUL | 7 | NULL | NULL |
| AUG | 8 | NULL | NULL |
| SEP | 9 | NULL | NULL |
| OCT | 10 | 0 | 0 |
| NOV | 11 | 125 | 123 |
| DEC | 12 | 1414 | 0 |
+-------+-----------+---------+--------+
尝试过:
ON a.id = t.activity_id
WHERE a.type = 'My Type'
哪些可以为我提供正确的过滤结果，但我又会丢失过滤结果:
Which gets me the correct filtered results but I lose the filtered results again:
+-------+-----------+---------+--------+
| month | month_num | planned | actual |
+-------+-----------+---------+--------+
| NOV | 11 | NULL | 123 |
| DEC | 12 | 500 | NULL |
+-------+-----------+---------+--------+
我正在寻找-过滤后的任务也为"null"个月:
I am looking for this - the filtered tasks with the 'null' months also:
+-------+-----------+---------+--------+
| month | month_num | planned | actual |
+-------+-----------+---------+--------+
| JAN | 1 | NULL | NULL |
| FEB | 2 | NULL | NULL |
| MAR | 3 | NULL | NULL |
| APR | 4 | NULL | NULL |
| MAY | 5 | NULL | NULL |
| JUN | 6 | NULL | NULL |
| JUL | 7 | NULL | NULL |
| AUG | 8 | NULL | NULL |
| SEP | 9 | NULL | NULL |
| OCT | 10 | NULL | NULL |
| NOV | 11 | NULL | 123 |
| DEC | 12 | 500 | NULL |
+-------+-----------+---------+--------+
我尝试了所有类型的JOINS进行第二次联接.不知道我在这里想念什么.
I have tried all types of JOINS for the second join. Not sure what I am missing here.
推荐答案
意识到我需要这样的子选择:
Realized I need a sub-select like this:
SELECT
UPPER(TO_CHAR(d.start_date, 'mon')) AS month,
EXTRACT(MONTH FROM d.start_date) AS month_num,
SUM(t.cost_planned) FILTER (WHERE t.aasm_state IN ('open', 'planned' ) ) AS planned,
SUM(t.cost_actual) FILTER (WHERE t.aasm_state = 'closed') AS actual
FROM GENERATE_SERIES('2020-01-01'::date, '2020-12-01'::date, '1 month') d(start_date)
LEFT JOIN
(
SELECT t.*
FROM activity_tasks t
LEFT JOIN activities a
ON a.id = t.activity_id
WHERE a.type = 'My Type'
) AS t ON t.start_date >= d.start_date and t.start_date < d.start_date + '1 month'::interval
GROUP BY d.start_date
ORDER BY d.start_date
这篇关于链接联接未按预期过滤的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
