mysql - 如何在不使用 with..as 的情况下计算 SQL 中的百分比？

Question

这是查询：

select grade_c as grade, count(c.grade_c) as num from `tblCustomerFeedback` c 

join `tblOrders`o on c.`invoice_n`=o.`invoice_n` 

join `tblProducts` p on o.`productID_n`=p.`productID_n`

where productName_c LIKE "%Premier%" 

group by c.grade_c

结果如下：

如何在不使用 with .. as 的情况下以最佳方式计算百分比，因为它在这个版本的 MySQL（5.6.10）中不可用？

标签： mysqlsql

Answer 1

Upgrade! This is much easier with window functions. In earlier versions, you can use variables:

select grade, num, num / total
from (select grade_c as grade, count(*) as num,
             (@sum := @sum + count(*)) as total
      from `tblCustomerFeedback` c join
           `tblOrders` o
           on c.`invoice_n` = o.`invoice_n` join
           `tblProducts` p
           on o.`productID_n` = p.`productID_n` cross join
           (select @sum := 0) params
      where productName_c LIKE "%Premier%" 
      group by c.grade_c
     ) g;