游标 vs Join

一、游标

DECLARE @time DATETIME = GETDATE()
DECLARE @ms INT


DECLARE @t_NumberOfPeriodRecord AS TABLE
(
    FNumberOfPeriod VARCHAR(30) NOT NULL,
    FNumberOfPeriodNum INT NOT NULL
)

INSERT INTO @t_NumberOfPeriodRecord(FNumberOfPeriod,FNumberOfPeriodNum)
SELECT FNumberOfPeriod ,COUNT(FNumberOfPeriod) FROM dbo.TOrders
GROUP BY FNumberOfPeriod
ORDER BY COUNT(FNumberOfPeriod) DESC


DECLARE @Result AS TABLE
(
    FID INT,
    FOrderNumber VARCHAR(100),
    FGameID INT,
    FPeriodID INT,
    FUserID INT,
    FCompanyID INT,
    FNumberOfPeriod VARCHAR(30)
)


DECLARE @fNumberOfPeriod VARCHAR(30)

DECLARE you_biao CURSOR FOR SELECT FNumberOfPeriod FROM @t_NumberOfPeriodRecord
OPEN you_biao;
FETCH NEXT FROM you_biao INTO @fNumberOfPeriod
WHILE(@@fetch_status=0)
BEGIN
    IF EXISTS(SELECT * FROM @t_NumberOfPeriodRecord)
    BEGIN
        INSERT INTO @Result(FID,FOrderNumber,FGameID,FPeriodID,FUserID,FCompanyID,FNumberOfPeriod)
        SELECT FID,FOrderNumber,FGameID,FPeriodID,FUserID,FCompanyID,FNumberOfPeriod 
        FROM dbo.TOrders 
        WHERE FNumberOfPeriod=@fNumberOfPeriod 
    END 

    FETCH NEXT FROM you_biao INTO @fNumberOfPeriod
END 
CLOSE you_biao
DEALLOCATE you_biao


--SELECT * FROM @Result 
--ORDER BY FPeriodID 

SET @ms=DATEDIFF(ms,@time,GETDATE())
SELECT '游标', @ms AS '毫秒' --毫秒数

 

二、JOIN

DECLARE @time DATETIME = GETDATE()
DECLARE @ms INT


DECLARE @t_NumberOfPeriodRecord AS TABLE
(
    FNumberOfPeriod VARCHAR(30) NOT NULL,
    FNumberOfPeriodNum INT NOT NULL
)

INSERT INTO @t_NumberOfPeriodRecord(FNumberOfPeriod,FNumberOfPeriodNum)
SELECT FNumberOfPeriod ,COUNT(FNumberOfPeriod) FROM dbo.TOrders
GROUP BY FNumberOfPeriod
ORDER BY COUNT(FNumberOfPeriod) DESC


DECLARE @Result AS TABLE
(
    FID INT,
    FOrderNumber VARCHAR(100),
    FGameID INT,
    FPeriodID INT,
    FUserID INT,
    FCompanyID INT,
    FNumberOfPeriod VARCHAR(30)
)

INSERT INTO @Result(FID,FOrderNumber,FGameID,FPeriodID,FUserID,FCompanyID,FNumberOfPeriod)
SELECT FID,FOrderNumber,FGameID,FPeriodID,FUserID,FCompanyID,FNumberOfPeriod 
FROM dbo.TOrders 
WHERE FNumberOfPeriod IN (SELECT FNumberOfPeriod FROM  @t_NumberOfPeriodRecord) 


--SELECT * FROM @Result
--ORDER BY FPeriodID 

SET @ms=DATEDIFF(ms,@time,GETDATE())
SELECT 'Join',@ms AS '毫秒' --毫秒数