代码之家 › 专栏 › 技术社区 › DarkLight

TSQL迭代表中的子集-根据组更新值

sql-update tsql sql-server sql

DarkLight · 技术社区 · 8 年前

我有一个包含多组记录的大表。我需要更改内部组ID-我想分别为每个组运行此过程

示例数据

Index  Group    InternalID  Data
001    01       01       
002    01       02      
003    01       03      Split
004    01       04
005    02       01       
006    02       02      Split
007    02       03
008    02       04
009    02       05
100    03       01       
101    03       02
102    03       03
103    03       04      Split
104    03       05

对于每组,我需要执行以下操作:

获取该组的记录数(N)
使用“拆分”数据获取记录的位置
将ID的拆分位置更改为原始+N
使用ID的拆分+N+1和拆分+N+2向每个组添加两行
将拆分后的Id更改为原始+N+2

最终结果应该是

Group        InternalID      Data
01           05
01           06
01           07              Split
01           08              NewItem1
01           09              NewItem2
01           10              
02           06
02           07              Split
02           08              NewItem1
02           09              newItem2
02           10 
02           11
02           12
03           06
03           07
03           08
03           09              Split
03           10              NewItem1
03           11              NewItem2
03           12

我想象它看起来像[小心伪代码!!!不是真正的SQL!]

Declare @GCount VARCHAR(10)
    @GSplit VARCHAR(10)

CREATE TABLE ##temp (
    Idx int,
    Grp int,
    InternalID int, 
    Data varchar (10)

FOREACH [Group] from [Example]

        Select @GCount = COUNT(InternalID)
        Select @GSplit = (select InternalID from [Example] where [Data]='Split')
        Insert Into ##temp 
            Select Grp, Data,
            CASE WHEN InternalID <= @GSplit THEN InternalID + @GCount
            ELSE THEN InternalID+@GCount+2
            END AS InternalID
        INSERT INTO ##temp 
            VALUES (00,[Group],@GSplit + @GCount + 1, 'NewItem1'),  (01,[Group],@GSplit + @GCount + 2, 'NewItem2')
 END FOREACH

UPDATE  
    [Example]
SET 
    [Example].* = ##temp.*
FROM 
    ##temp
OUTER JOIN
    [Example]
ON
    ##temp.Idx=[Example].Idx


IF OBJECT_ID('tempdb..##temp') IS NOT NULL

BEGIN

    Drop Table ##temp

END
)

我知道上述内容不存在,但我如何才能获得相同的功能?

2 回复 | 直到 8 年前

MatBailie 8 年前

使用windows函数找出拆分的位置以及组的大小。

根据拆分的行添加新行。这确保您知道要应用的id,并且只添加实际是拆分的行。

使用算术和 CASE 计算出正确的id值。

WITH
  summarised AS
(
  SELECT
    *,
    MAX([InternalID])
      OVER (PARTITION BY [group])                             AS GroupMaxID,
    MAX(CASE WHEN [data] = 'split' THEN [InternalID] END)
      OVER (PARTITION BY [group])                             AS GroupSplitID
  FROM
    yourData
)
SELECT
  [group],
  GroupMaxID
    + InternalID
    + CASE WHEN InternalID > GroupSplitID THEN 2 ELSE 0 END   AS InternalID,
  [data]
FROM
  summarised

UNION ALL

SELECT
  summarised.[group],
  summarised.GroupMaxID
    + summarised.InternalID
    + NewRows.Offset,
  NewRows.datum
FROM
  summarised
CROSS APPLY
(
  SELECT 1 AS offset, 'NewItem1' AS datum
  UNION ALL
  SELECT 2 AS offset, 'NewItem2' AS datum
)
  AS NewRows
WHERE
  summarised.[data] = 'split'

ORDER BY
  1, 2

http://sqlfiddle.com/#!18/91d7c/3

编辑:

相同逻辑的实现略短且更一致:

http://sqlfiddle.com/#!18/d56f1/1

速度也略微加快。

WITH
  summarised AS
(
  SELECT
    *,
    MAX([InternalID])
      OVER (PARTITION BY [group])                             AS GroupMaxID,
    MAX(CASE WHEN [data] = 'split' THEN [InternalID] END)
      OVER (PARTITION BY [group])                             AS GroupSplitID
  FROM
    yourData
)
SELECT
  summarised.[group]           AS [group],
  summarised.GroupMaxID
    + summarised.InternalID
    + NewRows.Offset           AS InternalID,
  NewRows.[data]               AS [data]
FROM
  summarised
CROSS APPLY
(
  SELECT 0 AS offset, summarised.[data] WHERE summarised.InternalID <= summarised.GroupSplitID
  UNION ALL
  SELECT 1 AS offset, 'NewItem1'        WHERE summarised.InternalID  = summarised.GroupSplitID
  UNION ALL
  SELECT 2 AS offset, 'NewItem2'        WHERE summarised.InternalID  = summarised.GroupSplitID
  UNION ALL
  SELECT 2 AS offset, summarised.[data] WHERE summarised.InternalID >  summarised.GroupSplitID
)
  AS NewRows
ORDER BY
  summarised.[group],
  summarised.InternalID,
  NewRows.Offset

paparazzo 8 年前

我想这样就行了

declare @t table (idx int, grp int, id int, data varchar(10));
insert into @t values  
       (001,    01,       01, null)      
     , (002,    01,       02, null)          
     , (003,    01,       03, 'Split')
     , (004,    01,       04, null)    
     , (005,    02,       01, null)           
     , (006,    02,       02, 'Split')
     , (007,    02,       03, null)    
     , (008,    02,       04, null)    
     , (009,    02,       05, null)    
     , (100,    03,       01, null)           
     , (101,    03,       02, null)    
     , (102,    03,       03, null)    
     , (103,    03,       04, 'Split')
     , (104,    03,       05, null);
with cte as 
(  select t.* 
        , count(*)     over (partition by t.grp) as cnt 
        , row_number() over (partition by t.grp order by t.id, t.idx) as rn 
        , (count(*) over (partition by t.grp) + t.id) as nwId  
        , ts.id as 'split' 
        , CASE WHEN t.id > ts.id THEN (count(*) over (partition by t.grp) + t.id + 2) 
                                 ELSE (count(*) over (partition by t.grp) + t.id) 
               end as nwnwId
     from @t t
     left join @t ts 
       on ts.grp = t.grp 
      and ts.data = 'split' 
)
select t.* 
 from cte t  
union all 
select t.idx, t.grp, t.id, null, t.cnt, t.rn, t.nwId, t.split, t.nwnwId + 1
 from cte t 
where t.data = 'split'
union all 
select t.idx, t.grp, t.id, null, t.cnt, t.rn, t.nwId, t.split, t.nwnwId + 2
 from cte t 
where t.data = 'split'
 order by grp, nwnwId, idx;