有一表如下(t1)a b c
A1 B1 3
A1 B2 5
A2 B3 2
A2 B4 3想得到如下结果a b cA1 B1,B2 8
A2 B3,B4 5
A1 B1 3
A1 B2 5
A2 B3 2
A2 B4 3想得到如下结果a b cA1 B1,B2 8
A2 B3,B4 5
解决方案 »
- DBCONSOLE问题
- 怎么计算停车收费
- 监听别名配置问题……
- 如果没有oracle客户端,怎样在服务器端查看数据库的数据?
- oracle dblink sqlserver查询字符串的问题
- [急求] 同一个oracle服务器 上有两个数据库, 请问怎么同时操作两个数据库. 也就是说可以用一条sql 操作两个数据库
- 在建一个和a表结构一样的空表中,有个问题不是很了解!
- 关于oracle排序的问题。
- 我的pb程序在和oracle连接后,对数据进行修改操作时,不能够自动断开连接?
- Oracle中不支持使用with子句
- 如何用sql 查看当前系统cpu使用率 以及占系统CPU最高的sql
- oracle 安装完后,怎么没management server服务
select a,WMSYS.WM_CONCAT(b) b, sum(c) c
from t1
group by a
select a,substr(max(sys_connect_by_path(b,',')),2) b ,sum(c) c
from (select a.*,row_number()over(partition by a order by b) rn from t1 )
group by a
start with rn=1
connect by rn-1=prior rn and a=prior a
(select 'A1' a, 'B1' b , 3 c from dual
union all
select
'A1', 'B2', 5 from dual
union all
select
'A2', 'B3', 2 from dual
union all
select 'A2', 'B4', 3 from dual)
select a,WMSYS.WM_CONCAT(b) b, sum(c) c
from t1
group by a结果
Row# A B C1 A1 B1,B2 8
2 A2 B3,B4 5
(SELECT 'A1' a, 'B1' b, 3 c
FROM DUAL
UNION ALL
SELECT 'A1', 'B2', 5
FROM DUAL
UNION ALL
SELECT 'A2', 'B3', 2
FROM DUAL
UNION ALL
SELECT 'A2', 'B4', 3
FROM DUAL)SELECT a, SUBSTR (MAX (SYS_CONNECT_BY_PATH (b, ',')), 2) b, SUM (c) c
FROM (SELECT a.*, ROW_NUMBER () OVER (PARTITION BY a ORDER BY b) rn
FROM t1 a)
GROUP BY a
START WITH rn = 1
CONNECT BY rn - 1 = PRIOR rn AND a = PRIOR a
结果
Row# A B C1 A1 B1,B2 8
2 A2 B3,B4 5
FROM (SELECT a.*, ROW_NUMBER () OVER (PARTITION BY a ORDER BY b) rn
FROM t1 a)
GROUP BY a
START WITH rn = 1
CONNECT BY rn - 1 = PRIOR rn AND a = PRIOR a