看看懂你的z列是不是没有用到还是写错了呢?select x,max(y),z,a group by x,z,a
我取得 是Z列 不同, x 11 1 4 x 11 2 6 你看着两列,我要得到:x 11 4 6
你用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 t.x,t.y,decode(t.z, '1', t.a) as A , decode(t.z, '2', t.a) as B from ( select x,y,z,a from test where (x,y) in (select x,max(to_number(y)) from test group by x) --group by x,y order by x,y ) t -- ,wmsys.wm_concat(A)
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
如果A列 有存在 为null 的数据,使用 这个函数就不好使了
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
最多五种的话,就这样写 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 UNION ALL SELECT 'y', 16, 7, 9 FROM DUAL), t1 as (select x, y, a, rn from (select x, y, a, dense_rank() over(partition by x order by y desc) dr, row_number() over(partition by x order by y desc) rn from t) where dr = 1) select * from t1 pivot(max(a) for rn in(1, 2, 3, 4, 5))
select t.x,t.y,decode(t.z, '1', t.a) as A , decode(t.z, '2', t.a) as B from ( select x,y,z,a from test where (x,y) in (select x,max(to_number(y)) from test group by x) --group by x,y order by x,y ) t -- ,wmsys.wm_concat(A)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 (
create or replace view se__examination_v as with a as (select se.sys_user_id,se_exam_p.Get_Employee_Names(se.sys_user_id) as SYS_USER_NAME,se.se_paper_id,sp.paper_name, max(sea.attribute_10) as exam_num from se_examinee_answer sea,se_paper sp ,se_question sq,se_examinee se where sea.se_examinee_id = se.id and sea.se_question_id = sq.id and sp.id = se.se_paper_id and se.status = '3' and sp.reply_num is not null and sp.reply_grade is not null and sea.attribute_10 is not null group by se.sys_user_id,se_exam_p.Get_Employee_Names(se.sys_user_id),se.se_paper_id,sp.paper_name),b as (select se.sys_user_id,se_exam_p.Get_Employee_Names(se.sys_user_id) as SYS_USER_NAME,se.se_paper_id,sp.paper_name, sq.question_type,sum(sea.answer_of_score) as scores,sea.attribute_10 from se_examinee_answer sea,se_paper sp ,se_question sq,se_examinee se,a where sea.se_examinee_id = se.id and sea.se_question_id = sq.id and sp.id = se.se_paper_id and a.sys_user_id = se.sys_user_id and a.se_paper_id = se.se_paper_id and a.exam_num = sea.attribute_10 and se.status = '3' and sp.reply_num is not null and sp.reply_grade is not null and sea.attribute_10 is not null group by se.sys_user_id,se_exam_p.Get_Employee_Names(se.sys_user_id),se.se_paper_id,sp.paper_name,sq.question_type,sea.attribute_10 )select b.sys_user_id,b.SYS_USER_NAME,s.id as se_examinee_id,b.se_paper_id,b.paper_name,b.attribute_10 as exam_num,s.real_end_time as exam_start_time ,max(decode(b.question_type, '1', scores,0)) dx_scores1,--单选 max(decode(b.question_type, '2', scores,0)) duox_scores2,--多选 max(decode(b.question_type, '3', scores,0)) tk_scores3,--填空 max(decode(b.question_type, '4', scores,0)) pd_scores4,--判断题 max(decode(b.question_type, '5', scores,0)) jd_scores5--简单题 from b left join se_examinee s on b.se_paper_id = s.se_paper_id and b.sys_user_id =s.sys_user_id group by b.sys_user_id,b.SYS_USER_NAME,s.id,b.se_paper_id,b.paper_name,b.attribute_10,s.real_end_time order by b.sys_user_id,b.se_paper_id,b.attribute_10 解决了,感谢大家的回答,及给予的思路,赋上解决sql给网友参考
x 11 1 4
x 11 2 6
你看着两列,我要得到:x 11 4 6
decode(z,1,a)a
decode(z,2,a)b
(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;
接近楼主的要求了,但还不完美!
from (
select x,y,z,a from test where (x,y) in
(select x,max(to_number(y)) from test group by x)
--group by x,y
order by x,y ) t -- ,wmsys.wm_concat(A)
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
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
,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
,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
最多五种的话,就这样写
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
UNION ALL
SELECT 'y', 16, 7, 9 FROM DUAL),
t1 as
(select x, y, a, rn
from (select x,
y,
a,
dense_rank() over(partition by x order by y desc) dr,
row_number() over(partition by x order by y desc) rn
from t)
where dr = 1)
select * from t1 pivot(max(a) for rn in(1, 2, 3, 4, 5))
from (
select x,y,z,a from test where (x,y) in
(select x,max(to_number(y)) from test group by x)
--group by x,y
order by x,y ) t -- ,wmsys.wm_concat(A)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 (
create or replace view se__examination_v as
with a as (select se.sys_user_id,se_exam_p.Get_Employee_Names(se.sys_user_id) as SYS_USER_NAME,se.se_paper_id,sp.paper_name,
max(sea.attribute_10) as exam_num
from se_examinee_answer sea,se_paper sp ,se_question sq,se_examinee se
where sea.se_examinee_id = se.id and sea.se_question_id = sq.id and sp.id = se.se_paper_id
and se.status = '3' and sp.reply_num is not null and sp.reply_grade is not null and sea.attribute_10 is not null
group by se.sys_user_id,se_exam_p.Get_Employee_Names(se.sys_user_id),se.se_paper_id,sp.paper_name),b as (select se.sys_user_id,se_exam_p.Get_Employee_Names(se.sys_user_id) as SYS_USER_NAME,se.se_paper_id,sp.paper_name,
sq.question_type,sum(sea.answer_of_score) as scores,sea.attribute_10
from se_examinee_answer sea,se_paper sp ,se_question sq,se_examinee se,a
where sea.se_examinee_id = se.id and sea.se_question_id = sq.id and sp.id = se.se_paper_id
and a.sys_user_id = se.sys_user_id and a.se_paper_id = se.se_paper_id and a.exam_num = sea.attribute_10
and se.status = '3' and sp.reply_num is not null and sp.reply_grade is not null and sea.attribute_10 is not null
group by se.sys_user_id,se_exam_p.Get_Employee_Names(se.sys_user_id),se.se_paper_id,sp.paper_name,sq.question_type,sea.attribute_10
)select b.sys_user_id,b.SYS_USER_NAME,s.id as se_examinee_id,b.se_paper_id,b.paper_name,b.attribute_10 as exam_num,s.real_end_time as exam_start_time
,max(decode(b.question_type, '1', scores,0)) dx_scores1,--单选
max(decode(b.question_type, '2', scores,0)) duox_scores2,--多选
max(decode(b.question_type, '3', scores,0)) tk_scores3,--填空
max(decode(b.question_type, '4', scores,0)) pd_scores4,--判断题
max(decode(b.question_type, '5', scores,0)) jd_scores5--简单题
from b
left join se_examinee s on b.se_paper_id = s.se_paper_id and b.sys_user_id =s.sys_user_id
group by b.sys_user_id,b.SYS_USER_NAME,s.id,b.se_paper_id,b.paper_name,b.attribute_10,s.real_end_time
order by b.sys_user_id,b.se_paper_id,b.attribute_10
解决了,感谢大家的回答,及给予的思路,赋上解决sql给网友参考