日期:2014-05-16 浏览次数:20565 次
select orderid,custid,val,100.*val/SUM(val) over() as pctall,
100.*val/sum(val) over(partition by custid) as pctcust
from Sales.OrderValues
----------------------------------------------------------------
-- Author :fredrickhu(小F,向高手学习)
-- Date :2014-03-28 10:53:50
-- Version:
-- Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Enterprise Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([PO] varchar(1),[refPO] varchar(1),[num] int)
insert [tb]
select 'a','a',10 union all
select 'a','a',10 union all
select 'a','a',10
--------------开始查询--------------------------
SELECT
a.*,
LTRIM(CAST( a.num*100.0/b.num AS dec(18,2)))+'%' AS 百分比
FROM
tb AS a
INNER JOIN
(select refPO,SUM(num) AS num from [tb] GROUP BY refPO) AS b
ON a.PO=b.refPO
----------------结果----------------------------
/* PO refPO num 百分比
---- ----- ----------- ------------------------------------------
a a 10 33.33%
a a 10 33.33%
a a 10 33.33%
(3 行受影响)
*/
create table je
(PO varchar(10),refPO varchar(10),Qty int)
insert into je
select 'A','A',10 union all
select 'B','A',10 union all
select 'C','A',10
select a.PO,
a.refPO,
a.Qty,
b.sqty '数量汇总',
rtrim(cast(a.Qty*1.0/b.sqty*100 as decimal(5,2)))+'%' '总数的百分比'
from je a
inner join
(select refPO,sum(Qty) 'sqty'
from je
group by refPO) b on a.refPO=b.refPO
/*
PO refPO Qty 数量汇总