在groupby中使用limit

mrfwxfqh  于 2021-06-25  发布在  Mysql
关注(0)|答案(1)|浏览(555)

这是我的table。

  1. Financials:
  2. Date
  3. CountryID
  4. ProductID
  5. Revenue
  6. Cost

我需要找出每个国家收入前五名的产品。有些产品会多次上市,所以我需要为每个产品的收入加总。

  1. SELECT
  2. Financials.CountryID,
  3. Financials.ProductID,
  4. SUM(Financials.Revenue) AS total
  5. FROM Financials
  6. INNER JOIN (SELECT
  7. CountryID,
  8. GROUP_CONCAT (ProductID ORDER BY Revenue DESC) grouped_ID
  9. FROM Financials
  10. GROUP BY CountryID) group_max
  11. ON Financials.CountryID = group_max.CountryID
  12. AND FIND_IN_SET(ProductID, grouped_ID) BETWEEN 1 AND 6
  13. GROUP BY Financials.ProductID
  14. ORDER BY Financials.CountryID, total DESC

这就是我目前所知道的。它不起作用。救命啊?
编辑
我一直在用https://sqltest.net/. 请参阅下面的插入命令

  1. CREATE TABLE mysql_test (
  2. id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  3. CountryID VARCHAR(30) NOT NULL,
  4. ProductID VARCHAR(30) NOT NULL,
  5. Revenue VARCHAR(50),
  6. cost VARCHAR(50),
  7. reg_date TIMESTAMP
  8. );
  9. CREATE TABLE mysql_test_sql (
  10. id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  11. CountryID VARCHAR(30) NOT NULL,
  12. ProductID VARCHAR(30) NOT NULL,
  13. Revenue VARCHAR(50),
  14. cost VARCHAR(50),
  15. reg_date TIMESTAMP
  16. );
  17. INSERT INTO `mysql_test` (`id`, `CountryID`, `ProductID`, `Revenue`, `cost`, `reg_date`) VALUES ('1', 'Canada', 'Doe', '20', '5', '2010-01-31 12:01:01');
  18. INSERT INTO `mysql_test` (`id`, `CountryID`, `ProductID`, `Revenue`, `cost`, `reg_date`) VALUES ('2', 'USA', 'Tyson', '40', '15', '2010-02-14 12:01:01');
  19. INSERT INTO `mysql_test` (`id`, `CountryID`, `ProductID`, `Revenue`, `cost`, `reg_date`) VALUES ('3', 'France', 'Keaton', '80', '25', '2010-03-25 12:01:01');
  20. INSERT INTO `mysql_test` (`id`, `CountryID`, `ProductID`, `Revenue`, `cost`, `reg_date`) VALUES ('4', 'France', 'Joe', '180', '45', '2010-04-25 12:01:01');
  21. INSERT INTO `mysql_test` (`id`, `CountryID`, `ProductID`, `Revenue`, `cost`, `reg_date`) VALUES ('5', 'France', 'Bill', '30', '6', '2010-04-25 12:01:01');
  22. INSERT INTO `mysql_test` (`id`, `CountryID`, `ProductID`, `Revenue`, `cost`, `reg_date`) VALUES ('6', 'France', 'Emma', '15', '2', '2010-04-25 12:01:01');
  23. INSERT INTO `mysql_test` (`id`, `CountryID`, `ProductID`, `Revenue`, `cost`, `reg_date`) VALUES ('7', 'France', 'Joe', '60', '36', '2010-04-25 12:01:01');
  24. INSERT INTO `mysql_test` (`id`, `CountryID`, `ProductID`, `Revenue`, `cost`, `reg_date`) VALUES ('8', 'France', 'Jammer', '130', '26', '2010-04-25 12:01:01');
  25. INSERT INTO `mysql_test` (`id`, `CountryID`, `ProductID`, `Revenue`, `cost`, `reg_date`) VALUES ('9', 'France', 'Louis', '350', '12', '2010-04-25 12:01:01');
  26. INSERT INTO `mysql_test` (`id`, `CountryID`, `ProductID`, `Revenue`, `cost`, `reg_date`) VALUES ('10', 'France', 'dennis', '100', '175', '2010-04-25 12:01:01');
  27. INSERT INTO `mysql_test` (`id`, `CountryID`, `ProductID`, `Revenue`, `cost`, `reg_date`) VALUES ('11', 'USA', 'zooey', '70', '16', '2010-04-25 12:01:01');
  28. INSERT INTO `mysql_test` (`id`, `CountryID`, `ProductID`, `Revenue`, `cost`, `reg_date`) VALUES ('12', 'France', 'Alex', '2', '16', '2010-04-25 12:01:01');
eoxn13cs

eoxn13cs1#

这里有一种方法:

  1. select countryId,
  2. substring_index(group_concat(productId order by revenue desc), ',', 5) as top5
  3. from (select countryId, productId, sum(revenue) as revenue
  4. from mysql_test
  5. group by countryId, productId
  6. ) cp
  7. group by countryId;

注意:对于的中间结果,默认的内部限制为1028个字符 group_concat() . 每个国家最多可以生产几百种产品。默认长度是系统设置,可以更改。

相关问题