除了在2组之间没有正确的交叉点

Question

我想从2019年3月开始吸引最畅销的人，同时不包括一月的前三名卖家。我尝试使用除了第一个SELECT提供3月的畅销书(全部(，第二个SELECT给出了1月的前三名。

SELECT * FROM (SELECT fullname, SUM(sale) sales
FROM mytable
WHERE oredrdate BETWEEN '2019-03-01' AND '2019-03-31'
GROUP BY fullname
ORDER BY sales DESC) X
EXCEPT
SELECT * FROM (SELECT fullname, SUM(sale) sales
FROM mytable
WHERE oredrdate BETWEEN '2019-01-01' AND '2019-01-31'
GROUP BY fullname
ORDER BY sales DESC
LIMIT 3) Y;

问题是EXCEPT不会像我希望的那样相交。每个SELECT返回的内容以及我所需的输出，并带有数据：

第一个SELECT返回：

fullname           sales
Tommy Williams     8320
Ryan Atkinson      7310
Petey Cruiser      6200
Anna Mull          5840
Gail Forcewind     4120
Paige Turner       3300
Bob Frapples       2100
...                ...

秒 SELECT返回：

fullname           sales
Tommy Williams     9220
Anna Mull          8100
Greta Life         7891

所需的输出：

fullname           sales
Ryan Atkinson      7310
Petey Cruiser      6200
Gail Forcewind     4120
Paige Turner       3300
Bob Frapples       2100
...                ...

我应该如何更改代码以实现这一目标？

Answer 1

可以使用左联接来完成匹配行：

SELECT X.* 
FROM (
  SELECT fullname, SUM(sale) sales
  FROM mytable
  WHERE oredrdate BETWEEN '2019-03-01' AND '2019-03-31'
  GROUP BY fullname
) X LEFT JOIN (
  SELECT fullname, SUM(sale) sales
  FROM mytable
  WHERE oredrdate BETWEEN '2019-01-01' AND '2019-01-31'
  GROUP BY fullname
  ORDER BY sales DESC
  LIMIT 3
) Y ON Y.fullname = X.fullname
WHERE Y.fullname IS NULL
ORDER BY X.sales DESC

Answer 2

您可以使用：

SELECT fullname, SUM(sales) AS total
FROM mytable
WHERE oredrdate BETWEEN '2019-03-01' AND '2019-03-31'
  AND fullname NOT IN (SELECT fullname, SUM(sales) AS total
                       FROM mytable
                       WHERE oredrdate BETWEEN '2019-01-01' AND '2019-01-31'
                         AND fullname IS NOT NULL
                       GROUP BY fullname
                       ORDER BY total DESC LIMIT 3)
GROUP BY fullname
ORDER BY total DESC;

我会按某种独特的专栏进行分组，例如员工_id，两个人可能具有相同的名字。

Answer 3

问题是 EXCEPT都在考虑 名称和数量列。第二个不太可能匹配。

写这篇文章的一种方法是：

WITH jan3 as (
      SELECT TOP (3) fullname, SUM(sale) as sales
      FROM mytable
      WHERE orderdate >= '2019-01-01' AND
            orderdate < '2019-02-01'
      GROUP BY fullname
      ORDER BY sales DESC
     )
SELECT m.fullname, SUM(m.sale) as sales
FROM mytable m
WHERE m.orderdate >= '2019-03-01' AND
      m.orderdate < '2019-04-01' AND
      NOT EXISTS (SELECT 1
                  FROM jan3
                  WHERE jan3.fullname = m.fullname
                 )
GROUP BY fullname
ORDER BY sales DESC;

请注意，这更改了使用>=和<的日期比较。这被认为是最佳实践，因为它适用于日期和日期(时间戳(值。

还有其他仅使用单个聚合来编写此内容的方法。例如：

WITH s as (
      SELECT m.fullname,
             SUM(CASE WHEN m.orderdate < '2019-02-01' THEN m.sale END) as sales_jan,
             SUM(CASE WHEN m.orderdate >= '2019-03-01' THEN m.sale END) as sales_mar
      FROM mytable m
      WHERE m.orderdate >= '2019-01-01' AND
            m.orderdate < '2019-04-01'
     )
SELECT s.*
FROM (SELECT s.*,
             ROW_NUMBER() OVER (ORDER BY sales_jan DESC) as seqnum_jan
      FROM s
     ) s
WHERE seqnum_jan > 3
ORDER BY s.sales_mar;