有表如下:
字段 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))