1.分批取數 declare @P1 int set @P1=180150000 declare @P2 int set @P2=8 declare @P3 int set @P3=1 declare @P4 int set @P4=3 exec sp_cursoropen @P1 output, N''''select top 3 * from authors'''', @P2 output, @P3 output, @P4 output select @P1 , @P2 , @P3 , @P4 go exec sp_cursorfetch 180150000, 16, 1, 1 go exec sp_cursorfetch 180150000, 16, 2, 1 go exec sp_cursorfetch 180150000, 16, 3, 1 go exec sp_cursorfetch 180150000, 16, 4, 1 go exec sp_cursorclose 180150000 go
exec sp_cursorfetch 180150000, 16, 1, 10 --從第1筆起,取10筆 exec sp_cursorclose 180150000 go
2.取相關ID的數據表
The table-valued function fn_FindReports(InEmpID), which -- given an Employee ID -- returns a table corresponding to all the employees that report to the given employee directly or indirectly. This logic is not expressible in a single query and is a good candidate for implementing as a user-defined function.
CREATE FUNCTION fn_FindReports (@InEmpId nchar(5)) RETURNS @retFindReports TABLE (empid nchar(5) primary key, empname nvarchar(50) NOT NULL, mgrid nchar(5), title nvarchar(30)) /*Returns a result set that lists all the employees who report to given employee directly or indirectly.*/ AS BEGIN DECLARE @RowsAdded int -- table variable to hold accumulated results DECLARE @reports TABLE (empid nchar(5) primary key, empname nvarchar(50) NOT NULL, mgrid nchar(5), title nvarchar(30), processed tinyint default 0) -- initialize @Reports with direct reports of the given employee INSERT @reports SELECT empid, empname, mgrid, title, 0 FROM employees WHERE empid = @InEmpId SET @RowsAdded = @@rowcount -- While new employees were added in the previous iteration WHILE @RowsAdded > 0 BEGIN /*Mark all employee records whose direct reports are going to be found in this iteration with processed=1.*/ UPDATE @reports SET processed = 1 WHERE processed = 0 -- Insert employees who report to employees marked 1. INSERT @reports SELECT e.empid, e.empname, e.mgrid, e.title, 0 FROM employees e, @reports r WHERE e.mgrid=r.empid and e.mgrid <> e.empid and r.processed = 1 SET @RowsAdded = @@rowcount /*Mark all employee records whose direct reports have been found in this iteration.*/ UPDATE @reports SET processed = 2 WHERE processed = 1 END -- copy to the result of the function the required columns INSERT @retFindReports SELECT empid, empname, mgrid, title FROM @reports RETURN END GO
[办公软件]在sybase中插入图片、PDF、文本文件 [办公软件]安装Sybase ASE [办公软件]linux指令大全(完整篇) [办公软件]Linux新手入门常用命令大全 [办公软件]在RedHat Linux 9里安装gaim0.80 [办公软件]浅谈Linux 下Java 1.5 汉字方块问题解决方法 [办公软件]Linux程序员必读:中文化与GB18030标准 [办公软件]linux指令大全 [办公软件]制作Linux启动盘的四种方法 [办公软件]Linux文件系统的反删除方法
|