php - 带计数的 mysql 汇总

Question

我在计数列上陷入了 mysql ROLLUP 困境。你能指导一下吗？

我写了以下 mysql 查询

查询:

 SELECT (SELECT store.StoreName from store 
          WHERE store.storeOwnerId=redeem.StoreNo)
             AS CompanyName,
        (SELECT mscheme.SchemeName from mscheme 
          WHERE mscheme.SchemeNo = redeem.SchemeNo)
             AS SchemeName,
 count(*) AS Count  
 FROM kirana_app.redeem GROUP BY redeem.SchemeNo 
 ORDER BY CompanyName;

** 输出 **

 CompanyName,SchemeName, Count
'Dwarkesh', 'September-saffola', '25'
'Dwarkesh', 'September-Tea', '20'
'Nitin stores', 'Testing Scheme', '1'
'Nitin stores', 'September-Parle', '63'

我需要像计数列一样进行汇总

输出如下:

 CompanyName,SchemeName, Count
'Dwarkesh', 'September-saffola', '25'
'Dwarkesh', 'September-Tea', '20'
 **Dwarkesh', NULL, '45'**
'Nitin stores', 'Testing Scheme', '1'
'Nitin stores', 'September-Parle', '63'
 **'Nitin stores', NULL, '64**'

Answer 1

学习进行正确的连接而不是子查询。

SELECT s.StoreName AS CompanyName,
m.SchemeName AS SchemeName,
COUNT(*) AS Count
FROM redeem r
INNER JOIN store s ON r.StoreNo = s.storeOwnerId
INNER JOIN mscheme m ON m.SchemeNo = r.SchemeNo
GROUP BY s.CompanyName, m.SchemeNo 
WITH ROLLUP;

ORDER BY 是由 GROUP BY 隐式完成的。