求助 oracle 一条sql 语句, 急用 可以 加分
本帖最后由 likaiyihou512 于 2013-09-22 10:50:13 编辑 有表如下：
字段 X Y Z A
x 10 1 2
x 10 2 3
x 11 1 4
x 11 2 6
y 15 5 1
y 15 6 4
y 16 5 2
y 16 6 8
求： 按字段X 分组，取 Y 最大的记录，
例如 要得到的结果是：
x 11 4 6,
y 16 2 8 oracle sql
[解决办法]
看看懂你的z列是不是没有用到还是写错了呢？

select x,max(y),z,a group by x,z,a

[解决办法]
数据确实乱，不过语法大体和楼上一致
[解决办法]
你用decode转换一下吧
decode(z,1,a)a
decode(z,2,a)b
[解决办法]
最近学了个函数，来抛砖引玉一下：

WITH T AS
 (SELECT 'x' X, 10 Y, 1 Z, 2 A
    FROM DUAL
  UNION ALL
  SELECT 'x', 10, 2, 3
    FROM DUAL
  UNION ALL
  SELECT 'x', 11, 1, 4
    FROM DUAL
  UNION ALL
  SELECT 'x', 11, 2, 6
    FROM DUAL
  UNION ALL
  SELECT 'y', 15, 5, 1
    FROM DUAL
  UNION ALL
  SELECT 'y', 15, 6, 4
    FROM DUAL
  UNION ALL
  SELECT 'y', 16, 5, 2
    FROM DUAL
  UNION ALL
  SELECT 'y', 16, 6, 8 FROM DUAL)
SELECT X, MAX(Y), WMSYS.WM_CONCAT(A)
  FROM (SELECT * FROM T WHERE Y IN (SELECT MAX(Y) FROM T GROUP BY X))
 GROUP BY X;

接近楼主的要求了，但还不完美！
[解决办法]

select
x,gy,min(a),max(a)
from
(select X,y,max(Y) over(partition by Z) gy
,a
from
(select 'x' X, 10 Y,1 Z,2 A from dual
union
select 'x' X, 10 Y,2 Z,3 A from dual
union
select 'x' X, 11 Y,1 Z,4 A from dual
union
select 'x' X, 11 Y,2 Z,6 A from dual
union
select 'y' X, 15 Y,5 Z,1 A from dual
union
select 'y' X, 15 Y,6 Z,4 A from dual
union
select 'y' X, 16 Y,5 Z,2 A from dual
union
select 'y' X, 16 Y,6 Z,8 A from dual)) 
 
where y=gy
group by x,gy

[解决办法]
你是的意思是有多少条，后面就加多少列？
那这样的话就会出现，有些组有两条，有些组有五条,那到底是要多少列。
比如说如果最多条数的是五，后面加五列，对于那些只有两三条的分组，那些空列怎么处理
你要把你的需求再说明白点
[解决办法]
with t as(
select 'x' X, 10 Y,1 Z,2 A from dual
union
select 'x' X, 10 Y,2 Z,3 A from dual
union
select 'x' X, 11 Y,1 Z,4 A from dual
union
select 'x' X, 11 Y,2 Z,6 A from dual
union
select 'y' X, 15 Y,5 Z,1 A from dual
union
select 'y' X, 15 Y,6 Z,4 A from dual
union
select 'y' X, 16 Y,5 Z,2 A from dual
union
select 'y' X, 16 Y,6 Z,8 A from dual)
select x,y, wmsys.wm_concat(a) from t where (x,y) in ( select x,max(y) from t group by x)
group by x,y
[解决办法]

select x,gy,a1,a2,a3,a4,a5 from
(
select x,gy
,lag(a,case when rb-1 >=0 then rb-1 else 999 end) over(partition by x,gy order by a) a1
,lag(a,case when rb-2 >=0 then rb-2 else 999 end) over(partition by x,gy order by a) a2
,lag(a,case when rb-3 >=0 then rb-3 else 999 end) over(partition by x,gy order by a) a3
,lag(a,case when rb-4 >=0 then rb-4 else 999 end) over(partition by x,gy order by a) a4
,lag(a,case when rb-5 >=0 then rb-5 else 999 end) over(partition by x,gy order by a) a5
,row_number() over( partition by x order by  rownum desc) xrb
from (select
x,gy,a,row_number() over(partition by x order by a asc) rb
from
(select X,y,max(Y) over(partition by Z) gy
,a
from
(select 'x' X, 10 Y,1 Z,2 A from dual
union
select 'x' X, 10 Y,2 Z,3 A from dual
union
select 'x' X, 11 Y,1 Z,4 A from dual
union
select 'x' X, 11 Y,2 Z,6 A from dual
union
select 'x' X, 11 Y,1 Z,7 A from dual
union
select 'x' X, 11 Y,2 Z,8 A from dual
union
select 'x' X, 11 Y,2 Z,9 A from dual
union
select 'y' X, 15 Y,5 Z,1 A from dual
union
select 'y' X, 15 Y,6 Z,4 A from dual
union
select 'y' X, 16 Y,5 Z,2 A from dual
union
select 'y' X, 16 Y,6 Z,8 A from dual))
where y=gy))
where xrb=1

用的分析函数有点多，而且还是嵌套,如果数据量大(千万级)的话，建议分几步作
[解决办法]

select x,gy,a1,a2,a3,a4,a5 from
(
select x,gy
,lag(a,case when rb-1 >=0 then rb-1 else 999 end,0) over(partition by x,gy order by a) a1
,lag(a,case when rb-2 >=0 then rb-2 else 999 end,0) over(partition by x,gy order by a) a2
,lag(a,case when rb-3 >=0 then rb-3 else 999 end,0) over(partition by x,gy order by a) a3
,lag(a,case when rb-4 >=0 then rb-4 else 999 end,0) over(partition by x,gy order by a) a4
,lag(a,case when rb-5 >=0 then rb-5 else 999 end,0) over(partition by x,gy order by a) a5
,row_number() over( partition by x order by  rownum desc) xrb
from
(select X,y,max(Y) over(partition by x) gy,
 rank() over (partition by x,y order by a asc) rb
,a
from
(select 'x' X, 10 Y,1 Z,2 A from dual
union
select 'x' X, 10 Y,2 Z,3 A from dual
union
select 'x' X, 11 Y,1 Z,4 A from dual
union
select 'x' X, 11 Y,2 Z,6 A from dual
union
select 'x' X, 11 Y,1 Z,7 A from dual
union
select 'x' X, 11 Y,2 Z,8 A from dual
union
select 'x' X, 11 Y,2 Z,9 A from dual
union 
 
select 'y' X, 15 Y,5 Z,1 A from dual
union
select 'y' X, 15 Y,6 Z,4 A from dual
union
select 'y' X, 16 Y,5 Z,2 A from dual
union
select 'y' X, 16 Y,5 Z,9 A from dual
union
select 'y' X, 16 Y,6 Z,8 A from dual))
where y=gy)
where xrb=1

这个少了嵌套,找不到的列置为0
[解决办法]

select t.x,t.y,decode(t.z, '1', t.a) as A , decode(t.z, '2', t.a) as B
from (
改为：
select t.x,t.y,decode(t.z, '1', t.a,null,t.a) as A , decode(t.z, '2', t.a,null,t.a) as B
from (