1.
用什么语句可以把下面的多行数据显示为一行?好像用group by不行阿
STUDENTID FIRSTNAME LASTNAME
241234 Pablo Picasso
241234 Pablo Picasso
241234 Pablo Picasso
241234 Pablo Picasso
241234 Pablo Picasso
241234 Pablo Picasso
241234 Pablo Picasso
241234 Pablo Picasso
241234 Pablo Picasso 2.
列出刚刚选择了专业(degreecourse)但没有选择课程(module)的学生的名字(first and last name)。
注意已经入学的学生会有一个入学时间(dateenrolled),而且不会有辍学时间(datewithdrawn)和毕业时间(dategraduated因为还没毕业)
数据:
-------------
degreecourse |
-------------|
courseID(PK) |
courseTitle |
--------------------------
student |
-------------|
studentID{PK)|
Firstname |
Lastname |
courseID(FK) |
dateEnrolled |
dateWithdrawn|
dateGraduated|
-----------------------------
registration |
----------------|
studentID(PK,FK)|
moduleID(PK,FK) |
year(PK) |
semester(PK) |
-----------------------------
module |
-------------|
moduleID(PK) |
moduleTitle |
-------------
====================================================================================
下面是一个错误的代码,仅供参考,不知道怎么错了,结果是未选定行
select firstname,lastname
from student s, degreecourse d, registration r,module m
where s.courseid=d.courseid
and r.studentid=s.studentid
and r.moduleid=m.moduleid
and datewithdrawn is null
and dategraduated is null
and m.moduleid not in
(select moduleid
from registration);
用什么语句可以把下面的多行数据显示为一行?好像用group by不行阿
STUDENTID FIRSTNAME LASTNAME
241234 Pablo Picasso
241234 Pablo Picasso
241234 Pablo Picasso
241234 Pablo Picasso
241234 Pablo Picasso
241234 Pablo Picasso
241234 Pablo Picasso
241234 Pablo Picasso
241234 Pablo Picasso 2.
列出刚刚选择了专业(degreecourse)但没有选择课程(module)的学生的名字(first and last name)。
注意已经入学的学生会有一个入学时间(dateenrolled),而且不会有辍学时间(datewithdrawn)和毕业时间(dategraduated因为还没毕业)
数据:
-------------
degreecourse |
-------------|
courseID(PK) |
courseTitle |
--------------------------
student |
-------------|
studentID{PK)|
Firstname |
Lastname |
courseID(FK) |
dateEnrolled |
dateWithdrawn|
dateGraduated|
-----------------------------
registration |
----------------|
studentID(PK,FK)|
moduleID(PK,FK) |
year(PK) |
semester(PK) |
-----------------------------
module |
-------------|
moduleID(PK) |
moduleTitle |
-------------
====================================================================================
下面是一个错误的代码,仅供参考,不知道怎么错了,结果是未选定行
select firstname,lastname
from student s, degreecourse d, registration r,module m
where s.courseid=d.courseid
and r.studentid=s.studentid
and r.moduleid=m.moduleid
and datewithdrawn is null
and dategraduated is null
and m.moduleid not in
(select moduleid
from registration);
解决方案 »
- 求一条sql语句
- CentOS 5.5 上安装 oracle 10g 出现如下错误,请问如何解决?
- oracle for in 语句中如何设置引用变量
- 提高SQL水平的方式和途径
- oracle 终于装好了,不过Enterprise Manager不能用
- jsp c3p0 oracle 错误ORA-04030:
- 求一个超难sql,高人请进。
- 菜鸟级提问,一条SQL语句,不知道如何修改才能满足需求[请高手看看]
- oracle恢复数据问题
- oracle在什么时候会产生Inactive的Session?
- 请问怎么限制ORACLE使用内存的最高值?
- Oracle数据库的按时间查询问题,很简单,但是我不会
SELECT DISTINCT STUDENTID,FIRSTNAME, LASTNAME FROM TABLE
2、第二个问题
SELECT * FROM STUDENT
WHERE
COURESID IS NOT NULL
AND STUDENTID NOT IN(SELECT * FROM registration)
265478 Alvar Aalto
254988 Henry Moore
242346 Claude Monet
256677 Barbara Epstein
245743 Rachel Whiteread
265676 Ieoh Ming Pei
265479 Antonio Gaudi
267489 Le Corbusier
268548 Aldo Rossi
241234 Pablo Picasso
263734 Beryl Cook
258234 Edvard Munch 我的原代码如下,不知道是不是错了
select s2.studentid,s2.firstname,s2.lastname
from student s1,student s2,registration r1,registration r2
where r1.studentid=s1.studentid
and r2.studentid=r1.studentid
and s2.studentid=r2.studentid
and s2.firstname<>'Salvador'
and s2.lastname<>'Dali'
select firstname,lastname
from student s, degreecourse d, registration r,module m
where s.courseid=d.courseid
and r.studentid=s.studentid
and r.moduleid=m.moduleid
and s.courseid is not null
AND s.STUDENTID NOT IN
(SELECT STUDENTID
FROM registration)
第二个问题select firstname, lastname
from student s, registration r
where s.studentid = r.studentid(+)
and r.studentid is null
and s.datewithdrawn is null
and s.dategraduated is null
修改后这样就行了:
select firstname, lastname
from student s, registration r,degreecourse d
where s.studentid = r.studentid(+)
and s.courseid = d.courseid(+)
and s.courseid is not null
and r.studentid is null
and s.datewithdrawn is null
and s.dategraduated is null
题目是这样的:
用自插方法(self join)列出与Salvador Dali注册在同样课程的学生的学生号码(studentid)和名字(firstname,lastname)-------------
student |
-------------|
studentID{PK)|
Firstname |
Lastname |
courseID(FK) |
dateEnrolled |
dateWithdrawn|
dateGraduated|
-----------------------------
registration |
----------------|
studentID(PK,FK)|
moduleID(PK,FK) |
year(PK) |
semester(PK) |
cw |
exam |
----------------
===============================================================
--load Student with data
insert into Student values
('241234','Pablo','Picasso', 'FA', '01-sep-2004','','');insert into Student values
('242346','Claude', 'Monet', 'FA', '01-sep-2004','','01-jun-2006');insert into Student values
('245743','Rachel', 'Whiteread', 'SCU', '01-sep-2004','','');insert into Student values
('254988','Henry', 'Moore', 'SCU', '01-sep-2005','','01-jun-2006');insert into Student values
('256677','Barbara','Epstein', 'SCU', '01-sep-2005','','');insert into Student values
('258234','Edvard', 'Munch', 'FA', '01-sep-2005','','');insert into Student values
('259234','Salvador', 'Dali', 'FA', '01-sep-2005','','');insert into Student values
('263734','Beryl', 'Cook', 'FA', '01-sep-2006','','');insert into Student values
('265478','Alvar', 'Aalto', 'ARCH', '01-sep-2006','','');insert into Student values
('265676','Ieoh Ming', 'Pei', 'ARCH', '01-sep-2006','','');insert into Student values
('265479','Antonio', 'Gaudi', 'ARCH', '01-sep-2006','','');insert into Student values
('267489','Le', 'Corbusier', 'DCS', '01-sep-2006','','');insert into Student values
('268548','Aldo', 'Rossi', 'IA', '01-sep-2006','01-oct-2006','');insert into Student values
('269111','Damien', 'Hirst', '', '','','');insert into Student values
('269123','Andy', 'Warhol', 'DCS', '01-sep-2006','','');
--load Registration with data
insert into Registration values
('241234','FA101','04','1',90,0); insert into Registration values
('241234','FA111','04','1',45,35); insert into Registration values
('241234','FA201','04','2',78,0); insert into Registration values
('242346','FA101','04','1',76,0); insert into Registration values
('242346','FA111','04','1',65,44); insert into Registration values
('242346','AR131','04','2',72,51); insert into Registration values
('242346','AR231','04','2',56,61); insert into Registration values
('245743','FA101','04','1',52,0);insert into Registration values
('245743','SC101','04','1',72,0);insert into Registration values
('254988','FA101','05','1',59,0);insert into Registration values
('254988','SC101','05','1',88,0);insert into Registration values
('254988','FA201','05','2',67,0);insert into Registration values
('256677','FA101','05','1',34,0);insert into Registration values
('256677','SC101','05','1',98,0);
insert into Registration values
('256677','AR231','05','2',61,51);insert into Registration values
('256677','FA201','05','2',45,0); insert into Registration values
('256677','FA222','05','2',69,0);insert into Registration values
('258234','FA101','05','1',49,0);insert into Registration values
('258234','FA201','05','2',53,0);insert into Registration values
('259234','FA101','05','1',32,18);insert into Registration values
('259234','FA111','05','1',22,12);
insert into Registration values
('263734','FA101','06','1',31,0);insert into Registration values
('263734','SC101','06','1',34,0);insert into Registration values
('263734','FA111','06','1',53,45);insert into Registration values
('263734','FA211','06','2',41,33);insert into Registration values
('263734','AR201','06','2',58,54);insert into Registration values
('265478','AR101','06','1',88,91); insert into Registration values
('265478','AR122','06','1',85,76); insert into Registration values
('265478','AR123','06','2',71,0); insert into Registration values
('265478','AR201','06','2',56,41); insert into Registration values
('265478','FA222','06','2',44,0); insert into Registration values
('265676','AR101','06','1',99,84); insert into Registration values
('265479','AR101','06','1',64,64); insert into Registration values
('265479','AR122','06','1',88,45);insert into Registration values
('265479','FA222','06','2',17,0); insert into Registration values
('265479','AR201','06','2',78,79); insert into Registration values
('265479','AR122','06','2',97,0); insert into Registration values
('267489','AR131','06','1',83,56); insert into Registration values
('267489','AR231','06','1',66,56);insert into Registration values
('267489','AR101','06','1',64,43);insert into Registration values
('268548','AR101','06','1',50,0);拜托大侠了
====================================================
下面是我做的答案,不知道有没有错,select s2.studentid,s2.firstname,s2.lastname
from student s1,student s2,registration r1,registration r2
where r1.studentid=s1.studentid
and r2.studentid=r1.studentid
and s2.studentid=r2.studentid
and s2.firstname<>'Salvador'
and s2.lastname<>'Dali'
insert into Student values
('241234','Pablo','Picasso', 'FA', '01-9月-2004','','');
from student s1,student s2,registration r1,registration r2
where r1.studentid=s1.studentid
and r2.moduleID=r1.moduleID
and s2.studentid=r2.studentid
and s2.studentid<>s1.studentid
and s1.firstname ='Salvador'
and s1.lastname ='Dali' 没测试不知道对不对,我没有搞明白哪个是选课,还有,题目要求是所有的课程都和S.D选的一样 还是只要有一样的就可以