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;
好象这样也可以,不过可能效率要比子查询差一点
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