首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >分组依据hibernate中的两列条件

问分组依据hibernate中的两列条件
EN

Stack Overflow用户
提问于 2017-05-14 14:12:45
回答 2查看 260关注 0票数 0

我想在两列上分组。如果b不为空,则按a和b分组,如果b为空,则按a分组。我写了这个查询,但在b为空的情况下它不起作用!查询的结果是所有b不为空的行

代码语言:javascript
复制
select  m.s.a ,
case when (m.l is not  null)  
then  m.l.code end , coalesce(sum(m.c),0  ) 
from material m where m.Item.id =:itemId 
group by m.s.a, case 
when (m.l is not null)  
then m.l.code end


+--+----+-------+---+
|  | s  | l     | c |
+--+----+-------+---+
|  |  a | d     |  1 |
|  |  a | d     |  9 |
|  |  a | e     |  3 |
|  |  a | f     |  4 |
|  |  c | g     |  5 |
|  |  c | g     |  6 |
|  |  c | h     | 20 |
|  |  d | null  |  7 |
|  |  d | null  |  8 |

预期结果:

代码语言:javascript
复制
+--+----+-------+---+
|  | s  | l     | c |
+--+----+-------+---+
|  |  a | d     | 10 |
|  |  a | e     |  3 |
|  |  a | f     |  4 |
|  |  c | g     | 11 |
|  |  c | h     | 20 |
|  |  d |       | 15 |
EN

回答 2

Stack Overflow用户

发布于 2017-05-14 15:48:39

默认情况下,oracle/postgres/mysql将生成预期的输出。

代码语言:javascript
复制
SELECT s,l,sum(c)
FROM temp
GROUP BY s,l;

如果不想按空值分组,可以使用UNION

代码语言:javascript
复制
SELECT s,l,sum(c)
FROM temp
WHERE l is NOT NULL
GROUP BY s,l
UNION
SELECT s,l,sum(c)
FROM temp
WHERE l is NULL;
票数 2
EN

Stack Overflow用户

发布于 2017-05-14 20:07:49

代码语言:javascript
复制
with data (col1, col2, val) as
(
  select 'a',   'd',        1 from dual union all  
  select 'a',   'd',        9 from dual union all  
  select 'a',   'e',        3 from dual union all  
  select 'a',   'f',        4 from dual union all  
  select 'c',   'g',        5 from dual union all  
  select 'c',   'g',        6 from dual union all  
  select 'c',   'h',       20 from dual union all  
  select 'd',   null,       7 from dual union all  
  select 'd',   null,       8 from dual union all
  select 'e',   'g',     null from dual  -- additional check if val is null
)
,
prs (col1, col2, col1n2) as 
(
  select distinct col1, col2, col1||'-'||col2 from data
)
,
rs (col, val) as 
(
  -- concatenate the columns that need to be grouped by 
  -- to act as one single column (col1 and col2)
  select col1||'-'||col2, sum(nvl(val,0)) from data group by col1||'-'||col2
)
select 
  prs.col1, prs.col2, rs.val 
from 
  rs join prs 
  on (prs.col1n2 = rs.col)
order by 1
;
票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/43960899

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档