如何在T-SQL中获得所需的结果,如[关闭]

我想分享我的解决方案,以帮助任何遇到相同问题的人:

/****** Object:  StoredProcedure [dbo].[MergeSeqDates]    Script Date: 06/12/2010 18:18:26 ******/
/************************************************************************************************
THIS STORED PROCEDURE CAN BE USED FOR AN INPUT TABLE LIKE THE FOLLOWING 
tablename: [_dateSeq]
UserId  InDate                      OutDate id          Record number
1       3/12/2010 12:00:00 AM   3/12/2010 12:00:00 AM   1 
1       3/12/2010 12:00:00 AM   3/13/2010 12:00:00 AM   2 
1       3/19/2010 12:00:00 AM   3/30/2010 12:00:00 AM   3 
2       3/2/2010 12:00:00 AM    3/3/2010 12:00:00 AM    4 
2       3/3/2010 12:00:00 AM    3/4/2010 12:00:00 AM    5 
2       3/4/2010 12:00:00 AM    3/9/2010 12:00:00 AM    6 
3       2/2/2010 12:00:00 AM    2/28/2010 12:00:00 AM   7 


TO GIVE AN OUTPUT TABLE LIKE:
tablename: [mergeddateseq]
UserId  InDate                  OutDate                 Unique_Id 
1       3/12/2010 12:00:00 AM   3/13/2010 12:00:00 AM   1
1       3/19/2010 12:00:00 AM   3/30/2010 12:00:00 AM   2
2       3/2/2010 12:00:00 AM    3/9/2010 12:00:00 AM    3
3       2/2/2010 12:00:00 AM    2/28/2010 12:00:00 AM   4


Table Structures used:
(1)
CREATE TABLE [dbo].[_dateSeq](
    [UserId] [bigint] NULL,
    [InDate] [datetime] NULL,
    [OutDate] [datetime] NULL,
    [id] [bigint] IDENTITY(1,1) NOT NULL,
 CONSTRAINT [PK__dateSeq] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]



(2)
CREATE TABLE [dbo].[MergedDateSeq](
    [Unique_Id] [bigint] IDENTITY(1,1) NOT NULL,
    [UserId] [bigint] NULL,
    [InDate] [datetime] NULL,
    [OutDate] [datetime] NULL,
 CONSTRAINT [PK_MergedDateSeq] PRIMARY KEY CLUSTERED 
(
    [Unique_Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]


************************************************************************************************/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER Procedure [dbo].[MergeSeqDates]
(
@StartDate datetime,
@EndDate datetime
)
AS
BEGIN
/*Clear the output table before new data is put into it*/
    DROP TABLE mergeddateseq;
    CREATE TABLE [dbo].[MergedDateSeq](
    [Unique_Id] [bigint] IDENTITY(1,1) NOT NULL,
    [UserId] [bigint] NULL,
    [InDate] [datetime] NULL,
    [OutDate] [datetime] NULL,
 CONSTRAINT [PK_MergedDateSeq] PRIMARY KEY CLUSTERED 
(
    [Unique_Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

/*For every record in the original table, check if the user's next start date is consequent to the user's previous end date*/
/*If yes, update the earlier record*/
/*If not, add a new record record*/

DECLARE rec_cursor CURSOR FOR
SELECT UserId,InDate,OutDate FROM [_dateSeq] WHERE InDate>=@StartDate and ((OutDate<=@EndDate) or (OutDate is null)) order by InDate;


OPEN rec_cursor

Declare @DateFrom DateTime
Declare @DateTo DateTime
Declare @CardId bigint

Declare @mrgDateFrom DateTime
Declare @mrgDateTo DateTime
Declare @mrgCardId bigint
Declare @Unque_Id bigint

FETCH NEXT FROM rec_cursor
INTO @CardId,@DateFrom, @DateTo

WHILE @@FETCH_STATUS = 0
BEGIN
    set @Unque_Id=0;
    SELECT @Unque_Id=Unique_Id,@mrgCardId=UserId,@mrgDateFrom=InDate,@mrgDateTo=OutDate FROM mergeddateseq where UserId=@CardId order by OutDate desc;
    if @@rowcount>0
        BEGIN
        --check dates
        --update record if date is same
        if (@DateFrom=@mrgDateTo)
            Update mergeddateseq set OutDate=@DateTo where Unique_Id=@Unque_Id;
        --update record if dates are sequential
        else if (@DateFrom=DATEADD(day,+1,@mrgDateTo))
            Update mergeddateseq set OutDate=@DateTo where Unique_Id=@Unque_Id;
        else
        --insert new record
        Insert into mergeddateseq (UserId,InDate,OutDate) values (@CardId,@DateFrom,@DateTo);
        END
    else
        BEGIN
        --insert new record
        Insert into mergeddateseq (UserId,InDate,OutDate) values (@CardId,@DateFrom,@DateTo);
        END

    FETCH NEXT FROM rec_cursor
    INTO @CardId,@DateFrom, @DateTo
END

CLOSE rec_cursor
DEALLOCATE rec_cursor

    Select * from mergeddateseq;
END

--exec [MergeSeqDates] @StartDate='1-1-2010', @EndDate='1-1-2011'

可能是两步的过程。 首先创建一个带有userid、indat和outdate的临时工作表。然后 插入工作表(userid,indate)选择userid,indate从可输入的group by userid,indate

下一步就是

更新工作表 set outlate=t.outlate 从(select userid,outlate from inputtable group by userid,outlate)作为t 其中userid=t.userid

我还没有尝试最后一条语句,但是您应该能够动态地创建一个虚拟表T并更新主表。如果它不起作用,那么为每个用户的每个日期创建第二个工作表,然后在两个工作表之间创建一个联接。

可能是这样的:

SELECT UseriD, InDate, MAX(outDate)
FROM TABLE
GROUP BY UseriD, InDate
ORDER BY UseriD, InDate

这将选择所有 UseriD, InDate 组合并显示最新 outDate 匹配那个组合。例如,有两行的userid=1,indate=3/12/2010,但过期的是3/12/2010和3/13/2010,因此,该userid的最大(过期),indate分组将显示 3/13/2010 .