ITEM_NO COL1
A 100
A 100
B 100
B 100
B 100
-----------------------------------
要求结果如下ITEM_NO COL1 COL2
A 100 50%
A 100 50%
B 100 33%
B 100 33%
B 100 33%------------
select ITEM_NO ,COL1
,ltrim(cast(COL1*100.0/sum(COL1) over(partition by ITEM_NO ) as int))+'%' as col2
from tb以上能不能改成UPDATE语句的啊?

解决方案 »

  1.   

    update a
    set col2=ltrim(a.col1*100/b.col)+'%'
    from tb a
    join(select item_no,sum(col1)col from tb group by item_no) b
    on a.item_no=b.item_noselect * from tb
    /**
    ITEM_NO COL1        col2
    ------- ----------- ----------
    A       100         50%
    A       100         50%
    B       100         33%
    B       100         33%
    B       100         33%(5 行受影响)**/
      

  2.   

    if object_id('tempdb.dbo.#tb') is not null drop table #tb
    go
    create table #tb([ITEM_NO] varchar(1),[COL1] int,[col2] sql_variant)
    insert #tb
    select 'A',100,null union all
    select 'A',100,null union all
    select 'B',100,null union all
    select 'B',100,null union all
    select 'B',100,null--------------------------------查询开始------------------------------
    update #tb set col2=ltrim((cast(100.0/(select count(*) from #tb where ITEM_NO=a.ITEM_NO) as decimal(9,2))))+'%'
    from #tb aselect * from #tb
    /*
    ITEM_NO COL1        col2
    ------- ----------- -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    A       100         50.00%
    A       100         50.00%
    B       100         33.33%
    B       100         33.33%
    B       100         33.33%(5 行受影响)*/
      

  3.   

    --> 生成测试数据表: [tb]
    IF OBJECT_ID('[tb]') IS NOT NULL
    DROP TABLE [tb]
    GO
    CREATE TABLE [tb] ([ITEM_NO] [nvarchar](10),[COL1] [int],[COL2] [varchar](10))
    INSERT INTO [tb]
    SELECT 'A','100',NULL UNION ALL
    SELECT 'A','100',NULL UNION ALL
    SELECT 'B','100',NULL UNION ALL
    SELECT 'B','100',NULL UNION ALL
    SELECT 'B','100',NULL--SELECT * FROM [tb]-->SQL查询如下:-->SQL查询如下:
    ;WITH  t AS 
    (
    SELECT *, COL3 = LTRIM(COL1*100/SUM(COL1)OVER(PARTITION BY ITEM_NO))+'%' 
    FROM [tb]
    )
    UPDATE t SET col2=col3SELECT * FROM tb
    /*
    ITEM_NO    COL1        COL2
    ---------- ----------- -------------
    A          100         50%
    A          100         50%
    B          100         33%
    B          100         33%
    B          100         33%(5 行受影响)
    */