over() 函数，最简略的一种
下列是SQL片断，正在外观嵌套一个Select是由于over 没有会SQL语句一切的data sum，而没有是 limit 以后的数据，以是须要先limit 正在over（）
SELECT
b.*,
ROUND(b.amount*100 / SUM(b.amount) OVER(),两) AS inv_salesShare
FROM (
SELECT
cm_cust_no,
cm_co_name,
SUM ( amount ) AS amount
FROMCross 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经由过程2个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到此那篇闭于MySQL sum后再计较percentage的多种经管方法的文章便先容到那了,更多相闭MySQL计较percentage形式请搜刮剧本之野之前的文章或者连续涉猎上面的相闭文章心愿大师之后多多撑持剧本之野！
发表评论 取消回复