好象这样也可以,不过可能效率要比子查询差一点 declare @work_author_aid integer select @work_author_aid=2 select a.w_id,a.w_name,b.aid,b.w_position from work as a inner join work_author as c on a.w_id=c.w_id and c.aid=@work_author_aid inner join work_author as b on c.w_id=b.w_id and b.position=1
SELECT work.w_id, work.w_name, work_author.aid, work_author.w_position FROM [work] INNER JOIN work_author ON work.w_id = work_author.w_id WHERE (((work_author.aid)="作者") AND ((work_author.w_position)=1)) GROUP BY work.w_id, work.w_name, work_author.aid, work_author.w_position;
只是感觉用IN会不会效率比较低,不知道是否可以转换为用EXISTS,我自己试试吧
我说的返回一条记录的意思是对同一本书有几个作者都只返回第一作者,而如果一个人是10不同书的作者,则需要返回10条记录。
declare @work_author_aid integer
select @work_author_aid=2
select a.w_id,a.w_name,b.aid,b.w_position
from work as a inner join work_author as c
on a.w_id=c.w_id and c.aid=@work_author_aid
inner join work_author as b
on c.w_id=b.w_id and b.position=1
FROM [work] INNER JOIN work_author ON work.w_id = work_author.w_id
WHERE (((work_author.aid)="作者") AND ((work_author.w_position)=1))
GROUP BY work.w_id, work.w_name, work_author.aid, work_author.w_position;