在输入参数中将查询结果用于公用表表达式 [英] Using result of a query in input parameters for a common table expression

问题描述

我有这样一个查询：

select distinct Project_Id,keyword,SE_Id from Table1 ;

它返回了将近14.000行。

It returns me almost 14.000 rows.

我还有另一个SQL查询，如下所示：

I have another SQL-query which looks like the following:

with DateWithValue as (

SELECT * 
  FROM DateTable as dt LEFT JOIN Table1 as PK on dt.Date_ID=PK.Load_Date and PK.Project_Id=? and Pk.keyword=? and SE_Id=?
LEFT JOIN Table2 as pr on PK.Project_ID=pr.Project_ID
where PK.Domain is not null and 
dt.Date_ID>= (SELECT min(Load_Date) from Table1 where Project_Id=? and keyword=? and SE_Id=? ) and
dt.Date_ID<= (SELECT max(Load_Date) from Table1 where Project_Id=? and keyword=? and SE_Id=? )
--order by Date_ID
)
select * from DateTable as dt Left join DateWithValue on dt.Date_ID=DateWithValue.Date_ID 
where DateWithValue.Date_ID is not null
order by dt.Date_ID

在此查询中，？应该替换为第一个查询的结果。

In this query ? should be replaced wthe ith result of the first query.

如何合并这两个查询？您对我有任何想法吗？

How can I combine these two queries? do you have any Idea for me

更新：我将查询更改如下（为了更好地分析，我举一个例子）：

UPDATE: I have changed my query as following (to analyse better, I hold an example):

    SELECT 
    dt.Date_ID
    ,Pk.keyword
--  ,pr.Company_BK
--  ,pr.Project_URL
    ,t2.pos_position
    ,pk.Project_Id
    ,PK.SE_Id
FROM 
    DimDate as dt 
    JOIN 
    (
    SELECT
    t1.Project_Id,
    t1.keyword,
    t1.SE_Id,
    Min_Load_Date =  MIN(t1.Load_Date),
    Max_Load_Date = MAX(t1.Load_Date)
FROM
    [RL].[SearchMetrics_ProjectKeyword] t1
    where keyword='aktueller gaspreis pro kwh' and Project_Id=383601 and SE_ID=1  
GROUP BY
    t1.Project_Id,
    t1.keyword,
    t1.SE_Id
    )
     as PK 
        ON dt.Date_ID >=  PK.Min_Load_Date
        AND dt.Date_ID <  PK.Max_Load_Date
        --AND PK.Project_Id=? 
        --AND Pk.keyword=? 
        --AND SE_Id=?
        LEFT JOIN [RL].[SearchMetrics_ProjectKeyword] t2
          on Pk.Project_Id=t2.Project_Id and PK.keyword=t2.keyword and PK.SE_Id=t2.SE_Id and t2.Load_Date >=  dt.Date_ID and t2.Load_Date <=  dt.Date_ID
--    LEFT JOIN MDM.SearchMetrics_Project as pr 
 --       ON PK.Project_ID=pr.Project_ID

通过此查询，我得到这样的结果：

with this query I get such a result:

结构正是我想要的，但是新的挑战是列 pos_positin ，有时具有NULL值。但这不应该是这样。
20180406和20180412之间的此列应为19，而20180413和20180423之间的此列应为21
如何通过此查询实现该目标？

The structure is exactly what I was looking for, but the new challenge is the column pos_positin, which has sometimes NULL values. But it should not be like this. This column between 20180406 and 20180412 should be 19 and between 20180413 and 20180423 be 21 How can I achieve that with this query?

UPDATE 2 ：我知道了，这是解决方案：

UPDATE 2: I got it here is the solution:

with cte as
(
    SELECT 
    dt.Date_ID
    ,Pk.keyword
--  ,pr.Company_BK
--  ,pr.Project_URL
    ,t2.pos_position
    ,pk.Project_Id
    ,PK.SE_Id
FROM 
    DimDate as dt 
    JOIN 
    (
    SELECT
    t1.Project_Id,
    t1.keyword,
    t1.SE_Id,
    Min_Load_Date =  MIN(t1.Load_Date),
    Max_Load_Date = MAX(t1.Load_Date)
FROM
    [RL].[SearchMetrics_ProjectKeyword] t1
    where keyword='citire gaze' and Project_Id=1458724 and SE_ID=79
GROUP BY
    t1.Project_Id,
    t1.keyword,
    t1.SE_Id
    )
     as PK 
        ON dt.Date_ID >=  PK.Min_Load_Date
        AND dt.Date_ID <  PK.Max_Load_Date
        --AND PK.Project_Id=? 
        --AND Pk.keyword=? 
        --AND SE_Id=?
        LEFT JOIN [RL].[SearchMetrics_ProjectKeyword] t2
          on Pk.Project_Id=t2.Project_Id and PK.keyword=t2.keyword and PK.SE_Id=t2.SE_Id and t2.Load_Date >=  dt.Date_ID and t2.Load_Date <=  dt.Date_ID
--    LEFT JOIN MDM.SearchMetrics_Project as pr 
 --       ON PK.Project_ID=pr.Project_ID
    )
    select a.Date_ID
    ,a.keyword
    ,a.Project_Id
    ,a.SE_Id
    ,ISNULL(a.pos_position,x.pos_position) pos_position
     from cte a outer apply
(select top 1 pos_position 
    from cte b 
    where b.Date_ID<a.Date_ID and 
        b.Project_Id is not null and 
        b.pos_position is not null and 
        a.pos_position is null order by Date_ID desc)x

推荐答案

这应该可以为您提供所需的内容，并避免大量的性能困扰...

This should get you what you want and skip a ton of performance pain...

更改您对此的第一个查询...

Change you're 1st query to this...

-- #first_query...
SELECT
    t1.Project_Id,
    t1.keyword,
    t1.SE_Id,
    Min_Load_Date = CONVERT(DATE, MIN(t1.Load_Date)),
    Max_Load_Date = CONVERT(DATE(MAX(t1.Load_Date))
FROM
    dbo.Table1 t1
GROUP BY
    t1.Project_Id,
    t1.keyword,
    t1.SE_Id;

然后您是第二个查询，就像这样...

Then you're second query would just look like this...

SELECT 
    * 
FROM 
    dbo.DateTable as dt 
    JOIN #First_Query as PK 
        ON dt.Date_ID >=  PK.Min_Load_Date
        AND dt.Date_ID < DATEADD(DAY, 1, Max_Load_Date)
        --AND PK.Project_Id=? 
        --AND Pk.keyword=? 
        --AND SE_Id=?
    LEFT JOIN Table2 as pr 
        ON PK.Project_ID=pr.Project_ID;
--where 
--  PK.Domain is not null 
--  AND  dt.Date_ID>= (SELECT min(Load_Date) from Table1 where Project_Id=? and keyword=? and SE_Id=? ) 
--  AND dt.Date_ID<= (SELECT max(Load_Date) from Table1 where Project_Id=? and keyword=? and SE_Id=? )
--order by Date_ID

这篇关于在输入参数中将查询结果用于公用表表达式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！