htl258(tony)来帮忙！

[PBVC]

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语句的啊？

[GOODlivelife]

不是写了有吗

[GOODlivelife]

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_no

select * from tb
/**
ITEM_NO COL1        col2
------- ----------- ----------
A       100         50%
A       100         50%
B       100         33%
B       100         33%
B       100         33%

(5 行受影响)

**/

[黄_瓜]

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 a

select * 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 行受影响)

*/

[黄_瓜]

[Quote=引用楼主 pbvc 的回复:]
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……
[/Quote]

上一贴好多人都回了，你都不开。。。。。。。。。

[黄_瓜]

对了，你的百分比究竟是怎么算的？
你的数据有点特殊

[htl258_Tony]

--> 生成测试数据表: [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=col3

SELECT * FROM tb
/*
ITEM_NO    COL1        COL2
---------- ----------- -------------
A          100         50%
A          100         50%
B          100         33%
B          100         33%
B          100         33%

(5 行受影响)


*/