none
增加列问题,有大大会么? RRS feed

  • 问题

  • 表Names
    MyName
    nvarchar(50)
    'aaa'
    'aaa'
    'aaa'
    'aaa'
    'bbb'
    'bb'
    'ccc'
    'ccc'
    'bb'
    要求是:增加一个计算列,要求结果如下:
    MyName         NameID
    nvarchar(50)  
    'aaa'             'new'
    'aaa'             'old'
    'aaa'             'old'
    'aaa'             'old'
    'bbb'             'new' 
    'bb'               'new'
    'ccc'             'new'
    'ccc'             'old'
    'bb'               'old'

    各位大大们,如何写此计算列呢?
    2009年11月5日 10:05

答案

  • 表Names
    MyName
    nvarchar(50)
    'aaa'
    'aaa'
    'aaa'
    'aaa'
    'bbb'
    'bb'
    'ccc'
    'ccc'
    'bb'
    要求是:增加一个计算列,要求结果如下:
    MyName         NameID
    nvarchar(50)  
    'aaa'             'new'
    'aaa'             'old'
    'aaa'             'old'
    'aaa'             'old'
    'bbb'             'new' 
    'bb'               'new'
    'ccc'             'new'
    'ccc'             'old'
    'bb'               'old'

    各位大大们,如何写此计算列呢?
    ------------------------------------
    -- Author: flystone  
    -- Version:V1.001  
    -- Date:2009-11-05 22:50
    ------------------------------------
    
    -- Test Data: ta
    If object_id('ta') is not null 
        Drop table ta
    Go
    Create table ta(MyName nvarchar(10))
    Go
    Insert into ta
     select 'aaa' union all
     select 'aaa' union all
     select 'aaa' union all
     select 'aaa' union all
     select 'bbb' union all
     select 'bb' union all
     select 'ccc' union all
     select 'ccc' union all
     select 'bb' 
    Go
    --Start
    alter table ta add nameid varchar(10)
    go
    declare @s varchar(10),@s1 varchar(8000)
    set @s1 = ''
    update a
    	set nameid = case when charindex( ','+MyName +',',','+@s1+',') > 0 then 'old' 
                     else 'new' 
    				 end ,
    		@s1 = case when charindex( ','+cast(@s+'' as varchar(10)) +',',','+@s1+',') > 0 then @s1
         			else @s1+','+isnull(cast(@s+'' as varchar(10)),'')  
    				end ,
    		@s =  myname 
    from ta a
     
    select * from ta
    
    --Result:
    /*
    
    MyName     nameid     
    ---------- ---------- 
    aaa        new
    aaa        old
    aaa        old
    aaa        old
    bbb        new
    bb         new
    ccc        new
    ccc        old
    bb         old
    */
    --End 
    


    More: blog.csdn.net/happyflystone
    2009年11月5日 14:51
  • --只是查詢SQL2005以上版本可這樣用,SQL2000 用臨時表或表變量代替
    
    declare @T table (MyName nvarchar(10))
    Insert into @t
    select 'aaa' union all
    select 'aaa' union all
    select 'aaa' union all
    select 'aaa' union all
    select 'bbb' union all
    select 'bb' union all
    select 'ccc' union all
    select 'ccc' union all
    select 'bb'
    
    
    ;with Cte
    as
    (
    select ID=row_number()over(order by (select 1)),MyName from @T)
    select
        a.MyName,
        NameID=case when b.ID is null then 'New' else 'Old' end
    from Cte a
        left join Cte b on a.MyName=b.MyName and a.ID=b.ID+1
    order by a.ID
    /*
    MyName     NameID
    ---------- ------
    aaa        New
    aaa        Old
    aaa        Old
    aaa        Old
    bbb        New
    bb         New
    ccc        New
    ccc        Old
    bb         New
    */
    


    ROY WU(吳熹)
    2009年11月8日 4:40
    版主

全部回复

  • 增加计算列貌似不行。。。。
    其实我想算的是select count(distinct MyName) from dbo.Names;
    但是Names有几百亿条数据,这样执行效率太慢,大大们是怎么解决的?
    2009年11月5日 10:30
  • 計算列,需要寫一個函數,把函數設置為計算列

    樓主這樣設計不好,最好是新增一列,通過觸發器實現
    ROY WU(吳熹)
    2009年11月5日 10:55
    版主
  • 如果楼主用的是08,可以新增一列NameID,通过触发器写数据。再在新建的列上建一个带条件的索引(where就和后面sql的where子句一样),然后select count(NameID) from dbo.Names where NameID='new'
    2009年11月5日 14:10
    版主
  • 表Names
    MyName
    nvarchar(50)
    'aaa'
    'aaa'
    'aaa'
    'aaa'
    'bbb'
    'bb'
    'ccc'
    'ccc'
    'bb'
    要求是:增加一个计算列,要求结果如下:
    MyName         NameID
    nvarchar(50)  
    'aaa'             'new'
    'aaa'             'old'
    'aaa'             'old'
    'aaa'             'old'
    'bbb'             'new' 
    'bb'               'new'
    'ccc'             'new'
    'ccc'             'old'
    'bb'               'old'

    各位大大们,如何写此计算列呢?
    ------------------------------------
    -- Author: flystone  
    -- Version:V1.001  
    -- Date:2009-11-05 22:50
    ------------------------------------
    
    -- Test Data: ta
    If object_id('ta') is not null 
        Drop table ta
    Go
    Create table ta(MyName nvarchar(10))
    Go
    Insert into ta
     select 'aaa' union all
     select 'aaa' union all
     select 'aaa' union all
     select 'aaa' union all
     select 'bbb' union all
     select 'bb' union all
     select 'ccc' union all
     select 'ccc' union all
     select 'bb' 
    Go
    --Start
    alter table ta add nameid varchar(10)
    go
    declare @s varchar(10),@s1 varchar(8000)
    set @s1 = ''
    update a
    	set nameid = case when charindex( ','+MyName +',',','+@s1+',') > 0 then 'old' 
                     else 'new' 
    				 end ,
    		@s1 = case when charindex( ','+cast(@s+'' as varchar(10)) +',',','+@s1+',') > 0 then @s1
         			else @s1+','+isnull(cast(@s+'' as varchar(10)),'')  
    				end ,
    		@s =  myname 
    from ta a
     
    select * from ta
    
    --Result:
    /*
    
    MyName     nameid     
    ---------- ---------- 
    aaa        new
    aaa        old
    aaa        old
    aaa        old
    bbb        new
    bb         new
    ccc        new
    ccc        old
    bb         old
    */
    --End 
    


    More: blog.csdn.net/happyflystone
    2009年11月5日 14:51
  • CREATE VIEW [dbo].[View_1] AS with tmp(myname) as ( select 'aaa' union all select 'aaa' union all select 'aaa' union all select 'aaa' union all select 'bbb' union all select 'bb' union all select 'ccc' union all select 'ccc' union all select 'bb' ) , tmp2(c1,c2) as ( select *,row_number() over(partition by myname order by myname) from tmp ) select c1,case c2 when 1 then 'new' else 'old' end as c3 from tmp2
    2009年11月6日 0:51
  • --只是查詢SQL2005以上版本可這樣用,SQL2000 用臨時表或表變量代替
    
    declare @T table (MyName nvarchar(10))
    Insert into @t
    select 'aaa' union all
    select 'aaa' union all
    select 'aaa' union all
    select 'aaa' union all
    select 'bbb' union all
    select 'bb' union all
    select 'ccc' union all
    select 'ccc' union all
    select 'bb'
    
    
    ;with Cte
    as
    (
    select ID=row_number()over(order by (select 1)),MyName from @T)
    select
        a.MyName,
        NameID=case when b.ID is null then 'New' else 'Old' end
    from Cte a
        left join Cte b on a.MyName=b.MyName and a.ID=b.ID+1
    order by a.ID
    /*
    MyName     NameID
    ---------- ------
    aaa        New
    aaa        Old
    aaa        Old
    aaa        Old
    bbb        New
    bb         New
    ccc        New
    ccc        Old
    bb         New
    */
    


    ROY WU(吳熹)
    2009年11月8日 4:40
    版主