现有如果情况的数据 一张表有下列数据
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个 求大侠指教啊,效率最好高点啊
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;
如果是少量数据 可以 先排序 然后在链接
数据多的话 楼上应该要于我的