declare @t table(fie varchar(10), num int) insert @t select 'a' , 10 insert @t select 'b', 90 select fie,num,num*1.0/(select sum(num) from @t) from @t union all select 'total',sum(num),1 from @t /*fie num ---------- ----------- -------------------------- a 10 .100000000000 b 90 .900000000000 total 100 1.000000000000(影響 3 個資料列)*/
DECLARE @a TABLE(fie VARCHAR(20), num INT) INSERT @a SELECT 'a', 10 union all select 'b', 90 SELECT fie,num,str(num*1.0/x,15,2) val FROM ( SELECT fie,num FROM @a UNION SELECT 'total',SUM(num) n FROM @a) aa ,(SELECT SUM(num) x FROM @a ) bb--result /* fie num val -------------------- ----------- --------------- a 10 0.10 b 90 0.90 total 100 1.00(所影响的行数为 3 行) */
select t1.fie,t1.num,Rate=t1.num/t2.Total from Fee t1,(select Total=sum(num) from Fee) t2 union all select 'total',sum(num),1 from Fee
select fie=isnull(fie,'total'),num=sum(num)/100.0 from tab group by fie with rollup
if object_id('[tb]') is not null drop table [tb] go create table [tb]([fie] varchar(10),[num] int) insert [tb] select 'a',10 union all select 'b',90 go --select * from [tb]select isnull(fie,'total') fie,sum(num) num,scale=sum(num)*1.0/sum(num) over() from tb group by fie,num with rollup having grouping(fie)=grouping(num) /* fie num scale ---------- ----------- --------------------------------------- a 10 0.100000000000 b 90 0.900000000000 total 100 1.000000000000(3 行受影响) */
-- 测试数据 IF OBJECT_ID('LI') IS NOT NULL DROP TABLE LI CREATE TABLE LI (fie varchar(10), num int) INSERT LI SELECT 'a', 10 UNION ALL SELECT 'b', 90 --执行查询结果 SELECT T.FIE, cast(T.NUM * 0.1 / LO.AMOUNT * 10 as decimal(18,1)) AS MARK FROM LI T, ( SELECT SUM(T.NUM) AS AMOUNT FROM LI T) LO GROUP BY ROLLUP(T.FIE,T.NUM) --删除过程 DROP TABLE LI
学习蓉儿的方法呀。[code=SQL]SELECT isnull(fie,'total') fie,sum(num) num,scale=sum(num)*1.0/sum(num) over() from tb group by fie,num with rollup having grouping(fie)=grouping(num)[/code]真不错。
insert @t select 'a' , 10
insert @t select 'b', 90
select fie,num,num*1.0/(select sum(num) from @t) from @t
union all
select 'total',sum(num),1 from @t
/*fie num
---------- ----------- --------------------------
a 10 .100000000000
b 90 .900000000000
total 100 1.000000000000(影響 3 個資料列)*/
INSERT @a SELECT 'a', 10
union all select 'b', 90 SELECT fie,num,str(num*1.0/x,15,2) val FROM
(
SELECT fie,num FROM @a UNION SELECT 'total',SUM(num) n FROM @a) aa
,(SELECT SUM(num) x FROM @a ) bb--result
/*
fie num val
-------------------- ----------- ---------------
a 10 0.10
b 90 0.90
total 100 1.00(所影响的行数为 3 行) */
select t1.fie,t1.num,Rate=t1.num/t2.Total
from Fee t1,(select Total=sum(num) from Fee) t2
union all
select 'total',sum(num),1 from Fee
group by fie with rollup
go
create table [tb]([fie] varchar(10),[num] int)
insert [tb]
select 'a',10 union all
select 'b',90
go
--select * from [tb]select isnull(fie,'total') fie,sum(num) num,scale=sum(num)*1.0/sum(num) over()
from tb
group by fie,num with rollup
having grouping(fie)=grouping(num)
/*
fie num scale
---------- ----------- ---------------------------------------
a 10 0.100000000000
b 90 0.900000000000
total 100 1.000000000000(3 行受影响)
*/
IF OBJECT_ID('LI') IS NOT NULL
DROP TABLE LI CREATE TABLE LI (fie varchar(10), num int)
INSERT LI SELECT 'a', 10
UNION ALL SELECT 'b', 90
--执行查询结果
SELECT T.FIE, cast(T.NUM * 0.1 / LO.AMOUNT * 10 as decimal(18,1)) AS MARK
FROM LI T,
(
SELECT SUM(T.NUM) AS AMOUNT
FROM LI T) LO
GROUP BY ROLLUP(T.FIE,T.NUM) --删除过程
DROP TABLE LI
from tb
group by fie,num with rollup
having grouping(fie)=grouping(num)[/code]真不错。