CTE循环查询 [英] CTE looping query

问题描述

首先，我将向您说明情况.

First I'll explain you the situation.

我想将数据从一个表(View_Solidnet_Training)传输到另一个表(OBJ_Availability).有一个问题:在视图中有一个Start-和EndDate！在 OBJ_Availability 中，每个日期都有一个记录.因此，视图中的一行在另一张表中具有多行.

I want to transfer data from one table(View_Solidnet_Training) to another(OBJ_Availability). There is one problem: In the view there is a Start- and EndDate! In OBJ_Availability every date has one record. So one line in the view has multi lines in the other table.

我必须与CTE合作.所以游标对我来说是没有选择的.中间的 WITH 运行完美，但是当我想添加一个额外的 WITH 来检查 ID 是否不为零时，必须更改变量 @Start 和 @End 到视图中的新记录.

I must work with CTE. So cursor is no option for me. The middle WITH runs perfect, but when I want to add an extra WITH to check if the ID isn't zero, it must change the variable @Start and @End to the new record in the view.

对不起，我的英语不好，但是希望您能理解这种情况.

Sorry for my English, it isn't that good but I hope you understand the situation.

这是我的代码:

DECLARE @Start AS DATETIME;
DECLARE @End AS DATETIME;
SET @Start = '2013-04-09';
SET @End = '2013-04-11';
with cte1 as 
(

with cte2 as
(
select @Start as DateValue
union all

select DateValue + 1
from cte2
where DateValue + 1 <= @End
)
into OBJ_Availability  
select 34, DateValue, 'AM', 2, 'Test' from cte2
)
select * from cte1 where PK_Training_ID is not null;

类似这样的东西，但是我不明白它从哪里获取视图的信息.我从没在任何地方提到过这个名字?

Something like this, but I don't understand where it gets the information of the view. I never mentioned the name anywhere?

推荐答案

我还无法发表评论，因此在回答之前我需要问几个问题.

I can not make comment yet, so I need to ask you few questions before answering.

1，为什么CTE的名称与视图的名称相同?它永远都不会工作.

1, how come the CTE's name is the same as the view's name? it should never work.

2，检查ID是否不为零，它必须将变量@Start和@End更改为视图中的新记录"是什么意思，我不知道为什么需要这样做空检查

2, what do you mean by 'check if the ID isn't zero, it must change the variable @Start and @End to the new record in the view', I could not figure out why you need to do the null check

3，您确定可以使用DateValue + 1来获取下一个日期吗?您应该使用DATEADD吗?

3, you sure you can use DateValue + 1 to get next date? should you be using DATEADD?

最后，您不能在CTE中拥有CTE，这是行不通的.也不可能在CTE中声明变量.

lastly, you cannot have a CTE inside a CTE, this is not going to work. Declare variable in CTE is not possible neither.

这是我最好的客人:

首先，正如您提到的，您的视图有一个startdate列和一个enddate列，

firstly, as you mentioned that your view has a startdate column and a enddate column,

所以我假设视图中有StartDate和EndDate列，这是我的sql:

so I assume there are StartDate and EndDate columns in the view, here is my sql:

DECLARE @start AS DATETIME
DECLARE @end AS DATETIME

SELECT @start = min(StartDate)
from View_Solidnet_Training 
where PK_Training_ID is not null

SELECT @end = max(EndDate) 
from View_Solidnet_Training 
where PK_Training_ID is not null

;with cte_dates as
(
    select @start DateValue
    union all
    select DateValue + 1
    from cte_dates
    where DateValue + 1 <= cast(@end as datetime)
)
into OBJ_Availability  
select v.PK_Training_ID, DateValue, 'AM', 2, 'Test' --columns from the view
from cte_dates cte
join View_Solidnet_Training v on v.StartDate < cte.DateValue and cte.DateValue < v.EndDate 
where v.PK_Training_ID is not null

max()和min函数找出视图中的最新日期和最旧日期

the max() and min function figure out that newest and oldest date in the view

然后CTE cte_dates创建一个从@start到@end的日期列表

then the CTE cte_dates creates a list of dates from the @start to @end

然后加入CTE将在StartDate到EndDate范围内重复记录

then the join to the CTE will make records repeated within the range from StartDate to EndDate

希望获得帮助

顺便说一句，我的家用电脑上没有sql，因此我可以检查是否

by the way, I do not have sql on my home pc, so I can check if

SELECT @start = min(StartDate)
from View_Solidnet_Training 
where PK_Training_ID is not null

运行与否，但您应该了解一下

runs or not, but you should get the idea

代替CTE使用:

DECLARE @start AS DATETIME
DECLARE @end AS DATETIME

SELECT @start = min(StartDate)
from View_Solidnet_Training 
where PK_Training_ID is not null

SELECT @end = max(EndDate) 
from View_Solidnet_Training 
where PK_Training_ID is not null

DECLARE @AllDates table
        (DateValue datetime)

DECLARE @dCounter datetime
SELECT @dCounter = @start

WHILE @dCounter <= @end
BEGIN
 INSERT INTO @AllDates VALUES (@dCounter)
 SELECT @dCounter=@dCounter+1 
END

insert into OBJ_Availability  
select v.PK_Training_ID, DateValue, 'AM', 2, 'Test' --columns from the view
from @AllDates d
join View_Solidnet_Training v on v.StartDate < d.DateValue and d.DateValue < v.EndDate 
where v.PK_Training_ID is not null

或者您甚至可以

DECLARE @start AS DATETIME
DECLARE @end AS DATETIME

SELECT @start = min(StartDate)
from View_Solidnet_Training 
where PK_Training_ID is not null

SELECT @end = max(EndDate) 
from View_Solidnet_Training 
where PK_Training_ID is not null


DECLARE @dCounter datetime
SELECT @dCounter = @start

WHILE @dCounter <= @end
BEGIN

    insert into OBJ_Availability  
    select v.PK_Training_ID, DateValue, 'AM', 2, 'Test' --columns from the view
    from View_Solidnet_Training v
    where v on v.StartDate < @dCounter and @dCounter < v.EndDate and v.PK_Training_ID is not null

    SELECT @dCounter=@dCounter+1 
END

这篇关于CTE循环查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！