创建自定义表值函数   获取此id教师下的学生列表  
create function getStudent(@tid int)  
returns @tmp table(sid int ,tid int,sName varchar(10))  
as  
begin  
    with cte(sid, tid, sName)  
    as  
    (  
        select sid, tid, sName from Student where tid=@tid  
    )  
    insert into @tmp select * from cte  
    return  
end  
--查询调用  与上面不用表值函数的写法对照一下，会好理解点  
select * from Teacher t cross apply getStudent(t.tid)--在查询中调用表值函数  
--查询结果  
tid         tName      sid         tid         sName  
----------- ---------- ----------- ----------- ----------  
1           li         1           1           zhou  
1           li         2           1           wu  
2           zhang      3           2           zheng  
2           zhang      4           2           wang  
  
(4 行受影响)