1.MSSQL 2000
DECLARE @tmpDepartments TABLE(SerialID INT IDENTITY(1,1),DepartmentsID INT,DepartmentsCode VARCHAR(50)) --事业部表 CREATE TABLE #tmpAgencies(SerialID INT IDENTITY(1,1),AgenciesID INT,DepartmentsID INT) --机构表
注:临时表可以进行TRUNCATE TABLE操作,表变量不可以。TRUNCATE TABLE操作后,自增序列又会从1开始计数。
2.MSSQL2005
ALTER PROCEDURE [dbo].[GetDealCustomerCountByUser]
AS BEGIN DECLARE @result TABLE(UserID INT,DealCustomerCount INT,Serial INT)--添加Serial字段作为自增序列 INSERT INTO @result --ROW_NUMBER() OVER(ORDER BY UserID) 按UserID排序由1开始自增 SELECT DISTINCT UserID,0 AS DealCustomerCount,Serial=row_number() over(ORDER BY UserID)
FROM CRM_Log_CheckTempCustomer WHERE UpdateTime>=@fromDate AND UpdateTime<@toDate GROUP BY UserID ORDER BY UserID; END