将两行合并为一行，其中一列具有不同的值

Question

我有两次相同的exp_seek_id的经验，基于一个国外命名的专栏，它要么是'Y‘，要么是'N’。我计算了所有国外经验的总和和所有国内经验的总和。到目前为止，我已经成功获取了上面的结果，但我得到了两行相同的主键。有没有可能同时获得(国外和国内)两种体验？

到目前为止，我得到了这样的结论：

EXP_SEEK_ID | Experience | Abroad
      146           7        Y
      146           3        N

预期结果：

EXP_SEEK_ID | Abroad | Domestic
      146           7      3

我的代码：

Select exp_seek_id ,experience,
SUM( extract (year from exp_date_to)- extract (year from exp_date_from) )
From 
job_seek_experience where 
exp_seek_id = 146
Group By 
exp_seek_id,experience

Answer 1

我将假设您的最终结果作为我的输入，但您可以根据自己的情况轻松地修改脚本。

create table exp_table
(exp_seek_id number, experience number, abroad char);

insert into exp_table
values(146, 7, 'Y');
insert into exp_table
values(146, 3, 'N');



with abroad_exp as
        (select exp_seek_id, 
                experience, 
                abroad 
         from exp_table
         where abroad = 'Y'),
     domestic_exp as
        (select exp_seek_id, 
                experience, 
                abroad  
         from exp_table
         where abroad = 'N')
select /*+ parallel */
  abroad_exp.exp_seek_id,
  abroad_exp.experience as abroad,
  domestic_exp.experience as domestic
from abroad_exp,
     domestic_exp
where abroad_exp.exp_seek_id = domestic_exp.exp_seek_id

Answer 2

通常的做法是结合使用SUM + DECODE，例如：

SQL> with job_seek_experience
  2    (exp_seek_id, experience, abroad) as
  3    (select 146, 1, 'Y' from dual union all
  4     select 146, 6, 'Y' from dual union all
  5     select 146, 3, 'N' from dual union all
  6     --
  7     select 222, 4, 'Y' from dual
  8    )
  9  select exp_seek_id,
 10         sum(decode(abroad, 'Y', experience)) abroad,
 11         sum(decode(abroad, 'N', experience)) domestic
 12  from job_seek_experience
 13  where exp_Seek_id = 146
 14  group by exp_Seek_id;

EXP_SEEK_ID     ABROAD   DOMESTIC
----------- ---------- ----------
        146          7          3

SQL>

对您来说有趣的部分是第9-14行(CTE不是那么有趣，不是吗？)

Answer 3

使用DECODE ()很容易

drop table omc.test;
create table omc.test (
EXP_SEEK_ID  NUMBER(5),
Experience  NUMBER ( 3) , 
Abroad  CHAR(1) 
); 

insert into omc.test values ( 146, 7, 'Y' ) ;
insert into omc.test values ( 146, 3, 'N' ) ;

commit;

select EXP_SEEK_ID,  SUM(decode( Abroad,'Y',Experience))  Abroad  , SUM( decode( Abroad,'N',Experience))  Domestic
FROM omc.test
group by EXP_SEEK_ID

国外EXP_SEEK_ID国内

    146          7          3