用函数:CREATE FUNCTION FN_GETLAST(@teacher VARCHAR(30)) RETURNS @R TABLE (student VARCHAR(30),LEVEL INT,...) AS BEGIN DECLARE @T TABLE (student VARCHAR(30),LEVEL INT) DECLARE @LEVEL INT SET @LEVEL=1 INSERT @T SELECT student,1 FROM AA WHERE teacher=@teacher WHILE EXISTS (SELECT 1 FROM AA WHERE teacher IN (SELECT student FROM @T)) BEGIN SET @LEVEL=@LEVEL+1 INSERT @T SELECT student,@LEVEL FROM AA WHERE teacher IN (SELECT student FROM @T) DELETE @T WHERE LEVEL<@LEVEL END INSERT @R(student,LEVEL,...) SELECT A.student ,A.LEVEL,B.... FROM @T A,AA B WHERE A.student=B.student RETURN END --大概吧,没测试
select A.student from aa A where (select count(*) from aa where teacher=A.student) = 0--未测试
RETURNS @R TABLE (student VARCHAR(30),LEVEL INT,...)
AS
BEGIN
DECLARE @T TABLE (student VARCHAR(30),LEVEL INT)
DECLARE @LEVEL INT
SET @LEVEL=1
INSERT @T SELECT student,1 FROM AA WHERE teacher=@teacher
WHILE EXISTS (SELECT 1 FROM AA WHERE teacher IN (SELECT student FROM @T))
BEGIN
SET @LEVEL=@LEVEL+1
INSERT @T SELECT student,@LEVEL FROM AA WHERE teacher IN (SELECT student FROM @T)
DELETE @T WHERE LEVEL<@LEVEL
END
INSERT @R(student,LEVEL,...) SELECT A.student ,A.LEVEL,B.... FROM @T A,AA B WHERE A.student=B.student
RETURN
END
--大概吧,没测试
where (select count(*) from aa where teacher=A.student) = 0--未测试