这种情况如何用SQL语句实现

jimruski

You can use outer join to get what you wanted.

Try this:

create table books (book# interger, author char(30));
insert into books values(123, 'Peter');
insert into books values(123, 'David');
insert into books values(123, 'John');


with temp as (select c1.book# as book, c1.author as author1, c2.author as author2, c3.author as author3
from books as c1 left outer join books as c2
on c1.book# = c2.book# left outer join books as c3 on c2.book# = c3.book#
and c1.author = 'Peter' and c2.author = 'David' and c3.author = 'John')
select * from temp where author1 is not null and author2 is not null and author3 is not null;


Jimruski

最初由 oracle96 发布
[B]I have a table about the book and author relationship.

Book    Author
----    ------
123     Peter
123     David
123     John

How can I use a single sql query to get the result:

Book    Author
----    ------
123     Peter,David,John [/B]

cqing

最初由 jimruski 发布
[B]You can use outer join to get what you wanted.

Try this:

create table books (book# interger, author char(30));
insert into books values(123, 'Peter');
insert into books values(123, 'David');
insert into books values(123, 'John');


with temp as (select c1.book# as book, c1.author as author1, c2.author as author2, c3.author as author3
from books as c1 left outer join books as c2
on c1.book# = c2.book# left outer join books as c3 on c2.book# = c3.book#
and c1.author = 'Peter' and c2.author = 'David' and c3.author = 'John')
select * from temp where author1 is not null and author2 is not null and author3 is not null;


Jimruski


[/B]

如果author是2个呢，是4个呢？你这个太不通用了。

xxx_123_xxx

为什么非要用一个sql实现，有何好处？费解！

shao

很多功能不应该在数据库上纠缠，前台实现是很容易的。

xxx_123_xxx

非常赞成提高sql写作水平，但不要矫枉过正！
论坛上有太多的单句sql偏执狂，精神可嘉，但效果未必最佳！