显示作者姓名,书名为谁写的多本书籍
CREATE TABLE Book_Master(
Book_code NUMBER(10) PRIMARY KEY,
Book_name VARCHAR2(50) NOT NULL,
Book_pub_year NUMBER,
Book_pub_author VARCHAR2 (50) NOT NULL);
INSERT INTO book_master VALUES(10000001,'Let Us C++',2000,'Yashavant Kanetkar');
INSERT INTO book_master VALUES(10000002,'Mastersing VC++',2005,'P.J Allen');
INSERT INTO book_master VALUES(10000003,'JAVA Complete Reference',2004,'H.Schild');
INSERT INTO book_master VALUES(10000004,'J2EE Complete Reference',2000,'H. Schild');
INSERT INTO book_master VALUES(10000005,'Relational DBMS',2000,'B.C. Desai');
INSERT INTO book_master VALUES(10000006,'Let Us C',2000, 'Yashavant Kanetkar');
INSERT INTO book_master VALUES(10000007,'Intoduction To Algorithams',2001,'Cormen');
INSERT INTO book_master VALUES(10000008,'Computer Networks',2000,'Tanenbaum');
INSERT INTO book_master VALUES(10000009,'Introduction to O/S',2001,'Millan');
select Book_pub_author, Book_name
from book_master
where Book_pub_author in (select Book_pub_author
from book_master
group by Book_pub_author
having count(1)>1)
order by Book_pub_author, Book_name
Thx为您提供帮助 –
@AbhiramVarma欢迎您。 –
这些作者还可以使用内部联接,而不是IN子句
Select Book_pub_author
, Book_name
from book_master b
INNER JOIN (
select Book_pub_author
from book_master
group by Book_pub_author
having count(1)>1
) t on t.Book_pub_author = b.Book_pub_author
anfd您可以获取所有的书在同一行中使用group_concat
Select Book_pub_author
, group_concat(Book_name)
from book_master b
INNER JOIN (
select Book_pub_author
from book_master
group by Book_pub_author
having count(1)>1
) t on t.Book_pub_author = b.Book_pub_author
group by Book_pub_author
Thx为您的帮助 –
@AbhiramVarma如果其中一个答案是正确的,请选择一个你喜欢的,并将其标记为已接受...看看这里如何 http://meta.stackexchange.com/questions/5234/how-不接受-的回答工作 – scaisEdge
认真对待作业。显示一些努力。 –