over() 函数,最简单的一种
以下是SQL片段,在外面嵌套一个Select是因为over 不会SQL语句所有的data sum,而不是 limit 之后的数据,所以需要先limit 在over()
SELECT
b.*,
ROUND(b.amount*100 / SUM(b.amount) OVER(),2) AS inv_salesShare
FROM (
SELECT
cm_cust_no,
cm_co_name,
SUM ( amount ) AS amount
FROM
Cross Join
通过cross join 把total 作为一个结果集并到sum的sql,然后通过除法计算Percentage。
SELECT
P.PersonID, SUM(PA.Total)
SUM(PA.Total) * 100 / [p] AS 'Percentage'
FROM
Person P
JOIN
Package PA ON P.PersonID = PA.PackageFK
CROSS JOIN
(SELECT SUM(PA.[Total]) AS [p]
FROM Package PA) t
GROUP BY
P.PersonID
通过两个Select 嵌套查询
这种方法适合简单Case,如果条件复杂时,第二个Select就会臃肿,比如 增加 时间条件、Group,Limit等。
SELECT PA.PersonID, SUM(PA.Total),
SUM(PA.Total) * 100.0 / (SELECT SUM(PA.Total) FROM PA)
FROM Package PA
GROUP BY PA.PersonID;
通过with 函数处理
此方法简单,分块处理,一些复杂的SQL可以考虑使用with方法处理。
有时间验证下
;WITH PersonTotals AS
(
SELECT P.PersonID, SUM(CAST(PA.Total AS MONEY)) Total
FROM Person P
JOIN Package PA ON P.PersonID = PA.PackageFK
GROUP BY P.PersonID
),
GrandTotal AS
(
SELECT SUM(PT.Total) Total
FROM PersonTotals PT
)
SELECT PT.*, (PT.Total / NULLIF((SELECT Total From GrandTotal),0)) * 100 Percentage
FROM PersonTotals PT