求1条SQL急！高手帮忙

[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

[PBVC]

注，是UPDATE语句。

[PBVC]

哇，太快拉！

[GOODlivelife]

if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([ITEM_NO] varchar(1),[COL1] int)
insert [tb]
select 'A',100 union all
select 'A',100 union all
select 'B',100 union all
select 'B',100 union all
select 'B',100
go

select *,ltrim(a.col1*100/b.col)+'%' as col2
from tb a
join(select item_no,sum(col1)col from tb group by item_no) b
on a.item_no=b.item_no
/**
ITEM_NO COL1        item_no col         col2
------- ----------- ------- ----------- -------------
A       100         A       200         50%
A       100         A       200         50%
B       100         B       300         33%
B       100         B       300         33%
B       100         B       300         33%

(5 行受影响)
**/

[黄_瓜]

--> 测试数据：#tb
if object_id('tempdb.dbo.#tb') is not null drop table #tb
go
create table #tb([ITEM_NO] varchar(1),[COL1] int)
insert #tb
select 'A',100 union all
select 'A',100 union all
select 'B',100 union all
select 'B',100 union all
select 'B',100
--2005
--------------------------------查询开始------------------------------

select *,COL2=ltrim((cast(100.0/count(1) over(partition by ITEM_NO) as decimal(9,2))))+'%'
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%
*/

[黄_瓜]

[Quote=引用 1 楼 ldslove 的回复:]

SQL code
select ITEM_NO ,COL1
,ltrim(cast(COL1*100.0/sum(COL1) over(partition by ITEM_NO ) as int))+'%' as col2
from tb
[/Quote]
流氓

[GOODlivelife]

[Quote=引用 2 楼 pbvc 的回复:]
注，是UPDATE语句。
[/Quote]

if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([ITEM_NO] varchar(1),[COL1] int,col2 varchar(10))
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 
go

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

**/

[htl258_Tony]

--> 生成测试数据表: [tb]
IF OBJECT_ID('[tb]') IS NOT NULL
	DROP TABLE [tb]
GO
CREATE TABLE [tb] ([ITEM_NO] [nvarchar](10),[COL1] [int])
INSERT INTO [tb]
SELECT 'A','100' UNION ALL
SELECT 'A','100' UNION ALL
SELECT 'B','100' UNION ALL
SELECT 'B','100' UNION ALL
SELECT 'B','100'


-->SQL查询如下:
SELECT *, COL2 = LTRIM(COL1*100/SUM(COL1)OVER(PARTITION BY ITEM_NO))+'%' 
FROM [tb]
/*
ITEM_NO    COL1        COL2
---------- ----------- -------------
A          100         50%
A          100         50%
B          100         33%
B          100         33%
B          100         33%

(5 行受影响)


*/

[黄_瓜]

--2000
select *,
COL2=ltrim((cast(100.0/(select count(*) from #tb where ITEM_NO=a.ITEM_NO) as decimal(9,2))))+'%'
from #tb a

[东那个升]

 create table test(ITEM_NO varchar(10), COL1 int)
 
 insert test select 'A',100
insert test select 'A', 100
insert test select 'B', 100
insert test select 'B', 100
insert test select 'B', 100


alter table test add col2 varchar(10)



with cte as(select *
,ltrim(cast(COL1*100.0/sum(COL1) over(partition by ITEM_NO ) as int))+'%' as col3
from test)

update cte 
set col2=col3

select * from test


ITEM_NO           COL1 col2
---------- ----------- ----------
A                  100 50%
A                  100 50%
B                  100 33%
B                  100 33%
B                  100 33%

(5 行受影响)

[-晴天]

create table tb(ITEM_NO varchar(10),COL1 int)
insert into tb select 'A',100 union all
select 'A',100 union all
select 'B',100 union all
select 'B',100 union all
select 'B',100
go
select a.*,convert(varchar,convert(int,100.0*a.col1/b.col2))+'%' as col2 from tb a inner join (
select item_no,sum(col1)as col2 from tb group by item_no)
b on a.item_no=b.item_no
go
drop table tb
/*
ITEM_NO    COL1        col2
---------- ----------- -------------------------------
A          100         50%
A          100         50%
B          100         33%
B          100         33%
B          100         33%

(5 行受影响)

*/

[黄_瓜]

[Quote=引用 2 楼 pbvc 的回复:]

注，是UPDATE语句。
[/Quote]
--> 测试数据：#tb
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=引用 13 楼 qianjin036a 的回复:]

都这么快啊!
[/Quote]

[无锡阔微信息科技]

if object_id('tb') is not null
drop table tb
go
create table tb(id nvarchar(20),name1 int,name2 decimal(18,2))
insert into tb(id,name1)
select 'A', 100 union all
select 'A', 100 union all
select 'B', 100 union all
select 'B' ,100 union all
select 'B' ,100


declare @str float
set @str=1
select @str/count(*),id from tb group by id

最简单的思路

[Sybase数据库恢复]

select @str/count(*),id from tb group by id

这个能执行？　

[Sybase数据库恢复]

楼主没有说明清楚 百分比是怎么算出来的。
有两种答案， 各个ITEM_NO 占相应ITEM_NO 总和的百分比？？