通過游標獲取方法對列中常見資料的行和最小最大開始/結束日期進行分組

我有一個帶有幾個輸入引數的存盤程序

輸入引數：

@UnitID = 13001,
@WOID = 731000037,732000049

結果集：

AuxTable不是最終的預期表格，它只是為了除錯/故障排除而顯示。

FinalTable：

這只是了解情況的示例。

問題：如何在不使用游標獲取方法的情況下獲得“FinalTable”？

我嘗試過使用max(Amount),并嘗試分組，min(Start date)但沒有成功。max(End date)MsgLinkId,MyRowId

這是我的存盤程序：

CREATE PROCEDURE dbo.********
    @UnitID nvarchar(50), 
    @WOID nvarchar(50), 
    @JobId nvarchar(50)= '', 
    @StartDate datetime = '', 
    @EndDate datetime = '' 
AS 
BEGIN 
    IF @EndDate = '' 
    BEGIN 
        SET @EndDate = @StartDate 
    END 

    SET @unitID = REPLACE(@unitId, 'All', '') 
    SET @WOID = REPLACE(@WOID, 'All', '') 
    SET @JobID = REPLACE(@JobId, 'All', '') 
    -- SET @startdate = REPLACE(@startdate, 'All', '')
    -- SET @enddate = REPLACE(@enddate, 'All', '')

    SELECT 
        T1.Msg_LinkId AS MsgLinkId, 
        ROW_NUMBER () OVER (PARTITION BY T1.Msg_LinkId 
                            ORDER BY T2.DT_Msg Asc) MyRowid, 
        T1.Unit_WO AS WO_ID, 
        T3.Name AS SourceUnitName, 
        T4.Name AS MaterialName, 
        T1.Data_DInt04 AS BatchId, 
        T5.description AS UnitofMeasure, 
        T6.Name AS Segment1, 
        T7.Name AS Segment2, 
        T8.Name AS Segment3, 
        T9.Name AS Segment4, 
        T10.Name AS Segment5, 
        SUM(T2.Data_Real01) OVER (PARTITION BY T2.Msg_LinkId) Amount, 
        T1.DT_Msg AS StartDate, 
        T2.DT_Msg AS EndDate 
    INTO
        #AuxTable
    FROM   
        QHandler_Messages T1 
    LEFT JOIN 
        QHandler_Messages T2 ON T1.Unit_WO = T2.Unit_WO 
                             AND T1.Msg_LinkId = T2.Msg_LinkId 
    LEFT JOIN 
        LK_CUSTOMDB.dbo.ListUnit T3 ON T1.Data_DInt02 = T3.ID 
    LEFT JOIN 
        LK_CUSTOMDB.dbo.vi_Materials T4 ON T1.Data_DInt03 = T4.Material_ID 
    LEFT JOIN 
        LK_CUSTOMDB.dbo.vw_uoms_all T5 ON T1.Data_DInt05 = T5.uom_id 
    LEFT JOIN 
        LK_CUSTOMDB.dbo.ListUnit T6 ON T1.Data_DInt06 = T6.ID 
    LEFT JOIN 
        LK_CUSTOMDB.dbo.ListUnit T7 ON T1.Data_DInt07 = T7.ID 
    LEFT JOIN 
        LK_CUSTOMDB.dbo.ListUnit T8 ON T1.Data_DInt08 =   T8.ID 
    LEFT JOIN  
        LK_CUSTOMDB.dbo.ListUnit T9 ON T1.Data_DInt09 = T9.ID 
    LEFT JOIN 
        LK_CUSTOMDB.dbo.ListUnit T10 ON T1.Data_DInt10 = T10.ID 
    WHERE 
        (T1.unit_wo IN (SELECT CAST(splitdata AS nvarchar(max)) 
                        FROM dbo.ft_Admin_SplitString(@WOID, ',')) 
         OR @WOID = '') 
        AND (T1.unit_id IN (SELECT CAST(splitdata AS nvarchar(max)) 
                            FROM dbo.ft_Admin_SplitString(@UnitID, ',')) 
             OR @unitid = '') 
        AND T1.Msg_Identifier = 11 
        AND T2.Msg_Identifier = 12 
        AND (T1.Data_DInt01 IN (SELECT CAST(splitdata AS nvarchar(max)) 
                                FROM dbo.ft_Admin_SplitString(@JobID, ',')) 
             OR @jobid = '') 

    UNION ALL 

    SELECT 
        T1.Msg_LinkId as MsgLinkId, 
        ROW_NUMBER () OVER (PARTITION BY T1.Msg_LinkId 
                            ORDER BY T1.DT_Msg Asc) MyRowid, 
        T1.Unit_WO AS WO_ID, 
        T3.Name AS SourceUnitName, 
        T4.Name AS MaterialName, 
        T1.Data_DInt04 AS BatchId, 
        T5.description AS UnitofMeasure, 
        T6.Name AS Segment1, 
        T7.Name AS Segment2, 
        T8.Name AS Segment3, 
        T9.Name AS Segment4, 
        T10.Name AS Segment5, 
        SUM(T1.Data_Real01) OVER (PARTITION BY T1.Msg_LinkId)   Amount, 
        T1.DT_Msg AS StartDate, 
        T1.DT_Msg AS EndDate 
    FROM 
        QHandler_Messages T1 
    LEFT JOIN 
        LK_CUSTOMDB.dbo.ListUnit T3 ON T1.Data_DInt02 = T3.ID 
    LEFT JOIN 
        LK_CUSTOMDB.dbo.vi_Materials T4 ON T1.Data_DInt03 = T4.Material_ID 
    LEFT JOIN 
        LK_CUSTOMDB.dbo.vw_uoms_all T5 ON T1.Data_DInt05 = T5.uom_id 
    LEFT JOIN 
        LK_CUSTOMDB.dbo.ListUnit T6 ON T1.Data_DInt06 = T6.ID 
    LEFT JOIN 
        LK_CUSTOMDB.dbo.ListUnit T7 ON T1.Data_DInt07 = T7.ID 
    LEFT JOIN 
        LK_CUSTOMDB.dbo.ListUnit T8 ON T1.Data_DInt08 = T8.ID 
    LEFT JOIN 
        LK_CUSTOMDB.dbo.ListUnit T9 ON T1.Data_DInt09 = T9.ID 
    LEFT JOIN 
        LK_CUSTOMDB.dbo.ListUnit T10 ON T1.Data_DInt10 = T10.ID 
    WHERE 
        (T1.unit_wo IN (SELECT CAST(splitdata AS nvarchar(max)) 
                        FROM dbo.ft_Admin_SplitString(@WOID, ',')) 
         OR @WOID = '') 
        AND (T1.unit_id IN (SELECT CAST(splitdata AS nvarchar(max)) 
                            FROM dbo.ft_Admin_SplitString(@UnitID, ',')) 
             OR @unitid = '') 
        AND T1.Msg_Identifier = 13 
        AND (T1.Data_DInt01 IN (SELECT CAST(splitdata AS nvarchar(max)) 
                                FROM dbo.ft_Admin_SplitString(@JobID, ',')) 
             OR @jobid = '') 
        AND T1.DT_msg BETWEEN @StartDate 
        AND DATEADD(HOUR, 48, @EndDate) 

    SELECT * 
    FROM #AuxTable --- Result of Aux Table 

    DECLARE @Finaltable TABLE  
                        (
                            MsgLinkId int, 
                            Unit_WO bigint, 
                            SourceUnitName nvarchar (255), 
                            MaterialName nvarchar (255), 
                            BatchId bigint, 
                            UnitofMeasure nvarchar (255), 
                            Segment1 nvarchar (255), 
                            Segment2 nvarchar (255), 
                            Segment3 nvarchar (255), 
                            Segment4 nvarchar (255), 
                            Segment5 nvarchar (255), 
                            Amount real, 
                            StartDate Datetime, 
                            EndDate Datetime
                        ) 
    DECLARE @MsgLinkId int 
    DECLARE @MyRowId int DECLARE cursorAux cursor for 
select 
  MsgLinkId, 
  MyRowid 
from 
  #Auxtable
  open cursorAux FETCH NEXT 
FROM 
  cursorAux INTO @MsgLinkId, 
  @MyRowid WHILE @@FETCH_STATUS = 0 BEGIN If Exists (
    select 
      * 
    from 
      @Finaltable 
    where 
      MsgLinkId = @MsgLinkId
  ) Begin 
Update 
  @Finaltable 
Set 
  EndDate = (
    Select 
      EndDate 
    from 
      #Auxtable where MsgLinkId = @MsgLinkId and MyRowid = @MyRowid)
    Where 
      MsgLinkId = @MsgLinkId end ELSE Begin Insert into @Finaltable (
        MsgLinkId, Unit_WO, SourceUnitName, 
        MaterialName, BatchId, UnitofMeasure, 
        Segment1, Segment2, Segment3, Segment4, 
        Segment5, Amount, StartDate, EndDate
      ) 
    select 
      MsgLinkId, 
      WO_ID, 
      SourceUnitName, 
      MaterialName, 
      BatchId, 
      UnitofMeasure, 
      Segment1, 
      Segment2, 
      Segment3, 
      Segment4, 
      Segment5, 
      Amount, 
      StartDate, 
      EndDate 
    from 
      #Auxtable
    where 
      MsgLinkId = @MsgLinkId 
      and MyRowid = @MyRowid end FETCH NEXT 
    FROM 
      cursorAux INTO @MsgLinkId, 
      @MyRowid END CLOSE cursorAux;
DEALLOCATE cursorAux;
select 
  Unit_WO, 
  SourceUnitName, 
  MaterialName, 
  BatchId, 
  UnitofMeasure, 
  Segment1, 
  Segment2, 
  Segment3, 
  Segment4, 
  Segment5, 
  Amount, 
  StartDate, 
  EndDate 
from 
  @Finaltable END GO

uj5u.com熱心網友回復：

根據游標似乎在做什么，下面的 cte 查詢應該會得到你的結果。游標似乎正在插入第一個 MsgLinkId 記錄 (MyRowID = 1)，然后更新任何后續記錄的 EndDate。此查詢采用第一個 MsgLinkId 記錄并連接到最后一個 MsgLinkId 記錄以獲取 EndDate，但保留第一個記錄中的所有其他資料。

;with cte as (
    SELECT MsgLinkId,
          MyRowId,
          row_number() over(partition by MsgLinkId order by MyRowId desc) as MyRowIdDesc, 
          WO_ID, 
          SourceUnitName, 
          MaterialName, 
          BatchId, 
          UnitofMeasure, 
          Segment1, 
          Segment2, 
          Segment3, 
          Segment4, 
          Segment5, 
          Amount, 
          StartDate, 
          EndDate
    FROM #AuxTable --- Result of Aux Table 
)
select
  row_first.Unit_WO, 
  row_first.SourceUnitName, 
  row_first.MaterialName, 
  row_first.BatchId, 
  row_first.UnitofMeasure, 
  row_first.Segment1, 
  row_first.Segment2, 
  row_first.Segment3, 
  row_first.Segment4, 
  row_first.Segment5, 
  row_first.Amount, 
  row_first.StartDate, 
  row_last.EndDate --On subsequent rows, only the EndDate appears to be updated
from
    cte row_first
    inner join
    cte row_last on row_first.MsgLinkId = row_last.MsgLinkId and row_last.MyRowIdDesc = 1
where
    row_first.MyRowId = 1 --The @Finaltable is first filled with the initial record

標籤：sql sql服务器 tsql

上一篇：TSQL：總體上計算不同的值并根據組的標準

下一篇：根據條件選擇組-CrystalReport