有表如下:流水号 ID 数量 类型
1001 A1 20 X
1002 A1 10 X
1003 A2 40 X
1004 A2 20 Y
1005 A3 80 Z
1006 A3 20 Y
1007 A3 10 Y求一SQL语句欲得到如下结果:A1 30 X
A2 60 X,Y
A3 110 Y,Z
1001 A1 20 X
1002 A1 10 X
1003 A2 40 X
1004 A2 20 Y
1005 A3 80 Z
1006 A3 20 Y
1007 A3 10 Y求一SQL语句欲得到如下结果:A1 30 X
A2 60 X,Y
A3 110 Y,Z
解决方案 »
- 第一次写存储过程 报错 请高手指导 在线等
- oracle里德函数是不是一定要有返回值?
- Connection refused(DESCRIPTION=(TMP=)(VSNNUM=169869568)(ERR=12505)(ERROR_STACK=(ERROR=(CODE=12505)(EMFI=4))))
- 急 朋友们帮我看一下吧 安装碰到问题拉.
- 了解SQL SERVER 及ORACLE的朋友们,有关两种数据库的数据同步;
- SQL 语句不会
- 救急:ora:01034 oracle not available 请问如何解决?
- 请问:oracle9i(或8i)数据库的字段有没有自动产生流水号的功能 ?
- 不明白,不明白,来帮我看一下,谢谢
- 急!!急!!急!!急!!急!!大家帮个忙。
- oracle 10g 导入数据库后,如何创建用户,并且能查看该数据库。
- PL/SQL Developer中如何设置让关键字大写?
select 'A' grade,'XX'name from dual union all
select 'A','XY' from dual union all
select 'A','YY' from dual union all
select 'B','aa' from dual union all
select 'B','bb' from dual)
select grade,substr(max(sys_connect_by_path(name,';')),2) name
from (select grade,name,row_number() over(partition by grade order by name) rn from tb)
start with rn=1
connect by rn= prior rn+1 and
connect_by_root(grade)=grade
group by grade;
--
GRADE NAME
----- --------------------------------------------------------------------------------
A XX;XY;YY
B aa;bb
--
10g的实现:
with t as(
select 'A' grade,'XX'name from dual union all
select 'A','XY' from dual union all
select 'A','YY' from dual union all
select 'B','aa' from dual union all
select 'B','bb' from dual)
select grade,wmsys.wm_concat(name) name
from t
group by grade;
--
GRADE NAME
----- --------------------------------------------------------------------------------
A XX,XY,YY
B aa,bb
with tb as(
select 'A' grade,'XX'name from dual union all
select 'A','XY' from dual union all
select 'A','YY' from dual union all
select 'B','aa' from dual union all
select 'B','bb' from dual)
select grade,substr(max(sys_connect_by_path(name,';')),2) name
from (select grade,name,row_number() over(partition by grade order by name) rn from tb)
start with rn=1
connect by rn= prior rn+1 and grade=prior grade
group by grade;
--10g
with t as(
select 'A' grade,'XX'name from dual union all
select 'A','XY' from dual union all
select 'A','YY' from dual union all
select 'B','aa' from dual union all
select 'B','bb' from dual)
select grade,wmsys.wm_concat(name) name
from t
group by grade;
result:
A XX;XY;YY
B aa;bb
注意看我例子里的A3 类型合并后显示的 是 y,z 而不是 y,y,z
既相同的类型只显示一个,而不同类型的则合并显示并用逗号隔开
with tb1 as(
select 'A' ID,20 qty,'X' TYPE from dual union all
select 'A',30,'X' from dual union all
select 'A',10,'Y' from dual union all
select 'A',10,'Z' from dual union all
select 'B',12,'X' from dual union all
select 'B',31,'Y' from dual)
SELECT ID,SUM(qty) qty,wm_concat(TYPE) TYPE FROM ( SELECT ID,SUM(qty) qty,TYPE FROM tb1 GROUP BY ID,TYPE ORDER BY ID, TYPE) GROUP BY ID
with a as(
select 1001 id,'A1' type,20 value,'X' flag from dual UNION all
select 1002,'A1',10,'X' from dual union all
select 1003,'A2',40,'X' from dual union all
select 1004,'A2',20,'Y' from dual union all
select 1005,'A3',80,'Z' from dual union all
select 1006,'A3',20,'Y' from dual union all
select 1007,'A3',10,'Y' from dual)
SELECT TYPE,SUM(VALUE),wm_concat(DISTINCT flag) FROM a GROUP BY TYPE;
with tb as(
select '1001' a,'A1' b, 20 c, 'X' d from dual
union all
select '1002','A1', 10 ,'X' from dual
union all
select '1003','A2', 40 ,'X' from dual
union all
select '1004','A2', 20 ,'Y' from dual
union all
select '1005','A3', 80 ,'Z' from dual
union all
select '1006','A3', 20 ,'Y' from dual
union all
select '1007','A3', 10 ,'Y' from dual
)select b,sum(c),max(substr(sys_connect_by_path(d,','),2)) path from
(
select b,d,sum(c) c,row_number()over(partition by b order by b) rn from tb group by b,d) start with rn=1
connect by rn-1 =prior rn and b=prior b
group by b
--result:
A1 30 X
A2 60 X,Y
A3 110 Y,Z