MySQL-如何加入两个子查询

我正在尝试创建一个查询，该查询将为我提供:
-商店名称
-顶级销售员
-销售员销售值(value)
-经理姓名

我已经成功创建了以下两个查询。这两个查询都有效，但我只需要帮助就可以将它们结合在一起。

从每个商店获取顶级销售人员。

SELECT MAX(sales) FROM (SELECT shopid, SUM(amount) AS sales
FROM fss_Payment GROUP BY empnin) AS salesdata GROUP BY shopid

获取所有经理及其商店

SELECT s.shopname, e.empname FROM fss_Shop s
JOIN fss_Employee e ON e.shopid = s.shopid AND e.mgrnin=""
ORDER BY s.shopid

现在，我需要结合两个查询的结果。我试图这样做，如下所示:

SELECT * FROM
(SELECT s.shopname, e.empname FROM fss_Shop s
JOIN fss_Employee e ON e.shopid = s.shopid AND e.mgrnin=""
) x
JOIN
(SELECT MAX(sales) FROM (SELECT shopid, SUM(amount) AS sales
    FROM fss_Payment GROUP BY empnin) AS salesdata GROUP BY shopid
) y
ON x.shopid = y.shopid

在此链接中查看我的一些表的样子-https://www.db-fiddle.com/f/t94XmTEMgXpmSLS3e8HWAh/1

最佳答案

更新

MySQL 8.0引入了窗口函数

https://dev.mysql.com/doc/refman/8.0/en/window-functions.html

以下原始答案仍适用于8.0之前的MySQL版本。

原始答案

我们认为这将是一件简单的事情。并且一些数据库为我们提供了分析/窗口功能，这使得这相当容易。在MySQL中，我们有两种选择:采用直接的SQL方法，或者使用一些用户定义的变量来模拟解析函数。

对于直接的SQL方法，我们可以一次构建查询。

首先，我们可以从每个商店获得每个销售员的总销售额

  SELECT p.shopid
       , p.empnin
       , SUM(p.amount) AS sales
    FROM fss_Payment p
   GROUP
      BY p.shopid
       , p.empnin
   ORDER
      BY p.shopid ASC
       , SUM(p.amount) DESC

查看结果并确认这是正确的。然后，我们可以将该查询用作另一个查询的内联 View ，以获取每个商店的“最高”总销售额:

  -- get highest total sales for each store
  SELECT q.shopid
       , MAX(q.sales) AS highest_sales
    FROM ( SELECT p.shopid
                , p.empnin
                , SUM(p.amount) AS sales
             FROM fss_payment p
            GROUP
               BY p.shopid
                , p.empnin
         ) q
   GROUP
      BY q.shopid
   ORDER
      BY q.shopid

我们可以采用该结果，然后将其加入每个销售员/商店的总销售额中，以获得该商店具有匹配的“最高”销售额的销售员

  -- get salesperson with the highest total sales for each store
  SELECT t.shopid
       , t.empnin
       , t.sales
    FROM ( SELECT q.shopid
                , MAX(q.sales) AS highest_sales
             FROM ( SELECT p.shopid
                         , p.empnin
                         , SUM(p.amount) AS sales
                      FROM fss_payment p
                     GROUP
                        BY p.shopid
                         , p.empnin
                  ) q
            GROUP
               BY q.shopid
         ) r
    JOIN ( SELECT s.shopid
                , s.empnin
                , SUM(s.amount) AS sales
             FROM fss_payment s
            GROUP
               BY s.shopid
                , s.empnin
         ) t
      ON t.shopid = r.shopid
     AND t.sales  = r.highest_sales

如果有两个(或多个)empnin的总销售量相同(最高并列第一名)，则此查询将返回两个(或全部)这些销售人员。

现在，我们只需要向fss_shop添加一个联接以获取storename，并向fss_employee添加几个联接以获取销售员名称，并获取该销售员的经理

将此添加到查询中，

   JOIN fss_shop h
     ON h.shopid = t.shopid
   LEFT
   JOIN fss_employee e
     ON e.empnin = t.empnin
   LEFT
   JOIN fss_employee m
     ON m.empnin = e.mgrnin

通过子句添加订单

  ORDER BY h.storename, e.empname

并将适当的表达式添加到SELECT列表中。

将所有内容放在一起，我们得到的是这样的:

  SELECT h.shopname       AS `storename`
       , e.empname        AS `top salesperson`
       , t.sales          AS `salesperson sales value`
       , m.empname        AS `manager name`
    FROM ( SELECT q.shopid
                , MAX(q.sales) AS highest_sales
             FROM ( SELECT p.shopid
                         , p.empnin
                         , SUM(p.amount) AS sales
                      FROM fss_payment p
                     GROUP
                        BY p.shopid
                         , p.empnin
                  ) q
            GROUP
               BY q.shopid
         ) r
    JOIN ( SELECT s.shopid
                , s.empnin
                , SUM(s.amount) AS sales
             FROM fss_payment s
            GROUP
               BY s.shopid
                , s.empnin
         ) t
      ON t.shopid = r.shopid
     AND t.sales  = r.highest_sales
    JOIN fss_shop h
      ON h.shopid = t.shopid
    LEFT
    JOIN fss_employee e
      ON e.empnin = t.empnin
    LEFT
    JOIN fss_employee m
      ON m.empnin = e.mgrnin
   ORDER BY h.storename, e.empname

为了回答您提出的问题，如何将这两个查询结合在一起以获得指定的结果:我认为这两个查询不可能。

返回的manager name是员工的经理。如果我们想聘请商店经理，请更改m的加入条件以匹配h而不是e。

我提到的另一种方法是利用用户定义的变量。通过这种方法，每个商店只需要一名“顶级销售人员”就更容易了(当规范中总是有一个“领带打破者”时。顶级销售人员没有任何联系。)

使用用户定义的变量，我们可以在大型集合上获得更好的性能。但是，这种方法还依赖于《 MySQL引用手册》中记录的无法保证的行为。

关于MySQL-如何加入两个子查询，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/47561248/