select
t.a,t.b,t.c,t.d,(select sum(d) from 表 where a=t.a) as 'sum'
from
表 t
where
not exists(select 1 from 表 where a=t.a and b<t.b)
t.a,t.b,t.c,t.d,(select sum(d) from 表 where a=t.a) as 'sum'
from
表 t
where
not exists(select 1 from 表 where a=t.a and b<t.b)
not exists(select 1 from 表 where a=t.a and b<t.b)
什么意思?
select 1?
b<t.b?
and b<t.b的含义是在t.b这个试图中,每条记录是不是都不大于原始表中a字段值相同的记录。
这个sql肯定没问题。
b的值不一定是b1\b2\b3这样的,怎么能用"b<t.b"?
我得意思是 "a字段相同数据如果多条的话只要第一条"
也许后边还有其他字段efg之类
除了这个还有没有别的办法?这个sql在我这个实际应用中不能用
2 (select a, b, c, d, row_number()over(partition by a order by b, c, d) as rn, sum(d) over(partition by a) as totald
3 from
4 (select 'a1' as a, 'b1' as b, 'c1' as c, '1' as d from dual union all
5 select 'a1' as a, 'b2' as b, 'c1' as c, '2' as d from dual union all
6 select 'a1' as a, 'b3' as b, 'c1' as c, '3' as d from dual union all
7 select 'a2' as a, 'b4' as b, 'c1' as c, '4' as d from dual union all
8 select 'a3' as a, 'b5' as b, 'c1' as c, '5' as d from dual)
9 )where rn = 1
10 /A B C D TOTALD
-- -- -- - ----------
a1 b1 c1 1 6
a2 b4 c1 4 4
a3 b5 c1 5 5