select t.name name, t.class class, t.course course, t.credit credit ( select c.name name,c.class class,c.course course,c.credit credit from class c group by c.name union select a.name name,null class,null c.course,sum(c.credit) credit from class a group by a.name ) t group by t.name
/******************************************************** name class course credit n1 1 English 80 n1 1 Chinese 70 n1 1 Histroy 95 245 n2 1 Chinese 65 n2 1 English 75 n2 1 History 100 240 ********************************************************/
select decode(t.class,null,null,t.name) name t.class class, t.course course, t.credit credit ( select c.name name,c.class class,c.course course,c.credit credit from class c group by c.name union select a.name name,null class,null c.course,sum(c.credit) credit from class a group by a.name ) t group by t.name
/******************************************************** name class course credit n1 1 English 80 n1 1 Chinese 70 n1 1 Histroy 95 245 n2 1 Chinese 65 n2 1 English 75 n2 1 History 100 240 ********************************************************/
谢谢大家,另外 select decode(t.class,null,null,t.name) name t.class class, t.course course, t.credit credit ( select c.name name,c.class class,c.course course,c.credit credit from class c group by c.name union select a.name name,null class,null c.course,sum(c.credit) credit from class a group by a.name ) t group by t.name 这个语句是不是有问题,经测试无法使用谢谢(dinya2003(OK)),如果可以的话解释一下这段代码.我没用过这种代码,谢谢!现提供完整的建库代码,希望帮我解决(另外再加分200,问题解决了就可以领分,贴子马上就发) drop table class; create table class (name char(10),class INTEGER(2),course char(10),credit INTEGER(3)); insert into class (name,class,course,credit) values ('n1',1,'English',80); insert into class (name,class,course,credit) values ('n1',1,'chinese',70); insert into class (name,class,course,credit) values ('n1',1,'Histroty',95); insert into class (name,class,course,credit) values ('n2',1,'chinese',65); insert into class (name,class,course,credit) values ('n2',1,'English',75); insert into class (name,class,course,credit) values ('n2',1,'Histroty',100);
请问楼主,你使用的是什么数据库,什么版本号?我感觉不是oracle.
SQL> compute sum label 'sum(credit' of credit on name SQL> break on name SQL> select * from class ;NAME CLASS COURSE CREDIT ---------- ---------- ---------- ---------- n1 1 English 80 1 chinese 70 1 Histroty 95 ********** ---------- sum(credit 245 n2 1 chinese 65 1 English 75 1 Histroty 100 ********** ---------- sum(credit 240 已选择6行。SQL> spool off
select name,class,course,credit from ( select name,class,course,credit from szy union select name,null class,null course,sum(credit) credit from szy group by name ) order by name
select name,class,course,credit from ( select name,class,course,credit from class union select name,null class,null course,sum(credit) credit from class group by name ) order by name
t.name name,
t.class class,
t.course course,
t.credit credit
(
select c.name name,c.class class,c.course course,c.credit credit from class c group by c.name
union
select a.name name,null class,null c.course,sum(c.credit) credit from class a group by a.name
)
t
group by
t.name
/********************************************************
name class course credit
n1 1 English 80
n1 1 Chinese 70
n1 1 Histroy 95
245
n2 1 Chinese 65
n2 1 English 75
n2 1 History 100
240
********************************************************/
decode(t.class,null,null,t.name) name
t.class class,
t.course course,
t.credit credit
(
select c.name name,c.class class,c.course course,c.credit credit from class c group by c.name
union
select a.name name,null class,null c.course,sum(c.credit) credit from class a group by a.name
)
t
group by
t.name
/********************************************************
name class course credit
n1 1 English 80
n1 1 Chinese 70
n1 1 Histroy 95
245
n2 1 Chinese 65
n2 1 English 75
n2 1 History 100
240
********************************************************/
select
decode(t.class,null,null,t.name) name
t.class class,
t.course course,
t.credit credit
(
select c.name name,c.class class,c.course course,c.credit credit from class c group by c.name
union
select a.name name,null class,null c.course,sum(c.credit) credit from class a group by a.name
)
t
group by
t.name
这个语句是不是有问题,经测试无法使用谢谢(dinya2003(OK)),如果可以的话解释一下这段代码.我没用过这种代码,谢谢!现提供完整的建库代码,希望帮我解决(另外再加分200,问题解决了就可以领分,贴子马上就发)
drop table class;
create table class (name char(10),class INTEGER(2),course char(10),credit INTEGER(3));
insert into class (name,class,course,credit) values ('n1',1,'English',80);
insert into class (name,class,course,credit) values ('n1',1,'chinese',70);
insert into class (name,class,course,credit) values ('n1',1,'Histroty',95);
insert into class (name,class,course,credit) values ('n2',1,'chinese',65);
insert into class (name,class,course,credit) values ('n2',1,'English',75);
insert into class (name,class,course,credit) values ('n2',1,'Histroty',100);
SQL> break on name
SQL> select * from class ;NAME CLASS COURSE CREDIT
---------- ---------- ---------- ----------
n1 1 English 80
1 chinese 70
1 Histroty 95
********** ----------
sum(credit 245
n2 1 chinese 65
1 English 75
1 Histroty 100
********** ----------
sum(credit 240 已选择6行。SQL> spool off
http://community.csdn.net/Expert/topic/2634/2634127.xml?temp=.3534662
另外你是怎么做到的,你的SQL语句不完整 谢谢.
(
select name,class,course,credit from szy
union
select name,null class,null course,sum(credit) credit from szy group by name
)
order by name
(
select name,class,course,credit from class
union
select name,null class,null course,sum(credit) credit from class group by name
)
order by name