求一个查询语句

Question

表: publishBook (专著)
BookNo (PK) 书号
bookName 书名
editor 主编
publishTime 出版时间
publish 出版单位
re 备注表: bookForTeacher (书作者)
bookNo (FK) 书号
TeacherNo (FK) 教职工号表:teacher (老师)
teacherNo (PK) 教职工号
teacherName 教师姓名
department 系别要求显示为:
书名  主编  出版日期  出版单位  全部作者  书号  备注  我系排名最前的作者  我系作者最靠前的排名我系排名最前的作者,比如一共有三个作者,按顺序存放的,那么我系在这三个人当中最前面的叫什么名
举个例子,数据库中的记录:
bookNo   TeacherNo
b001      t004
b002      t001
b002      t002
b002      t003
teacherNo    teacherName    department
t001          张三            管理系
t002          李四            计算机系
t003          王五            计算机系
t004          测试员          测控系
那么 我系(计算机系)排名最前的作者应该为:李四  我系作者最靠前的排名: 2
全部作者就是: 张三,李四,王五

Answer 1

根据teacherNo来排名?
我建议还是搞个排名序号比较好

Answer 2

create table bt(bookNo varchar(10),TeacherNo varchar(10))
insert bt select 'b001','t004'
union all select 'b002','t001'
union all select 'b002','t002'
union all select 'b002','t003'
create table tc(teacherNo varchar(10),teacherName varchar(10),department varchar(10))
insert tc select 't001'   ,       '张三',            '管理系'
union all select 't002',          '李四' ,           '计算机系' 
union all select 't003',          '王五' ,           '计算机系' 
union all select 't004',          '测试员',          '测控系' 
go
create function dbo.getmess(@book varchar(10),@depart varchar(10),@m int)
returns varchar(20)
as
begin
 declare @s varchar(20)
 declare @t table(id int identity,bookno varchar(10),teacherno varchar(10),teachername varchar(10),department varchar(10))
 insert @t select bookno,teacherno,'','' from bt where @book = bookno
 update a set teachername = b.teachername,department = b.department from @t a,tc b where a.teacherno = b.teacherno
 if @m = 2 select @s =rtrim(min(id)) from @t where department = @depart 
 if @m = 1 select @s = teachername from @t where id = (select min(id) from @t  where department = @depart)
 if @m = 0 select @s=isnull(@s+',','')+teachername from @t order by id
 return @s
end
go
 declare @dd varchar(10)
 set @dd = '计算机系' 
 select bookno,dbo.getmess(bookno,@dd,0) 全部作者,dbo.getmess(bookno,@dd,1) 最前作者,dbo.getmess(bookno,@dd,2) 最前名次 from bt a
 where exists(select 1 from tc where teacherno = a.teacherno and department = @dd)
 group by bookno
go
drop table bt,tc
drop function getmess
/*
bookno     全部作者                 最前作者                 最前名次                 
---------- -------------------- -------------------- -------------------- 
b002       张三,李四,王五             李四                   2*/