时间戳

首页 > 解决方案 > SQL - 子选择、联合和使用函数

sql - SQL - 子选择、联合和使用函数

问题描述

我有 2 个查询，1 个用于导出整体订单，另一个用于获取已交付的订单。列名与订单和值相同。只有条件在子查询中会有所不同。我想分别获取交付的订单和价值以及单独的整体订单和价值。之后，我想将交付的价值与总价值相除，并将其作为百分比值。只有一张桌子，只是条件不同。我希望从 2021 年 1 月到现在这个月明智。这是我尝试过的不起作用的示例。请帮我。

例子：

Select
a.C_Month,
a.C_Orders/B_Orders as per_orders,
a.C_Value/B_Value as per_Value

from(
select 
to_char(estDate,'yyyymm') as B_Month,
null as area,
count(distinct order_id) as B_Orders,
sum (Value) as B_Value 

from table1
where condition1 is not null
and condition2 is ='1'
and to_char(estDate,'yyyymm')>= '202101' 

group by to_char(estDate,'yyyymm')
order by to_char(estDate,'yyyymm')

union

select
to_char(estDate,'yyyymm') as C_Month,
area,
count(distinct order_id) as C_Orders,
sum (Value) as C_Value 

from table1
where condition1 is not null
and condition2 is ='1'
where condition3 is not null
where condition4 is not null

and to_char(estDate,'yyyymm')>= '202101' 

group by to_char(estDate,'yyyymm')
order by to_char(estDate,'yyyymm')

) as a

group by

a.C_Month,
a.C_Orders/B_Orders as per_orders,
a.C_Value/B_Value as per_Value
;

标签： sqlunionsql-subselect

解决方案

首先，在您的示例中，您要求“JOIN”，但您创建了“UNION”：

加入 VS 联盟

尝试遵循您的逻辑，您可能需要一个 self-join on B_Month = C_Month。

询问：

  Select
    a.C_Month,
    a.C_Orders/aa.B_Orders as per_orders,
    a.C_Value/aa.B_Value as per_Value
    
    from(
    (select 
    to_char(estDate,'yyyymm') as B_Month,
    null as area,
    count(distinct order_id) as B_Orders,
    sum (Value) as B_Value 
    
    from table1
    where condition1 is not null
    and condition2 is ='1'
    and to_char(estDate,'yyyymm')>= '202101' ) as aa
    
    INNER JOIN
    
    (select
    to_char(estDate,'yyyymm') as C_Month,
    area,
    count(distinct order_id) as C_Orders,
    sum (Value) as C_Value 
    
    from table2
    where condition1 is not null
    and condition2 is ='1'
    where condition3 is not null
    where condition4 is not null
    
    and to_char(estDate,'yyyymm')>= '202101' ) as a
          
    ON aa.B_Month=a.C_Month

该查询将 table1 的每一行与 table2 的每一行进行比较，以找到满足连接谓词的所有行对。

请记住，“INNER JOIN”可以根据您想要的输出替换为其他谓词（LEFT JOIN、RIGHT JOIN、OUTER JOIN、FULL JOIN）。

推荐阅读