有一个表,表中有如下数据:
id 姓名 语文 数学 英语
1 张三 97 93 null
2 张三 93 92 null
3 张三 null null 80
4 张三 null null 97我想查询出来的结果是这样的:
姓名 语文 数学 英语
张三 97 93 80
张三 93 92 97这样的select语句怎么写,我想和很多方法,老是是有四条数据
希望有大侠指点指点
id 姓名 语文 数学 英语
1 张三 97 93 null
2 张三 93 92 null
3 张三 null null 80
4 张三 null null 97我想查询出来的结果是这样的:
姓名 语文 数学 英语
张三 97 93 80
张三 93 92 97这样的select语句怎么写,我想和很多方法,老是是有四条数据
希望有大侠指点指点
drop table t purge;create table t(
id number(18,0),
xm varchar2(20),
yw number(18,2),
sx number(18,2),
yy number(18,2)
);insert into t(id,xm,yw,sx,yy) values(1,'张三',97,93,null);
insert into t(id,xm,yw,sx,yy) values(2,'张三',93,92,null);
insert into t(id,xm,yw,sx,yy) values(3,'张三',null,null,80);
insert into t(id,xm,yw,sx,yy) values(4,'张三',null,null,97);commit;
select t1.id, t1.xm, nvl(t1.yw,t2.yw) as "语文",
nvl(t1.sx,t2.sx) as "数学", nvl(t1.yy, t2.yy) as "英语"
from t t1 left join t t2 on t1.id=t2.id-2
where nvl(t1.yw,t2.yw) is not null
and nvl(t1.sx,t2.sx) is not null
and nvl(t1.yy, t2.yy) is not null;
可能你就这不明白? 这清除回收站其他的应该没有看不懂的吧
(
select 1 id,'张三' a,97 b,93 c from dual
union all
select 2,'张三',93,92 from dual
union all
select 3,'张三',80,0 from dual
union all
select 4,'张三',97,0 from dual
)select distinct b.id,b.a 姓名,b.b 语文,b.c 数学,a.b 英语
from
(
select id,a,b,c,mod(id,2) d from a
)b,
(
select id,a,b,c,mod(id,2) d from a
)a
where a.d=b.d and b.id<a.id
order by b.id--RESULT:
1 张三 97 93 80
2 张三 93 92 97
现在表里有下面几条数据:
id 姓名 语文 数学 英语
1 张三 97 93 null
2 张三 93 92 null
3 李四 89 98 null
4 李四 null null 81
5 张三 null null 80
6 张三 null null 97
我要写一个select语句,查询结果为:
姓名 语文 数学 英语
张三 97 93 80
张三 93 92 97
李四 89 98 81
根据姓名来做关联,把相同名字的人的分数放在一行里。
如果没有的话,可以这样解决
select XM, sum(decode(i,1,V,0)),sum(decode(i,2,v,0)),sum(decode(i,3,v,0)) from
(
select XM,YW V,1 i from t
union all
select XM,SX V,2 i from t
union all
select XM,YY V,3 i from t
) where V is not null
group by xm
如
id 姓名 语文 数学 英语 序号
1 张三 97 93 null 1
2 张三 93 92 null 2
3 李四 89 98 null 1
4 李四 null null 81 2
5 张三 null null 80 1
6 张三 null null 97 2分组条件由姓名变成按姓名+序号。
2 张三 93 92 null
3 李四 89 98 null
4 李四 null null 81
5 张三 null null 80
6 张三 null null 97
像这种数据,张三会不会还有,如有三个张三的数据,四个张三的数据........
先按姓名对整个表排序,然后从前往后检查,只要当前一条记录的成绩有NULL值,就从后面的记录中找,找到后替换当前的NULL值,并把取值记录中的值改为NULL。
循环完成后,select出那些不是全NULL的记录就可以了。
虽然慢点,但是应该能够处理这种数据。
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;
(
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
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
*/
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,'张三' 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
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 a as 姓名,sum(b) as 语文,sum(c) as 数学 ,sum(d) as 英语 from a group by mod(id,2),a
--RESULT:张三 93 92 178
张三 186 191 80结果就错了!!
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