17,086
社区成员
发帖
与我相关
我的任务
分享
with a as(
select 1 id,'张三' a,97 b, 93 c, 0 d from dual
union all
select 2,'张三',93,92,0 from dual
union all
select 3,'张三',93,92,0 from dual
union all
select 4,'李四',89,98,0 from dual
union all
select 5,'李四',0,0,81 from dual
union all
select 6,'张三',0,0,80 from dual
union all
select 7,'张三',0,0,97 from dual
union all
select 8,'张三',0,0,98 from dual
union all
select 9,'王五',87,97,0 from dual
union all
select 10,'王五',0,0,100 from dual
)
select distinct a.id,a.a 姓名,a.b 语文,a.c 数学,b.d 英语
from
(
select id,a,b,c,d,row_number() over(partition by a.a,a.d order by a.id) rm from a
where d=0
)a,
(
select id,a,b,c,d,row_number() over(partition by a.a,a.b,a.c order by a.id) rn from a
where d<>0
)b
where a.a=b.a and a.rm=b.rn
order by a.id
--result:
1 张三 97 93 80
2 张三 93 92 97
3 张三 93 92 98
4 李四 89 98 81
9 王五 87 97 100
with t as
(
select 1 id,'张三' a,97 b,93 c,null d from dual
union all
select 2,'张三',93,92,null from dual
union all
select 3,'张三',null,null,80 from dual
union all
select 4,'张三',null,null,97 from dual
)
select a as 姓名,sum(b) as 语文,sum(c) as 数学 ,sum(d) as 英语 from t group by mod(id,2),a
with a as(
select 1 id,'张三' a,97 b, 93 c, 0 d from dual
union all
select 2,'张三',93,92,0 from dual
union all
select 3,'李四',89,98,0 from dual
union all
select 4,'李四',0,0,81 from dual
union all
select 5,'张三',0,0,80 from dual
union all
select 6,'张三',0,0,97 from dual
)
select distinct a.id,a.a 姓名,a.b 语文,a.c 数学,b.d 英语
from
(
select id,a,b,c,d,row_number() over(partition by a.a,a.d order by a.id) rm from a
where d=0
)a,
(
select id,a,b,c,d,row_number() over(partition by a.a,a.b,a.c order by a.id) rn from a
where d<>0
)b
where a.a=b.a and a.rm=b.rn
order by a.id
--RESULT:
1 张三 97 93 80
2 张三 93 92 97
3 李四 89 98 81
with t as(
select 1 id,'张三' name,97 chinese, 93 math, null english from dual
union all
select 2,'张三',93,92,null from dual
union all
select 3,'李四',89,98,null from dual
union all
select 4,'李四',null,null,81 from dual
union all
select 5,'张三',null,null,80 from dual
union all
select 6,'张三',null,null,97 from dual
),
t1 as(
select name,chinese,row_number() over (order by name,id) rn
from t where chinese is not null order by name,id
),
t2 as(
select name,math,row_number() over (order by name,id) rn
from t where math is not null order by name,id
),
t3 as(
select name,english,row_number() over (order by name,id) rn
from t where english is not null order by name,id
)
select t1.name,t1.chinese,t2.math,t3.english
from t1,t2,t3 where t1.name=t2.name and t2.name=t3.name and t1.rn=t2.rn and t2.rn=t3.rn;
/*
张三 97 93 80
张三 93 92 97
李四 89 98 81
*/
with a as
(
select 1 id,'张三' a,97 b,93 c,0 d from dual
union all
select 2,'张三',93,92,0, from dual
union all
select 3,'张三',0,0,80 from dual
union all
select 4,'张三',0,0,97 from dual
)
select distinct b.id,b.a 姓名,b.b 语文,b.c 数学,a.b 英语
from
(
select id,a,b,c,row_number() over(partition by a.a,a.d order by a.id) rm from a
)a,
(
select id,a,b,c,row_number() over(partition by a.a,a.b,a.c order by a.id) rn from a
)b
where a.a=b.a and a.rm=b.rn
order by b.id
Select name,Sum(yuwen),Sum(shuxue),Sum(yingyu) From (
with test as
(
select 1 id,'张三' Name,97 yuwen,93 shuxue,Null yingyu from dual
union all
select 2,'张三',93,92,Null from dual
union all
select 3,'张三',Null,Null,80 from dual
union all
select 4,'张三',Null,Null,97 from dual
)
Select Name,
nvl(yuwen,0) yuwen,
nvl(shuxue,0) shuxue,
nvl(yingyu,0) yingyu,
Rownum num
From test Where yuwen Is Null And shuxue Is Null And yingyu Is Not Null
Union
Select Name,
nvl(yuwen,0) yuwen,
nvl(shuxue,0) shuxue,
nvl(yingyu,0) yingyu,
Rownum num
From test Where yuwen Is Not Null And shuxue Is Not Null And yingyu Is Null
) temp1 Group By Name,num;