我想在两列上分组。如果b不为空,则按a和b分组,如果b为空,则按a分组。我写了这个查询,但在b为空的情况下它不起作用!查询的结果是所有b不为空的行
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 |预期结果:
+--+----+-------+---+
| | s | l | c |
+--+----+-------+---+
| | a | d | 10 |
| | a | e | 3 |
| | a | f | 4 |
| | c | g | 11 |
| | c | h | 20 |
| | d | | 15 |发布于 2017-05-14 15:48:39
默认情况下,oracle/postgres/mysql将生成预期的输出。
SELECT s,l,sum(c)
FROM temp
GROUP BY s,l;如果不想按空值分组,可以使用UNION
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;发布于 2017-05-14 20:07:49
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
;https://stackoverflow.com/questions/43960899
复制相似问题