我在数据有个表:
id name num
1 a 10
2 b 2
3 c 3
4 a -13
5 d 11
6 b 1
7 e 10
合并掉相同的name后,num相加,其中第一行和第四行合并,第二行和第六行合并
如何变成下面的格式
id name num
1 a -3
2 b 3
3 c 3
4 d 11
5 e 10
id name num
1 a 10
2 b 2
3 c 3
4 a -13
5 d 11
6 b 1
7 e 10
合并掉相同的name后,num相加,其中第一行和第四行合并,第二行和第六行合并
如何变成下面的格式
id name num
1 a -3
2 b 3
3 c 3
4 d 11
5 e 10
--TRY IT
SELECT ROWNUM AS ID ,A.* FROM
(
select name,sum(num) as num from 表 group by name) A
select name,sum(num) as num from test group by name) order by name;
只能理解为按name排序重新编号
所以答案是select rownum as id, name, num
from (select name, sum(num) as num from test group by name)
order by name;
from (select name,min(id) id, sum(num) as num from test group by name)
order by id;
from (select name,min(id) id, sum(num) as num from a group by name)
order by id;
select id,name,num
from (select name,min(id) id, sum(num) as num from a group by name)
order by id;
我新的表里的id应该时连续的
SQL> SELECT DISTINCT DENSE_RANK() OVER(ORDER BY NAME) NEW_ID,
2 TT.NAME,
3 SUM(NUM) OVER(PARTITION BY NAME) SUM_NUM
4 FROM TABLE_NAME TT; NEW_ID NAME SUM_NUM
---------- ---- ----------
1 A -3
2 B 3
3 C 3
4 D 11
5 E 10