Oracle -基于子查询计数一次选择

Question

我需要根据主查询

计算子查询的结果我想数一下2021年有多少辆车。

我将如何生成这个查询的结果，其中一列计算年份为2021的每辆绿色汽车。在下面的示例

表1

<表类>ID车辆tbody><<tr>1汽车2摩托车3自行车

如果你想看到年份，车辆，颜色，应该是这样的

select t3.Year, t1.Vehicle, t2.color, COUNT(*)
from table1 t1 
inner join table2 t2 on t1.id = t2.id_table1 
inner join table3 t3 on t1.id = t3.id_table1 
GROUP BY t3.Year, t1.Vehicle, t2.color

如果你不需要一些文件-删除它在选择和分组。

但这不是最好的解决方案-当你在2021年没有销售绿色汽车(例如)-你不会在结果查询中看到它，这就是为什么我应该问:你需要看到它吗?(例如0而不是跳过这一行)

我对任务的理解正确吗?

看起来你想要outer apply():

select 
t1.Vehicle, t2.color, tbyear.*
from table1 t1 
inner join table2 t2 
on t1.id = t2.id_table1 
outer apply(
select count(*) cnt 
from table3 t3 
where t1.id = t3.id_table1 
and t3.year = 2020
) tbyear;

完整的测试数据示例:

with -- test data:
Table1(ID,Vehicle) as (
select 1, 'Car'        from dual union all
select 2, 'motorcycle' from dual union all
select 3, 'bicycle'    from dual 
)
,Table2(ID, ID_TABLE1, COLOR) as (
select 1, 2, 'RED'   from dual union all
select 2, 1, 'GREEN' from dual union all
select 3, 3, 'BLACK' from dual union all
select 4, 1, 'GREEN' from dual
)
,Table3(ID,ID_TABLE1,YEAR) as (
select 1, 2, 2021 from dual union all
select 2, 1, 2020 from dual union all
select 3, 3, 2021 from dual union all
select 3, 1, 2020 from dual
)
-- end test data
select 
t1.Vehicle, t2.color, tbyear.*
from table1 t1 
inner join table2 t2 
on t1.id = t2.id_table1 
outer apply(
select count(*) cnt 
from table3 t3 
where t1.id = t3.id_table1 
and t3.year = 2020
) tbyear 

结果:

EHICLE    COLOR        CNT
---------- ----- ----------
motorcycle RED            0
Car        GREEN          2
bicycle    BLACK          0
Car        GREEN          2