有两个表,例如:
table1:
姓名 性别 年龄
张三 男 22
李四 男 19table2:
姓名 科目 成绩
张三 数学 88
张三 语文 78
李四 数学 79
李四 语文 91我想查询的结果是这样的姓名 数学 语文
张三 88 78
李四 79 91
table1:
姓名 性别 年龄
张三 男 22
李四 男 19table2:
姓名 科目 成绩
张三 数学 88
张三 语文 78
李四 数学 79
李四 语文 91我想查询的结果是这样的姓名 数学 语文
张三 88 78
李四 79 91
a.姓名,
max(decode(b.科目,'数学',b.成绩)) as 数学,
max(decode(b.科目,'语文',b.成绩)) as 语文
from
table1 a,table2 b
where
a.姓名=b.姓名
group by
a.姓名
v_sql varchar2(4000):='select a.姓名,';
cursor cur_name is select distinct 科目 from table2;
begin
for c in cur_name loop
v_sql:=v_sql||'max(decode(b.科目,'||c.科目||',b.成绩)) "'||c.科目||'",';
end loop;
v_sql:=substr(v_sql,1,length(v_sql)-1)||' from table1 a,table2 b where a.姓名=b.姓名 group by a.姓名';
execute immediate v_sql;
insert into table1
select '张三','男',22 from dual
union
select '李四','男',19 from dual;
create table table2(cname varchar2(100),km varchar2(100),cj int )
insert into table2
select '张三','数学',88 from dual
union
select '张三','语文',78 from dual
union
select '李四','数学',79 from dual
union
select '李四','语文',91 from dual;select
a.cname,
max(decode(b.km,'数学',b.cj)) as 数学,
max(decode(b.km,'语文',b.cj)) as 语文
from
table1 a,table2 b
where
a.cname=b.cname
group by
a.cname
所以最好用pl/sql实现,比较好
(select b.stu_score from dev.table2 b where b.stu_name=a.stu_name and b.stu_course='数学') math,
(select b.stu_score from dev.table2 b where b.stu_name=a.stu_name and b.stu_course='语文') chinese
from dev.table1 a
select A.cname,b.cj as 数学,c.cj as 语文
from Table1 A,table2 B,table2 C
where A.cname = B.cname
and a.cname = c.cname
and b.km<>c.km
and b.km='数学'
这样写可以完成功能,但是有缺陷
max(decode(a.control_name,''部门'',a.control_value ))OVER (PARTITION BY a.organization_id,a.organization_name,a.code) 部门,
max(decode(a.control_name,''申请人'',a.control_value ))OVER (PARTITION BY a.code) 申请人,
max(decode(a.control_name,''申请时间'',a.control_value ))OVER (PARTITION BY a.code) 申请时间
from operation_value a,
(select organization_id ,organization_name ,control_name from operation_value
group by organization_id ,organization_name ,control_name )b
where a.organization_id=b.organization_id and a.control_name=b.control_name
select name ,nvl(sum("语文"),0) "语文",nvl(sum("数学"),0) "数学",nvl(sum("英语"),0) "英语"
from (select name,
case when Course='语文' then nvl(SUM(score),0) end "语文",
case when Course='数学' then nvl(SUM(score),0) end "数学",
case when Course='英语' then nvl(SUM(score),0) end "英语"from lht_test
group by name,course) t
group by name
max(decode(b.subject,'数学',b.score)) as "数学",
max(decode(b.subject,'语文',b.score)) as "语文"
from table1 a,table2 b
where a.name = b.name
group by b.name;
insert into stu
select '张三','男',25
union all
select '李四','男',19 create table score(name varchar2(10), sub varchar2(10), int)
insert into score
select '张三','数学',88 from dual
union all
select '张三','语文',78 from dual
union all
select '李四','数学',79 from dual
union all
select '李四','语文',89 from dual
select name, max(Case When sub ='数学' Then Else 0 End) As [数学],
max(case when sub='语文' then else 0 end ) as [语文]
from score
group by name
测试结果:
name 数学 语文
---------- ----------- -----------
李四 79 89
张三 88 78(所影响的行数为 2 行)楼主,试试
insert into #stu
select '张三','男',25
union all
select '李四','男',19 create table #score(name varchar2(10), sub varchar2(10), int)
insert into #score
select '张三','数学',88 from test
union all
select '张三','语文',78 from test
union all
select '李四','数学',79 from test
union all
select '李四','语文',89 from test
select name, max(Case When sub ='数学' Then Else 0 End) As [数学],
max(case when sub='语文' then else 0 end ) as [语文]
from #score
group by name
测试结果:
(所影响的行数为 2 行)
(所影响的行数为 12 行)name 数学 语文
---------- ----------- -----------
李四 79 89
张三 88 78(所影响的行数为 2 行)