MYSQL 从分组中获取总数

Question

如何从这个查询中得到每个组的总数(总和)？是否可以从“总计”中选择值？*我会尝试汇总，但会出现错误:“不正确地使用 CUBE/ROLLUP 和 ORDER BY”。 Mysql 5.0版本

我的查询:

SELECT datatest.region, datatest.cusname, datatest.mt, COUNT(dt.region) AS rank
FROM datatest
LEFT JOIN datatest AS dt ON datatest.region = dt.region AND datatest.mt < dt.mt
GROUP BY datatest.region, datatest.mt
HAVING COUNT(dt.mt) < 2
ORDER BY datatest.region, datatest.mt DESC

数据模式:

CREATE TABLE datatest (region varchar(50) ,cusname varchar(50) ,mt int(50) ) ;

insert  into datatest values 
('central', 'J.O.E SUPPLIER SDN BHD', 135),
('central', 'AURAJUBLI SDN BHD', 12),
('central', 'NEW MT CENTURY SDN. BHD.', 1242),
('central', 'KMT SUPPLY SDN BHD', 42),
('eastern', 'SASHWIN SDN. BHD.', 5),
('eastern', 'INFARMS SDN . BHD', 765),
('eastern', 'GMAZ TRADING', 105),
('eastern', 'TMI PACIFIC SDN. BHD.', 1025),
('southern', 'KMT SUPPLY SDN BHD', 80135),
('southern', 'GMAZ TRADING', 85),
('southern', 'NEW MT CENTURY SDN. BHD.', 128),
('southern', 'INFARMS SDN . BHD', 67);

期望的结果:

Answer 1

因为您的查询中没有SUM，所以您不能使用WITH ROLLUP。但是您可以将查询包装到一个子查询中，然后在其外部使用 SUM(mt) 并使用 GROUP BY ... WITH ROLLUP:

SELECT region, cusname, SUM(mt) AS mt, rank FROM (
SELECT datatest.region, datatest.cusname, datatest.mt, COUNT(dt.region) AS rank
FROM datatest
LEFT JOIN datatest AS dt ON datatest.region = dt.region AND datatest.mt < dt.mt
GROUP BY datatest.region, datatest.mt
HAVING COUNT(dt.region) < 2
ORDER BY datatest.region, datatest.mt DESC) d
GROUP BY region, cusname WITH ROLLUP

输出:

region      cusname                     mt  rank    
central     J.O.E SUPPLIER SDN BHD      135     1
central     NEW MT CENTURY SDN. BHD.    1242    0
central     Total                       1377    0
eastern     INFARMS SDN . BHD           765     1
eastern     TMI PACIFIC SDN. BHD.       1025    0
eastern     Total                       1790    0
southern    KMT SUPPLY SDN BHD          80135   0
southern    NEW MT CENTURY SDN. BHD.    128     1
southern    Total                       80263   1
Grand Total                             83430   1

更新

为了保持区域按排名排序，有必要使这个查询成为一个子查询，然后从中排序结果:

SELECT *
FROM (SELECT COALESCE(region, 'Grand Total') AS region, IF(region IS NULL, '', COALESCE(cusname, 'Total')) AS cusname, SUM(mt) AS mt, rank 
      FROM (SELECT datatest.region, datatest.cusname, datatest.mt, COUNT(dt.region) AS rank
            FROM datatest
            LEFT JOIN datatest AS dt ON datatest.region = dt.region AND datatest.mt < dt.mt
            GROUP BY datatest.region, datatest.mt
            HAVING COUNT(dt.region) < 2
            ORDER BY datatest.region, datatest.mt DESC) d
      GROUP BY region, cusname WITH ROLLUP) r
ORDER BY region='Grand Total', region, cusname='Total', rank

输出:

region      cusname                     mt  rank    
central     NEW MT CENTURY SDN. BHD.    1242    0
central     J.O.E SUPPLIER SDN BHD      135     1
central     Total                       1377    0
eastern     TMI PACIFIC SDN. BHD.       1025    0
eastern     INFARMS SDN . BHD           765     1
eastern     Total                       1790    0
southern    KMT SUPPLY SDN BHD          80135   0
southern    NEW MT CENTURY SDN. BHD.    128     1
southern    Total                       80263   1
Grand Total                             83430   1