我有一个POST表，一个CATEGORY表，一个ACTION表和ACTION_TYPE表，我解释ACTION表包含所有的动作，表ACTION_TYPE包含动作的细节，例如，ID = 4的ACTION有ACTION_TYPE_ID = 1的POST_ID 6，这意味着一个动作是为POST number 50做的，我们可以有许多动作对于一个POST_ID
id title content category_id
---------- ---------- ---------- ------------
1 title1 Text... 1
2 title2 Text... 1
3 title3 Text... 1
4 title4 Text... 3
5 title5 Text... 2
6 title6 Text... 1
CATEGORY表
id name
---------- ----------
1 category_1
2 category_2
3 category_3
ACTION_TYPE表
id name
---------- ----------
1 updated
2 deleted
3 restored
4 hided
ACTION表
id post_id action_type_id date
---------- ---------- -------------- -----
1 1 1 2017-01-01
2 1 1 2017-02-15
3 1 3 2018-06-10
4 6 1 2019-08-01
5 5 2 2019-12-09
6 2 3 2020-04-27
7 2 1 2020-07-29
8 3 2 2021-03-13
现在我解释这种情况，我实际上有两个查询一个查询用于计算每个类别的帖子，另一个查询用于计算按类别对每个帖子执行的操作，这些操作运行良好。
这是我的第一个查询
select categories, count(*) as cnt_posts_per_cat
from(
select
case
when p.category_id is not null then c.name
end as categories
from post p
left join category c on p.category _id = c.id
)
group by categories
;
结果
categories cnt_posts_per_cat
---------- -------------------
category_1 4
category_2 1
category_3 1
这是我的第二个查询
select categories, count(*) as cnt_actions_per_cat
from(
select distinct ac.post_id AS action_post_id, max(ac.date) over (partition by ac.post_id) as max_date,
case
when ac.action_type_id is not null then act.name
end as actions,
case
when p.category_id is not null then c.name
else 'na'
end as categories
from action ac
left join post p on ac.post_id = p.id
left join category c on p.category _id = c.id
left join action_type act on ac.action_type_id = act.id
where act.name in ('restored','deleted','updated')
)
group by categories
;
会得到正确的结果因为它隔离了每个action_type
的最后一个动作categories cnt_actions_per_cat
---------- -------------------
category_1 3
category_2 1
category_3 na
但我想有一个单一的结果表，两个查询在同一时间如下:这里的结果应该是
categories cnt_posts_per_cat cnt_actions_per_cat
---------- ----------------- -------------------
category_1 4 3
category_2 1 1
category_3 1 na
我正在尝试联合和联合所有，但它是不正确的，它返回以下结果
categories cnt_posts_per_cat
---------- -----------------
category_1 7
category_2 2
category_3 1
关联子查询呢?
样本数据:
SQL> with
2 post (id, category_id) as
3 (select 1, 1 from dual union all
4 select 2, 1 from dual union all
5 select 3, 1 from dual union all
6 select 4, 3 from dual union all
7 select 5, 2 from dual union all
8 select 6, 1 from dual
9 ),
10 category (id, name) as
11 (select 1, 'category_1' from dual union all
12 select 2, 'category_2' from dual union all
13 select 3, 'category_3' from dual
14 ),
15 action_type (id, name) as
16 (select 1, 'updated' from dual union all
17 select 2, 'deleted' from dual union all
18 select 3, 'restored' from dual union all
19 select 4, 'hided' from dual
20 ),
21 action (id, post_id, action_type_id) as
22 (select 1, 1, 1 from dual union all
23 select 2, 1, 1 from dual union all
24 select 3, 1, 3 from dual union all
25 select 4, 6, 1 from dual union all
26 select 5, 5, 2 from dual union all
27 select 6, 2, 3 from dual union all
28 select 7, 2, 1 from dual union all
29 select 8, 3, 2 from dual
30 )
查询从这里开始:
31 select c.name,
32 --
33 (select count(*)
34 from post p
35 where p.category_id = c.id
36 ) cnt_posts_per_cat,
37 --
38 (select count(*)
39 from action a join post p on p.id = a.post_id
40 join action_type t on t.id = a.id
41 where p.category_id = c.id
42 and t.name in ('restored', 'deleted', 'updated')
43 ) cnt_actions_per_cat
44 from category c
45 order by c.name;
NAME CNT_POSTS_PER_CAT CNT_ACTIONS_PER_CAT
---------- ----------------- -------------------
category_1 4 3
category_2 1 0
category_3 1 0
SQL>