现有如果情况的数据 一张表有下列数据
X Y
1 11
1 12
1 13
2 2
3 31
3 32
4 4我想查询时查询结果是
1 11,12,13(将这三个数据合并为一个结果字段)
2 2
3 31,32
4 4
注:表中数据可能上千万,但一般同一X对应Y的值不会超过100个 求大侠指教啊,效率最好高点啊
X Y
1 11
1 12
1 13
2 2
3 31
3 32
4 4我想查询时查询结果是
1 11,12,13(将这三个数据合并为一个结果字段)
2 2
3 31,32
4 4
注:表中数据可能上千万,但一般同一X对应Y的值不会超过100个 求大侠指教啊,效率最好高点啊
解决方案 »
- 求教:视图存在重复记录、查询卡死等问题
- 根据sqlserver中的多个表,组合成一个新表,并建立在oracle数据库中
- 8i里如何实现存储过程的嵌套调用?
- 还原了一个WE8ISO8859P1字符集 数据库 表里汉字都是乱码显示 如何才能看到汉字?
- 怎样修改oracle cache
- Could not create an environment: OCIEnvCreate returned -1.真奇怪!
- java.exe运行错误
- 为什么不能登陆ORACLE Manage Server
- 一个简单问题,寻求帮忙
- 在NT4.0和WIN2000中安装oracle8有什么区别
- sql修改语句,修改字段中的部分文本内容。
- QQ空间评论表结构设计
select X,wm_concat(Y) from 你的表 group by X;
select 1, 11, 'B' from dual
union all select 1, 12, 'A' from dual
union all select 1, 13, 'C' from dual
union all select 2, 2, 'G' from dual
union all select 3, 31, 'D' from dual
union all select 3, 32, 'F' from dual
union all select 4, 4, 'M' from dual
)
select x, y from (
select x,
wm_concat(y) over (partition by x order by z) y,
row_number() over (partition by x order by z desc) rn
from t) where rn = 1;执行结果:
X Y
--- ----------
1 12,11,13
2 2
3 31,32
4 4
with t(X,Y,Z) as(
select 1,11,3 from dual
union all select 1,12,2 from dual
union all select 1,13,4 from dual
union all select 2,2,2 from dual
union all select 3,31,2 from dual
union all select 3,32,1 from dual
union all select 4,4,1 from dual
)
select X,max(RN) Y
from (select X,wm_concat(Y) over(partition by X order by Z) RN from t)
group by X;
with t as (
select 1 as x, 11 as y, 'B' as z from dual
union all select 1, 12, 'A' from dual
union all select 1, 13, 'C' from dual
union all select 2, 2, 'G' from dual
union all select 3, 31, 'V' from dual
union all select 3, 32, 'F' from dual
union all select 4, 4, 'M' from dual
)
select X,wm_concat(Y)
from ( select * from t order by Z)
group by X;
如果是少量数据 可以 先排序 然后在链接
数据多的话 楼上应该要于我的