求1条SQL急！高手帮忙

Question

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%

Answer 1

select ITEM_NO ,COL1
,ltrim(cast(COL1*100.0/sum(COL1) over(partition by ITEM_NO ) as int))+'%' as col2
from tb

Answer 2

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
goselect *,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 行受影响)
**/

Answer 3

--> 测试数据：#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%
*/

Answer 4

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

Answer 5

--> 生成测试数据表: [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 行受影响)
*/

Answer 6

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

Answer 7

 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=col3select * from test
ITEM_NO           COL1 col2
---------- ----------- ----------
A                  100 50%
A                  100 50%
B                  100 33%
B                  100 33%
B                  100 33%(5 行受影响)

Answer 8

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

Answer 9

--> 测试数据：#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 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 行受影响)*/

Answer 10

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最简单的思路

Answer 11

select @str/count(*),id from tb group by id 这个能执行？　

Answer 12

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