mysql - 在透视 mysql 查询中获取百分比

Question

我的数据看起来像

 EmployeeId      paycategory            value

      1             Contribution         200
      1              Salary              18000

我使用以下查询，

select employeeid,
    max(case when paycategory = 'Salary'       then value end) salary,
    max(case when paycategory = 'Contribution' then value end) contribution
from reports
group by employeeid

上面显示的结果为，

EmployeeId        Salary      Contribution
      1             18000           200

这很棒。现在我想显示对工资的贡献百分比

所以我期待类似的东西，

EmployeeId        Salary      Contribution      Contribution Percentage
      1             18000           200            1.11 -- (200/18000)*100

如果可能的话，我该如何使用数据透视查询呢？

标签： mysqlaggregatecommon-table-expressionmysql-8.0

Answer 1

您可以使用子查询，如下所示：

select 
employeeid, 
salary, 
contribution, 
(contribution/salary)*100 Contribution_Percentage 
from (
select employeeid,
    max(case when paycategory = 'Salary'       then value end) salary,
    max(case when paycategory = 'Contribution' then value end) contribution
from reports
group by employeeid 
) t

小提琴上的演示

对于 MySql 8.0，您可以使用Common Table Expression如下：

with cte as (
select employeeid,
    max(case when paycategory = 'Salary'       then value end) salary,
    max(case when paycategory = 'Contribution' then value end) contribution
from reports
group by employeeid 
)
select 
employeeid, 
salary, 
contribution, (contribution/salary)*100 Contribution_Percentage
from cte

演示