时间戳

首页 > 解决方案 > 需要在下面的查询中添加另一个 While

sql - 需要在下面的查询中添加另一个 While

问题描述

我从这个论坛的一位专家那里得到了以下代码（工作正常）。所以我想做的是进一步修改这个查询，以同样的方式获取日期，但对于所有任务。数据库中有一个名为“TASK”的表，每个task_id都有一个clndr_id和proj_id。所需的输出将是 (proj_id, task_id, clndr_id, date_Value)，所以我认为我们需要添加另一个 WHILE 来获取 TASK 表中所有任务的所有日期。我希望@CurseStacker 或其他任何人都可以提供帮助:)..谢谢。

ALTER FUNCTION [dbo].[GetProjectDates] 
(   
     @project_name  varchar(50)
)
RETURNS @temp_tb TABLE([proj_id] int, [clndr_id] int , [date_value] date)
AS
BEGIN 
    -- Add the SELECT statement with parameter references here
    DECLARE @project_id int
    DECLARE @clndr_id   int
    DECLARE @walker     int             =   0
    DECLARE @holder     varchar(MAX)
    DECLARE @date       date
    DECLARE @data       varchar(MAX)

    SELECT @project_id = [p].[proj_id]
          ,@clndr_id = [p].[clndr_id]
          ,@holder = [c].[clndr_data]
    FROM [PMDB].[dbo].[PROJECT] AS [p]
    INNER JOIN [PMDB].[dbo].[CALENDAR] AS [c] ON [p].[clndr_id] = [c].[clndr_id]
    WHERE [p].[proj_short_name] = @project_name

    WHILE @walker <> LEN(@holder) + 1
    BEGIN
        IF SUBSTRING(@holder, @walker, 2) = 'd|'
        BEGIN
            SET @data = SUBSTRING(@holder, @walker, 10)
            IF SUBSTRING(@data, LEN(@data) - 2, 3) = ')()'
            BEGIN
                SET @date = DATEADD(D, CAST(SUBSTRING(@data, 3, 5) AS int) -2, '01/01/1900')
                INSERT INTO @temp_tb VALUES (@project_id, @clndr_id, @date)
            END
        END
        SET @walker = @walker + 1
    END

    RETURN
END

GO

所以“TASK”表包括（proj_id，task_id，clndr_id），从“日历”表中，我们可以通过链接到任务表中的proj_id来获取clndr_data（上面的代码在clndr_data中获取'd|'和'之间的日期）（） ' 只适用于一个 clndr_id，它工作正常' 现在我需要做同样的事情，但对于几个日历；

proj_id task_id clndr_id    clndr_data
4917    310449  7143    (0||CalendarData()(.....
4917    310450  7144    (0||CalendarData()(.....
4917    310451  7149    (0||CalendarData()(.....

期望的结果（假设 clndr_id 7143 在 'd|' 和 ')()' 之间只有 2 个日期，clndr_id 7144 有 3 个日期，而 cldnr_id 7149 有两个日期）只是一个假设

proj_id  task_id clndr_id      date 
4917    310449   7143        2018-09-24
4917    310449   7143       2018-09-25
4917    310450   7144       2018-09-26
4917    310450   7144       2018-10-01
4917    310450   7144       2018-10-02
4917    310451   7149      2018-10-03
4917    310451   7149       2018-10-04

标签： sqlsql-server

解决方案

试试下面的方法：

ALTER FUNCTION [dbo].[GetProjectDates] 
(   
     @project_name  varchar(50)
)
RETURNS @temp_tb TABLE([proj_id] int, [task_id] int, [clndr_id] int , [date_value] date)
AS
BEGIN 
    -- Add the SELECT statement with parameter references here
    DECLARE @project_id int
    DECLARE @task_id   int
    DECLARE @clndr_id   int
    DECLARE @walker     int             =   0
    DECLARE @holder     varchar(MAX) = ''
    DECLARE @date       date
    DECLARE @data       varchar(MAX)

    declare @tab table (project_id int, task_id int, clndr_id int, holder varchar(max))

    insert into @tab
    SELECT [T].[proj_id] as project_id
          ,[T].[task_id] as task_id
          ,[c].[clndr_id] as clndr_id
          ,[c].[clndr_data] as holder
    FROM [PMDB].[dbo].[TASK] as [T]
    INNER JOIN [PMDB].[dbo].[CALENDAR] AS [c] ON [T].[clndr_id] = [c].[clndr_id] and [T].[Proj_id] = [c].[proj_id] 
    WHERE [T].[proj_id] = (select proj_id FROM [PMDB].[dbo].[PROJECT] where [proj_short_name] = @project_name)

    declare @cur_task int, @cur_clndr int
    set @cur_task = (select top 1 task_id from @tab)
    set @cur_clndr = (select top 1 clndr_id from @tab where task_id = @task_id)
    set @holder = (select top 1 holder from @tab where task_id = @task_id)

    while ((select count(1) from @tab) > 0)
    begin
        print 'Current loop running for Task_Id' + convert(varchar(10), @cur_task)
        WHILE @walker <> LEN(@holder) + 1
        BEGIN
            IF SUBSTRING(@holder, @walker, 2) = 'd|'
            BEGIN
                SET @data = SUBSTRING(@holder, @walker, 10)
                IF SUBSTRING(@data, LEN(@data) - 2, 3) = ')()'
                BEGIN
                    SET @date = DATEADD(D, CAST(SUBSTRING(@data, 3, 5) AS int) -2, '01/01/1900')
                    INSERT INTO @temp_tb VALUES (@project_id, @task_id, @clndr_id, @date)
                END
            END
            SET @walker = @walker + 1
        END

        delete @tab where task_id = @cur_task

        set @cur_task = (select top 1 task_id from @tab)
        set @cur_clndr = (select top 1 clndr_id from @tab where task_id = @task_id)
        set @holder = (select top 1 holder from @tab where task_id = @task_id)

        set @walker = 0
    end

    RETURN
END

推荐阅读