Oracle SQL根据其他列值对一列进行分组

My Table:

con_ref     col_ref   sal
  1           NON     25
  1           COL     36
  1           COL     12
  1           NON     13
  2           NON     25
  2           NON     6
  2           NON     33
  3           COL     42
  3           NON     51
  4           NON     63
  4           NON     31
  4           NON     15

I want to group above data on Con_ref column where col_ref value is just 'NON'. so if the col_ref has both 'NON' and 'COL' this should exclude.

我想在Con_ref列上对数据进行分组,其中col_ref值只是'NON'。因此,如果col_ref同时包含'NON'和'COL',则应排除。

Desired Output:

CON_REF  sum(SAL)
2          64
4         109

2 个解决方案

#1

Something like this:

像这样的东西:

with sample_data as (select 1 con_ref, 'NON' col_ref, 25 sal from dual union all
                     select 1 con_ref, 'COL' col_ref, 36 sal from dual union all
                     select 1 con_ref, 'COL' col_ref, 12 sal from dual union all
                     select 1 con_ref, 'NON' col_ref, 16 sal from dual union all
                     select 2 con_ref, 'NON' col_ref, 25 sal from dual union all
                     select 2 con_ref, 'NON' col_ref, 6 sal from dual union all
                     select 2 con_ref, 'NON' col_ref, 33 sal from dual union all
                     select 3 con_ref, 'COL' col_ref, 42 sal from dual union all
                     select 3 con_ref, 'NON' col_ref, 51 sal from dual union all
                     select 4 con_ref, 'NON' col_ref, 63 sal from dual union all
                     select 4 con_ref, 'NON' col_ref, 31 sal from dual union all
                     select 4 con_ref, 'NON' col_ref, 15 sal from dual)
-- end of mimicking a table called sample_data with data in it
select con_ref,
       sum(sal)
from   sample_data
group by con_ref
having count(case when col_ref != 'NON' then 1 end) = 0;

   CON_REF   SUM(SAL)
---------- ----------
         2         64
         4        109

ETA: The same result as requested without using GROUP BY (N.B. this is for Oracle; I have no idea whether it would work on other platforms):

ETA:与不使用GROUP BY请求的结果相同(N.B.这适用于Oracle;我不知道它是否适用于其他平台):

with sample_data as (select 1 con_ref, 'NON' col_ref, 25 sal from dual union all
                     select 1 con_ref, 'COL' col_ref, 36 sal from dual union all
                     select 1 con_ref, 'COL' col_ref, 12 sal from dual union all
                     select 1 con_ref, 'NON' col_ref, 16 sal from dual union all
                     select 2 con_ref, 'NON' col_ref, 25 sal from dual union all
                     select 2 con_ref, 'NON' col_ref, 6 sal from dual union all
                     select 2 con_ref, 'NON' col_ref, 33 sal from dual union all
                     select 3 con_ref, 'COL' col_ref, 42 sal from dual union all
                     select 3 con_ref, 'NON' col_ref, 51 sal from dual union all
                     select 4 con_ref, 'NON' col_ref, 63 sal from dual union all
                     select 4 con_ref, 'NON' col_ref, 31 sal from dual union all
                     select 4 con_ref, 'NON' col_ref, 15 sal from dual)
-- end of mimicking a table called sample_data with data in it
select distinct con_ref,
                sum_sal
from   (select con_ref,
               sum(sal) over (partition by con_ref) sum_sal,
               count(case when col_ref != 'NON' then 1 end) over (partition by con_ref) cnt_non_non_con_ref
        from   sample_data)
where  cnt_non_non_con_ref = 0;

   CON_REF    SUM_SAL
---------- ----------
         4        109
         2         64

#2

Your table and its data :

你的表格及其数据:

And this code block solve your problem, check it :

这个代码块解决了你的问题,检查它:

select mt.con_ref ,sum(mt.sal) from mytable mt where mt.col_ref = 'NON' and not exists (select * from mytable mt1 where mt1.con_ref = mt.con_ref and mt1.col_ref <> 'NON') group by mt.con_ref ,mt.col_ref

从mytable mt中选择mt.con_ref,sum(mt.sal),其中mt.col_ref ='NON'并且不存在(从mytable mt1中选择*,其中mt1.con_ref = mt.con_ref和mt1.col_ref <>'NON')组由mt.con_ref,mt.col_ref

And output this :

输出这个:

#1