Sql中使用With创建多张临时表

CREATE PROC [dbo].[sp_VisitCount]
(
    @count INT
)
AS BEGIN
DECLARE @current DATETIME
SET @current=GETDATE();

WITH 
TodayVisitTable AS
(
    SELECT CorpName,COUNT(CorpName) AS TodayVisit
    FROM dbo.VisitRecords 
    WHERE YEAR(VisitTime)=YEAR(@current)
    AND MONTH(VisitTime)=MONTH(@current)
    AND DAY(VisitTime)=DAY(@current)
    GROUP BY CorpName
),
TotalVisitTable AS
(
    SELECT CorpName,COUNT(CorpName) AS TotalVisit
    FROM dbo.VisitRecords
    GROUP BY CorpName
),
QUERY AS 
(
    SELECT Row_number() OVER (ORDER BY TodayVisit DESC,TotalVisit DESC) AS RowNum,TotalVisitTable.CorpName,TodayVisit,TotalVisit
    FROM  TotalVisitTable
    LEFT JOIN TodayVisitTable ON TodayVisitTable.CorpName = TotalVisitTable.CorpName
)
SELECT RowNum,CorpName,ISNULL(TodayVisit,0) AS TodayVisit,TotalVisit
  FROM QUERY
 WHERE RowNum <= @count
 END

 

转载于:https://www.cnblogs.com/mtsl/p/4234589.html