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

浏览数：17 / 时间：2015年06月12日 
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

 

郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。

标签： des class style 使用 log si it la sp des class style 使用 log si it la sp

相关文章

随机文章

您可能还喜欢

您可能还喜欢