时间戳

首页 > 解决方案 > SQL：如何将查询结果保存为新的临时表？

sql - SQL：如何将查询结果保存为新的临时表？

问题描述

我是 SQL 的初学者。如何将A部分的查询结果（已经有多个使用WITH子句的子查询）保存为一个新的临时表，以便我可以将它用于B部分的查询（因为B部分的聚合是基于A部分的） ? 最后，我想分别获得 A 部分和 B 部分的结果。我试图从其他帖子中解决，但它似乎不适用于我的问题，因为我有多个使用 WITH 子句的子查询）。预先感谢您的帮助！

/****** Part A ******/
WITH DL_source AS
    (SELECT
        LogID
        ,HashID
        ,DeviceDateTime
        ,LAG(DeviceDateTime, 1) OVER (PARTITION BY HashID ORDER BY HashID, DeviceDateTime, LogID) AS Last_DeviceDateTime
        ,TotalSecondsSpent
        ,PageViews
        ,Pages
        ,PrevPage
        ,IEM
        ,CommCode
        ,ContentTag
        ,SearchKeyword
        ,TaggedMember
        ,TargetMember
    FROM (SELECT
       ROW_NUMBER() OVER(ORDER BY HashID, DeviceDate, HourOfDay, MinOfHour, SecOfMin) AS LogID
       ,HashID
       ,CAST(DeviceDate AS DATETIME)                                                                
       + CAST(TIMEFROMPARTS(HourOfDay, MinOfHour, SecOfMin,0 ,0) AS DATETIME) AS DeviceDateTime
       ,TotalSecondsSpent
       ,PageViews
       ,Pages
       ,PrevPage
       ,IEM
       ,CommCode
       ,ContentTag
       ,SearchKeyword
       ,TaggedMember
       ,TargetMember
    FROM [DM_RAW].[dbo].[WebAnalyticsVisit]                                 
    WHERE DeviceDate IS NOT NULL
    AND DeviceDate <> '1900-01-01'
    AND HashID IS NOT NULL
    AND HashID <> '') AS DL_DateTime),
DL_Session_Source AS (
    SELECT
        LogID
        ,HashID
        ,DeviceDateTime
        ,Last_DeviceDateTime
        ,DATEDIFF(MINUTE, DeviceDateTime, LEAD(DeviceDateTime, 1) OVER(PARTITION BY HashID ORDER BY DeviceDateTime ASC)) AS Min_btw_Page
        ,CASE WHEN DATEDIFF(minute, Last_DeviceDateTime, DeviceDateTime) <30 THEN 0 ELSE 1 END AS New_Session_flag
        ,TotalSecondsSpent
        ,PageViews
        ,Pages
        ,PrevPage
        ,IEM
        ,CommCode
        ,ContentTag
        ,SearchKeyword
        ,TaggedMember
        ,TargetMember
    FROM DL_source)
SELECT
        LogID
        ,HashID
        ,DeviceDateTime
        ,Last_DeviceDateTime
        ,Min_btw_Page
        ,New_Session_flag
        ,SUM(New_Session_flag) OVER (ORDER BY HashID, DeviceDateTime, LogID) AS Session_Num
        ,TotalSecondsSpent
        ,PageViews
        ,Pages
        ,PrevPage
        ,IEM
        ,CommCode
        ,ContentTag
        ,SearchKeyword
        ,TaggedMember
        ,TargetMember
FROM DL_Session_Source

/****** Part B ******/
SELECT *,
    Session_Num
    ,HashID
    ,COUNT(*) AS Num_Page
    ,MIN(DeviceDateTime) AS First_Page
    ,MAX(DeviceDateTime) AS Last_Page
FROM #cte_Visit_Record_cheunghm4532
GROUP BY Session_Num, HashID

标签： sqlvariablessubquerycommon-table-expressiontemp-tables

解决方案

Quick'n'easy 选项：当您到达 A 部分底部的 SELECT 部分时，将INTO表达式放入其中。

...请注意这些陈述，为了简洁/清晰，我减少了字段等的数量并替换为。

WITH DL_source AS
    (SELECT
    ... ),
DL_Session_Source AS (
    SELECT ...
    FROM DL_source)
SELECT
        LogID
        ,HashID
        ...
        ,TargetMember
INTO #cte_Visit_Record_cheunghm4532        -- Added this row
FROM DL_Session_Source;

请注意，上面创建了一个新表 - 它不会插入到现有表中。

更彻底/更灵活的版本是首先创建临时表，然后代替上面的 SELECT ，使其成为 INSERT 例如，

CREATE TABLE #cte_Visit_Record_cheunghm4532 (LogID int, HashID varchar(32), ...);

WITH DL_source AS
    (SELECT
    ... ),
DL_Session_Source AS (
    SELECT ...
    FROM DL_source)
INSERT INTO #cte_Visit_Record_cheunghm4532 (LogID, HashID, ...)
   SELECT LogID
         ,HashID
         ...
   FROM DL_Session_Source;

推荐阅读