oracle sql group by用空字符串

2024-04-27 • 问答

我应该用空字符串对列表进行分组。这里有2个示例，没有空字符串的ohne（工作正常）和有空字符串的一个例子（不工作）。有任何想法吗？

Works well
==============
Name    Bewertung   Leistung        
---------------------------------------
Jagger  234'234     <Null>
Jagger  <Null>      100'000

select Name,sum(Bewertung),sum(Leistung) from Stars
  group by Name

Name    Bewertung   Leistung        
---------------------------------------
Jagger  234'234     100'000


__/__/__/__/__/__/__/__/__/__/__/__/
Don't work
============
Name    Bewertung   WHRB    Leistung    WHRL
-------------------------------------------------------
Jagger  234'234     GBP     <Null>      <Null>
Jagger  <Null>      <Null>  100'000     GBP
Jagger  <Null>      <Null>   50'000     GBP

select Name,WHRB,sum(Leistung),WHRL
  group by Name,WHRL

Name    Bewertung   WHRB    Leistung    WHRL
-------------------------------------------------------
Jagger  234'234     GBP     <Null>      <Null>
Jagger  <Null>      <Null>  150'000     GBP

不确定您是否想要这样的东西，因为您没有发布预期的输出：

   select Name,WHRB,WHRL,sum(Bewertung),sum(Leistung)
      group by nvl(Name,rownum),Name nvl(WHRB,WHRB),nvl(WHRL,WHRL

或者您也可以通过汇总WHRB,WHRL列来忽略空值：

   select Name,sum(WHRB),sum(WHRL),sum(Leistung)
      group by Name;

一种选择是汇总这两列（例如，使用MAX）：

SQL> with stars (name,bewertung,whrb,leistung,whrl) as
  2    (select 'Jagger',234234,'GBP',to_number(null),to_char(null) from dual union all
  3     select 'Jagger',null,100000,'GBP' from dual
  4    )
  5  select name,sum(bewertung),max(whrb),sum(leistung),max(whrl)
  6  from stars
  7  group by name;

NAME   SUM(BEWERTUNG) MAX SUM(LEISTUNG) MAX
------ -------------- --- ------------- ---
Jagger         234234 GBP        100000 GBP

SQL>

另一种解决方案是使用唯一值创建2个子查询。

select *
from   
(
    select Name,Bewertung,WHRB 
    from stars 
    where WHRB is not null
) a
full join 
(
    select Name,Leistung,WHRL 
    from stars 
    where WHRL is not null
) b using (Name)

oracle sql group by用空字符串

a20855737 回答：oracle sql group by用空字符串

大家都在问