27,580
社区成员
发帖
与我相关
我的任务
分享
select a,sum(d) as d from (
select a,b,sum(d) as d,row_number() over(partition by a,b order by sum(d) desc) as rowid
from tb group by a,b,c
) a where rowid=1 group by a
只是减少了一层子查询with tb as (
select 'a' as a,'b' as b,'c' as c,10 as d
union all
select 'a' as a,'b' as b,'c' as c,20 as d
union all
select 'a1' as a,'b' as b,'c' as c,20 as d
union all
select 'a' as a,'b' as b,'c1' as c,10 as d
union all
select 'a' as a,'b' as b,'c1' as c,30 as d
union all
select 'a' as a,'b1' as b,'c1' as c,30 as d
)
--select a,b,c,sum(d) as d from tb group by a,b,c
SELECT A,SUM(D) AS D
FROM tb
GROUP BY A
A D
---- -----------
a 100
a1 20
(2 行受影响)
select a,sum(d) as d from (
select a,max(d) as d from (
select a,b,sum(d) as d from tb group by a,b,c
) a group by a,b
) a group by a
a d
---- -----------
a 70
a1 20
(2 行受影响)
select a,sum(d) as d from (
select a,b,sum(d) as d,row_number() over(partition by a,b order by sum(d) desc) as rowid
from tb group by a,b,c
) a where rowid=1 group by a
a d
---- -----------
a 70
a1 20
(2 行受影响)
SELECT A,SUM(D) AS D
FROM tb
GROUP BY A
B和C根本没用上, 转了不必要的弯