表连接
表1 表2
NO BOOK BOOK AUTHOR
125 b12 b01 wang
125 b13 b12 li
b13 b13 li
124 b12 b42 zhao
124 b12 b65 zhao
123 b65
122 b42
122 b01
如上为图书馆例子，NO为学号 ，要找出所有读了li 写的“所有”的书的学生学号。如上即为从表1 里选出 125
怎么办啊？？ 我只会建个临时的view 再从里面挑
最好解释下阿，谢谢！！
[解决办法]
if object_id( 'pubs..A ') is not null
drop table A
go
create table A(NO varchar(10),BOOK varchar(10))
insert into A(NO,BOOK) values( '125 ', 'b12 ')
insert into A(NO,BOOK) values( '125 ', 'b13 ')
insert into A(NO,BOOK) values( '124 ', 'b12 ')
insert into A(NO,BOOK) values( '124 ', 'b12 ')
insert into A(NO,BOOK) values( '123 ', 'b65 ')
insert into A(NO,BOOK) values( '122 ', 'b42 ')
insert into A(NO,BOOK) values( '122 ', 'b01 ')
go
if object_id( 'pubs..B ') is not null
drop table B
go
create table B(BOOK varchar(10),AUTHOR varchar(10))
insert into B(BOOK,AUTHOR) values( 'b01 ', 'wang ')
insert into B(BOOK,AUTHOR) values( 'b12 ', 'li ')
insert into B(BOOK,AUTHOR) values( 'b13 ', 'li ')
insert into B(BOOK,AUTHOR) values( 'b42 ', 'zhao ')
insert into B(BOOK,AUTHOR) values( 'b65 ', 'zhao ')
go
declare @AUTHOR as varchar(10)
set @author = 'li '
select no from
(
select distinct * from
(
select * from A where book in (select book from B where author = @author)
) t
) m
group by no having count(*) = (select count(*) from B where author = @author)
drop table A,B
/*
no
----------
125
（所影响的行数为 1 行）
*/
