时间戳

首页 > 解决方案 > 加入两个表，放置条件以创建新列并分组

sql - 加入两个表，放置条件以创建新列并分组

问题描述

接下来我想做：

我有一张由两张桌子组成的桌子。脚本如下：

SELECT 
    L.LSCRV, C.POSIC_ACTUAL, 
    C.IMP_CMA, C.LIMIT
FROM 
    ADC.RV L 
LEFT JOIN 
    ADC.CCB C ON SUBSTR(L.CLIENT, 1, 12) = C.CLIENT

我现在想添加一些条件以创建名为 CONDITION 的新列

case when C.POSIC_ACTUAL = '10' then 1 else 0 end 
as CONDITION

最后我想按 LSCRV 和 CONDITION 分组

输出表应与这些列一起出现：

LSCRV    CONDITION    sum(IMP_CMA) as IMP     sum(LIMITE) as LIM  Count(*) 
as Clients

我怎么能那样做？

谢谢

连接表看起来像这样

CLIENT    LSCRV   POSIC_ACTUAL   IMP_CMA    LIMIT
479        A         010         2253.75    601.01
352        A         010            1200    3300
352        B         020               0    0
352        A         040               0    0
419        C         010            2600    600
019        C         042               0    0

otuput 表应如下所示：

LSCRV     CONDITION     IMP_CMA      LIMITE     CLIENTS
  A            1         3453.75     3901.01       2
  A            0            0           0          1
  B            0            0           0          1
  C            1          2600         600         1
  C            0            0           0          1

标签： sqloracle

解决方案

根据你的解释

SELECT LSCRV, CONDITION, 
      sum(IMP_CMA) as IMP, 
      sum(LIMITE) as LIM, 
      Count(*) as Clients 
      FROM (
                SELECT L.LSCRV,
                case when C.POSIC_ACTUAL = '10' then 1 else 0 end as CONDITION,  --- this will check your case condition
                C.IMP_CMA, C.LIMIT
                FROM ADC.RV L LEFT JOIN ADC.CCB C
                     ON SUBSTR(L.CLIENT, 1,12) = C.CLIENT 
            ) 
      GROUP BY LSCRV , CONDITION  --- this will apply group by as you need

推荐阅读