Home Articles

更新具有重复行的表中的列

Asked
Viewed 912 times
1

有问题更新具有重复行的表中的列..我有一个表“tab1”,其数据看起来像这样..

Id   col2   col3     col4  col5    col6       col7  
1    xim       M     gtt   tif      1          2  
2    white     M     abc   png      0          25  
2    white     M     abc   jpeg     0          25  
3    gtc       V     ftt   gif      4          72

此表包含重复的ID,但仅在col5中有所不同 . 这个表包含大约4000行,我想更新col5,使得输出看起来像这样 .

Id   col2   col3     col4  col5     col6       col7  
1    xim       M     gtt   tif       1          2  
2    white     M     abc   png,jpeg  0          25  
3    gtc       V     ftt   gif       4          72

有没有办法使用update语句更新此表,我是否必须为此更新创建临时表.. ??

1 Answer

  • 1

    我同意njk和Tony发布的评论 . 对数据库进行非规范化并不是一个好主意,但也许你的最终目标并不那么明显,并且结合图像扩展名适合您的特定需求 .

    这就是你所要求的 . 我确信有一种方法可以使用XML而不使用函数中的游标...

    use tempdb
    go
    
    create table tmp (
      id int, 
      col2 varchar(10), 
      col3 varchar(10), 
      col4 varchar(10), 
      col5 varchar(255), 
      col6 int, 
      col7 int
    )
    go
    
    insert into tmp values
    (1, 'xim', 'M', 'gtt', 'tif', 1, 2),
    (2, 'white', 'M', 'abc', 'png', 0, 25),
    (2, 'white', 'M', 'abc', 'jpeg', 0, 25),
    (2, 'white', 'M', 'abc', 'gif', 0, 25),
    (3, 'gtc', 'V', 'ftt', 'jpeg', 4, 72),
    (3, 'gtc', 'V', 'ftt', 'tif', 4, 72),
    (3, 'gtc', 'V', 'ftt', 'png', 4, 72),
    (3, 'gtc', 'V', 'ftt', 'gif', 4, 72)
    go
    
    create function fnConcatCol5 (@id int) returns varchar(255) as
    begin
      declare @rtn varchar(255) = '', @val varchar(10)
    
      declare cr cursor local for
        select col5 from tmp where id = @id
    
      open cr
      fetch next from cr into @val
    
      while @@fetch_status = 0
      begin
        set @rtn = @rtn + @val + ','
        fetch next from cr into @val
      end
    
      close cr
      deallocate cr
    
      set @rtn = left(@rtn, datalength(@rtn) - 1)
    
      return @rtn
    end
    go
    
    -- it is more efficient to split up the 'distinct' and function call
    -- into separate SQL statements so the function is only run *one* time
    -- for each unique id
    
    select distinct id, col2, col3, col4, col6, col7 
    into #temp
    from tmp
    
    select id, col2, col3, col4, dbo.fnConcatCol5(id) as col5, col6, col7
    from #temp
    go
    
    drop table tmp, #temp
    go
    drop function fnConcatCol5
    go
    

    返回的数据如下所示:

    id    col2    col3    col4    col5                col6    col7
    ----- ------- ------- ------- ------------------- ------- ----
    1     xim     M       gtt     tif                 1       2
    2     white   M       abc     png,jpeg,gif        0       25
    3     gtc     V       ftt     jpeg,tif,png,gif    4       72
    

Related