时间戳

首页 > 解决方案 > 用sql简化

mysql - 用sql简化

问题描述

我有一些任务应该用 sql 来完成。以下是我目前使用的功能。

(SELECT ProductName, sum(Quantity*Price) Revenue, Country
  FROM products p
    JOIN orderdetails d
      ON p.ProductID = d.ProductID
      JOIN orders o
        ON o.OrderID = d.OrderID
        JOIN customers c
          ON c.CustomerID = o.CustomerID
    WHERE Country = 
    (
      SELECT DISTINCT Country
        FROM customers
          LIMIT 1
    )
      GROUP BY ProductName
        ORDER BY Revenue DESC
          LIMIT 1)
UNION
(SELECT ProductName, sum(Quantity*Price) Revenue, Country
  FROM products p
    JOIN orderdetails d
      ON p.ProductID = d.ProductID
      JOIN orders o
        ON o.OrderID = d.OrderID
        JOIN customers c
          ON c.CustomerID = o.CustomerID
    WHERE Country = 
    (
      SELECT DISTINCT Country
        FROM customers
          LIMIT 1,1
    )
      GROUP BY ProductName
        ORDER BY Revenue DESC
          LIMIT 1)        
UNION
(SELECT ProductName, sum(Quantity*Price) Revenue, Country
  FROM products p
    JOIN orderdetails d
      ON p.ProductID = d.ProductID
      JOIN orders o
        ON o.OrderID = d.OrderID
        JOIN customers c
          ON c.CustomerID = o.CustomerID
    WHERE Country = 
    (
      SELECT DISTINCT Country
        FROM customers
          LIMIT 2,1
    )
      GROUP BY ProductName
        ORDER BY Revenue DESC
          LIMIT 1)

我的任务是“根据每个国家/地区的收入找到最畅销的产品！ ”

我想要的结果如下：

产品名称 收入 国家
豆腐 279 阿根廷
科特德布莱 18445 奥地利

您可以从此链接RawDatabase访问我使用的数据

我使用的样本数据是这样的

产品名称 国家 收入
可可 阿根廷 120
面包 奥地利 10000
紧缩 奥地利 13265
科特德布莱 奥地利 18445
牛奶 阿根廷 254
豆腐 阿根廷 279

从这些数据中，我只想按收入为每个国家/地区选择最佳产品。数据中有21个国家。我应该怎么做才能得到下面的结果

产品名称 收入 国家
豆腐 279 阿根廷
科特德布莱 18445 奥地利

在我看来，唯一的方法是仅按每个国家/地区过滤数据，然后获得最好的产品，然后像我在上面给出的代码一样将它们合并。我想知道是否还有其他方法。

标签： mysqlsqlfor-loopsubquerysimplify

解决方案

使用 row_number 窗口函数或与国家/地区 maxrevenue 进行比较

DROP TABLe if exists t;
create table t
(ProdName varchar(20),  Country varchar(20),    Revenue int);
insert into t values
('coco' ,'Argentina'    ,120),
('bread'    ,'Austria'     ,10000),
('crunch','Austria'    ,13265),
('Cote de Blaye'    ,'Austria', 18445),
('milk' ,'Argentina'    ,254),
('Tofu' ,'Argentina'    ,279);

select * 
from
(
select prodname,country,revenue, 
         row_number() over(partition by country order by revenue desc) rn
from t
) s
where rn = 1;

或者

select * 
from t
join (select t.country,max(t.revenue) maxrevenue from t group by t.country) t1
        on t1.country = t.country and t1.maxrevenue = t.revenue;

+---------------+-----------+---------+----+
| prodname      | country   | revenue | rn |
+---------------+-----------+---------+----+
| Tofu          | Argentina |     279 |  1 |
| Cote de Blaye | Austria   |   18445 |  1 |
+---------------+-----------+---------+----+
2 rows in set (0.001 sec)

推荐阅读