mysql - WITH ROLLUP 结合多个 GROUP BY 条件
问题描述
我有下表：
CREATE TABLE PaL (
Event_Date DATE,
Country CHAR(2),
Category CHAR(255),
Revenue INTEGER(255),
Costs INTEGER(255)
);
INSERT INTO PaL
(Event_Date, Country, Category, Revenue, Costs)
VALUES
("2017-01-31", "DE", "Apparel", "692.09816652375", "-173.071989376023"),
("2017-02-28", "DE", "Apparel", "8419.9977988914", "-7622.61265984317"),
("2017-03-31", "DE", "Apparel", "2018.80471444031", "-1498.76213884283"),
("2017-04-30", "DE", "Apparel", "8863.15663035884", "-7965.69268589649"),
("2017-05-31", "DE", "Apparel", "6838.4514829573", "-1088.70351845663"),
("2017-06-30", "DE", "Apparel", "2025.73421386331", "-483.454199185678"),
("2017-07-31", "DE", "Apparel", "5389.0163788639", "-2643.93624645182"),
("2017-08-31", "DE", "Apparel", "6238.85870250446", "-1985.9879371866"),
("2017-09-30", "DE", "Apparel", "2294.62451106469", "-1864.98271539745"),
("2017-10-31", "DE", "Apparel", "4141.2074159951", "-197.773961036073"),
("2017-11-30", "DE", "Apparel", "1456.17584217397", "-1018.54129047119"),
("2017-12-31", "DE", "Apparel", "3623.54984724091", "-745.715567286581"),
("2017-01-31", "DE", "Shoes", "5955.20947079185", "-4745.39564508682"),
("2017-02-28", "DE", "Shoes", "9555.29563511224", "-5729.82601329738"),
("2017-03-31", "DE", "Shoes", "5490.36170257556", "-925.286457266534"),
("2017-04-30", "DE", "Shoes", "7652.35548686073", "-7335.32532050594"),
("2017-05-31", "DE", "Shoes", "9102.38987703511", "-5724.92574170071"),
("2017-06-30", "DE", "Shoes", "1703.95901703023", "-1678.19547060803"),
("2017-07-31", "DE", "Shoes", "3679.60045104324", "-2095.94207835501"),
("2017-08-31", "DE", "Shoes", "6672.43210841331", "-3475.55411014914"),
("2017-09-30", "DE", "Shoes", "7718.7744220635", "-1252.75877307055"),
("2017-10-31", "DE", "Shoes", "6976.01564153854", "-509.991595559256"),
("2017-11-30", "DE", "Shoes", "4725.46976319905", "-2835.09460170927"),
("2017-12-31", "DE", "Shoes", "8390.84483147949", "-7476.83516162742"),
("2017-01-31", "US", "Apparel", "939788.159047677", "-742666.846083707"),
("2017-02-28", "US", "Apparel", "826418.514009279", "-702997.151099908"),
("2017-03-31", "US", "Apparel", "775940.69563018", "-211238.971709086"),
("2017-04-30", "US", "Apparel", "516829.583069596", "-407521.856789393"),
("2017-05-31", "US", "Apparel", "635701.377748304", "-627829.016481388"),
("2017-06-30", "US", "Apparel", "757852.95599751", "-740948.867522139"),
("2017-07-31", "US", "Apparel", "154224.257732688", "-139805.456987081"),
("2017-08-31", "US", "Apparel", "102035.465731255", "-100103.875992667"),
("2017-09-30", "US", "Apparel", "880671.692714021", "-665324.083753931"),
("2017-10-31", "US", "Apparel", "187868.653562564", "-105676.793254039"),
("2017-11-30", "US", "Apparel", "994600.486892401", "-177382.899789215"),
("2017-12-31", "US", "Apparel", "813824.90461202", "-132527.311010471"),
("2017-01-31", "US", "Shoes", "661069.933966637", "-454778.427240679"),
("2017-02-28", "US", "Shoes", "675942.334464692", "-254489.623313569"),
("2017-03-31", "US", "Shoes", "473604.307973888", "-404226.047653847"),
("2017-04-30", "US", "Shoes", "872018.822577053", "-348781.396359871"),
("2017-05-31", "US", "Shoes", "718012.023481434", "-625306.312927362"),
("2017-06-30", "US", "Shoes", "688487.679029354", "-584512.575888519"),
("2017-07-31", "US", "Shoes", "690085.013711018", "-581753.771085971"),
("2017-08-31", "US", "Shoes", "204473.88894161", "-172301.871771595"),
("2017-09-30", "US", "Shoes", "516932.092423463", "-328002.737710081"),
("2017-10-31", "US", "Shoes", "609355.245817292", "-323624.391366703"),
("2017-11-30", "US", "Shoes", "313599.625504231", "-210253.020497022"),
("2017-12-31", "US", "Shoes", "723573.681040319", "-107333.764977439"),
("2017-01-31", "NZ", "Apparel", "81292.9610624533", "-27354.678748396"),
("2017-02-28", "NZ", "Apparel", "77473.6231986387", "-47920.2900396812"),
("2017-03-31", "NZ", "Apparel", "93819.4377266116", "-47582.1878235771"),
("2017-04-30", "NZ", "Apparel", "25580.8516093492", "-21277.2651303701"),
("2017-05-31", "NZ", "Apparel", "82842.9415935231", "-30714.5952859941"),
("2017-06-30", "NZ", "Apparel", "50878.6190715448", "-33047.3841488076"),
("2017-07-31", "NZ", "Apparel", "61423.3558286064", "-10811.2817583225"),
("2017-08-31", "NZ", "Apparel", "77517.2989019148", "-56818.7461336424"),
("2017-09-30", "NZ", "Apparel", "74046.1258000888", "-10108.0702908427"),
("2017-10-31", "NZ", "Apparel", "79490.650598675", "-68562.5753547413"),
("2017-11-30", "NZ", "Apparel", "65000.3971251097", "-25174.1329786955"),
("2017-12-31", "NZ", "Apparel", "99152.6457285608", "-42855.8431883814"),
("2017-01-31", "NZ", "Shoes", "20703.8970205884", "-11911.9616025915"),
("2017-02-28", "NZ", "Shoes", "72841.2537140946", "-14166.6747335237"),
("2017-03-31", "NZ", "Shoes", "45391.6550622383", "-40325.1638601903"),
("2017-04-30", "NZ", "Shoes", "58074.2843201579", "-54483.1122507654"),
("2017-05-31", "NZ", "Shoes", "52127.2701338519", "-28026.7984458694"),
("2017-06-30", "NZ", "Shoes", "32900.9222204099", "-22780.2637095601"),
("2017-07-31", "NZ", "Shoes", "18809.3868235169", "-11500.4020522949"),
("2017-08-31", "NZ", "Shoes", "67001.2729206886", "-53280.8129552599"),
("2017-09-30", "NZ", "Shoes", "26889.4058005421", "-24218.8734875798"),
("2017-10-31", "NZ", "Shoes", "56330.7544011198", "-51382.4201254223"),
("2017-11-30", "NZ", "Shoes", "60954.7129549264", "-19834.7256352672"),
("2017-12-31", "NZ", "Shoes", "97527.2014993995", "-83137.4844853141");
我使用以下查询从表中获取数据：
Select Country, Category, sum(Revenue) as Revenue, sum(Costs) as Costs
FROM Pal
WHERE Event_Date BETWEEN "2017-01-01" AND "2017-01-31"
GROUP BY Country, Category WITH ROLLUP
您还可以在sql fiddle 此处
找到包含数据的表格，
到目前为止所有这些都可以正常工作。
现在，我想知道如何避免该WITH ROLLUP函数计算每个国家/地区下方列的总数。相反，它应该只计算一次列总数，因此最终的结果如下所示：
Country Category Revenue Costs
DE Apparel 692 -173
DE Shoes 5955 -4745
: : : :
: : : :
: : : :
US Shoes 661070 -454778
(null) (null) 1709502 -1241630
为了实现这一点，我必须在我的 SQL 查询中进行哪些更改？
解决方案
MySQL 不支持GROUPING SETS，这是你真正想要的。也许最简单的方法是使用UNION ALL：
SELECT Country, Category, SUM(Revenue) as Revenue, SUM(Costs) as Costs
FROM Pal
WHERE Event_Date BETWEEN '2017-01-01' AND '2017-01-31'
GROUP BY Country, Category
UNION ALL
SELECT NULL, NULL, SUM(Revenue) as Revenue, SUM(Costs) as Costs
FROM Pal
WHERE Event_Date BETWEEN '2017-01-01' AND '2017-01-31';