sql 除法运算

 

一种:

use pubs;
create index idx_qty on dbo.sales(qty)
select ord_num,
cast(1.*qty/(select sum(qty) from sales)* 100 as decimal(5,2)) as num,
qty-(select AVG(qty) from sales) as diff,
ord_date
from sales

drop index dbo.sales.idx_qty

如下图:

 

 

 

二种:对聚合运算只扫描一次索引,计算2个聚合

select ord_num,
cast((1.*qty/agg.sumqty)*100 as decimal(5,2))
from sales,
(select SUM(qty) as sumqty, AVG(qty)as avgqty  from dbo.sales)as agg

 

 

;with agg as
(
   select sum(qty) as sumqty,AVG(qty)as avgqty from sales
)

select
cast(1.*qty/(select sumqty from agg)*100 as decimal(5,2)),
qty-(select avgqty from agg)
 from sales