34,587
社区成员
发帖
与我相关
我的任务
分享
联机丛书-自联接-自定义联接 有详细描述
create proc procname
(
@TeacherName varchar(20)=null
)
as
set nocount on
if @TeacherName is not null
select * from tbname where TeacherName=@TeacherName
else
select * from tbname t where not exists (select 1 from tbname where ProgramID=t.ProgramID and TeacherID<t.TeacherID)
set nocount off
go
exec procname
exec procname '张三'
--> 测试数据:#
if object_id('tempdb.dbo.#') is not null drop table #
create table #(TeacherID int, TeacherName varchar(8), ProgramID int, ProgramName varchar(8))
insert into #
select 12, '张三', 7, '院级项目' union all
select 13, '李四', 7, '院级项目' union all
select 14, '王五', 7, '院级项目' union all
select 13, '张三', 9, '系级项目' union all
select 18, '小三', 9, '系级项目' union all
select 14, '王五', 9, '系级项目'
-- 最小TeacherID
select * from # t where not exists (select 1 from # where ProgramID=t.ProgramID and TeacherID<t.TeacherID)
/*
TeacherID TeacherName ProgramID ProgramName
----------- ----------- ----------- -----------
12 张三 7 院级项目
13 张三 9 系级项目
*/
-- 最大TeacherID
select * from # t where not exists (select 1 from # where ProgramID=t.ProgramID and TeacherID>t.TeacherID)
/*
TeacherID TeacherName ProgramID ProgramName
----------- ----------- ----------- -----------
14 王五 7 院级项目
18 小三 9 系级项目
*/
-- 随机
select * from # t where TeacherID = (select top 1 TeacherID from # where ProgramID=t.ProgramID order by newid())
/*
TeacherID TeacherName ProgramID ProgramName
----------- ----------- ----------- -----------
14 王五 7 院级项目
13 张三 9 系级项目
*/