mysql - 如何过滤从窗口函数派生的列

Question

我有一个表格如下

+---------+-----------------+-----------------+--------------------+--------------+-------+--------+
| OrderID |    OrderDate    |    ShipDate     |    CustomerName    |     City     | Sales | Profit |
+---------+-----------------+-----------------+--------------------+--------------+-------+--------+
|    1001 | 11/8/2016 0:00  | 11/11/2016 0:00 | Claire Gute        | Henderson    |   262 |     42 |
|    1002 | 11/8/2016 0:00  | 11/11/2016 0:00 | Darrin Van Huff    | Henderson    |   732 |    220 |
|    1003 | 6/12/2016 0:00  | 6/16/2016 0:00  | Claire Gute        | Henderson    |    15 |      7 |
|    1004 | 10/11/2015 0:00 | 10/18/2015 0:00 | Brosina Hoffman    | Henderson    |   958 |   -383 |
|    1005 | 10/11/2015 0:00 | 10/18/2015 0:00 | Claire Gute        | Henderson    |    22 |      3 |
|    1006 | 6/9/2014 0:00   | 6/14/2014 0:00  | Irene Maddox       | Fort Worth   |    49 |     14 |
|    1007 | 6/9/2014 0:00   | 6/14/2014 0:00  | Harold Pawlan      | Fort Worth   |     7 |      2 |
|    1008 | 6/9/2014 0:00   | 6/14/2014 0:00  | Pete Kriz          | Fort Worth   |   907 |     91 |
|    1009 | 6/9/2014 0:00   | 6/14/2014 0:00  | Irene Maddox       | Fort Worth   |    19 |      6 |
|    1010 | 6/9/2014 0:00   | 6/14/2014 0:00  | Zuschuss Donatelli | Fort Worth   |   115 |     34 |
|    1011 | 6/9/2014 0:00   | 6/14/2014 0:00  | Ken Black          | Philadelphia |  1706 |     85 |
|    1012 | 6/9/2014 0:00   | 6/14/2014 0:00  | Sandra Flanagan    | Philadelphia |   911 |     68 |
|    1013 | 4/15/2017 0:00  | 4/20/2017 0:00  | Ken Black          | Philadelphia |    16 |      5 |
|    1014 | 12/5/2016 0:00  | 12/10/2016 0:00 | Eric Hoffmann      | Philadelphia |   408 |    133 |
|    1015 | 11/22/2015 0:00 | 11/26/2015 0:00 | Tracy Blumstein    | Naperville   |    69 |   -124 |
|    1016 | 11/22/2015 0:00 | 11/26/2015 0:00 | Matt Abelman       | Melbourne    |     3 |     -4 |
|    1017 | 11/22/2015 0:00 | 11/26/2015 0:00 | Gene Hale          | Melbourne    |   666 |     13 |
|    1018 | 5/13/2014 0:00  | 5/15/2014 0:00  | Steve Nguyen       | Melbourne    |    56 |     10 |
|    1019 | 5/13/2014 0:00  | 5/15/2014 0:00  | Linda Cazamias     | Dover        |     9 |      2 |
|    1020 | 5/13/2014 0:00  | 5/15/2014 0:00  | Ruben Ausman       | Dover        |   113 |     35 |
+---------+-----------------+-----------------+--------------------+--------------+-------+--------+

我在下面编写查询以获取每个 city 中基于 Sales 的给定订单的 rank

SELECT OrderID, OrderDate, ShipDate, CustomerName, City, Sales, 
RANK() OVER(PARTITION BY City ORDER BY Sales DESC) as RankA 
FROM Table1

我得到的结果完全正确

+---------+---------------------+---------------------+--------------------+--------------+-------+-------+
| OrderID |      OrderDate      |      ShipDate       |    CustomerName    |     City     | Sales | RankA |
+---------+---------------------+---------------------+--------------------+--------------+-------+-------+
|    1020 | 2014-05-13 00:00:00 | 2014-05-15 00:00:00 | Ruben Ausman       | Dover        |   113 |     1 |
|    1019 | 2014-05-13 00:00:00 | 2014-05-15 00:00:00 | Linda Cazamias     | Dover        |     9 |     2 |
|    1008 | 2014-06-09 00:00:00 | 2014-06-14 00:00:00 | Pete Kriz          | Fort Worth   |   907 |     1 |
|    1010 | 2014-06-09 00:00:00 | 2014-06-14 00:00:00 | Zuschuss Donatelli | Fort Worth   |   115 |     2 |
|    1006 | 2014-06-09 00:00:00 | 2014-06-14 00:00:00 | Irene Maddox       | Fort Worth   |    49 |     3 |
|    1009 | 2014-06-09 00:00:00 | 2014-06-14 00:00:00 | Irene Maddox       | Fort Worth   |    19 |     4 |
|    1007 | 2014-06-09 00:00:00 | 2014-06-14 00:00:00 | Harold Pawlan      | Fort Worth   |     7 |     5 |
|    1004 | 2015-10-11 00:00:00 | 2015-10-18 00:00:00 | Brosina Hoffman    | Henderson    |   958 |     1 |
|    1002 | 2016-11-08 00:00:00 | 2016-11-11 00:00:00 | Darrin Van Huff    | Henderson    |   732 |     2 |
|    1001 | 2016-11-08 00:00:00 | 2016-11-11 00:00:00 | Claire Gute        | Henderson    |   262 |     3 |
|    1005 | 2015-10-11 00:00:00 | 2015-10-18 00:00:00 | Claire Gute        | Henderson    |    22 |     4 |
|    1003 | 2016-06-12 00:00:00 | 2016-06-16 00:00:00 | Claire Gute        | Henderson    |    15 |     5 |
|    1017 | 2015-11-22 00:00:00 | 2015-11-26 00:00:00 | Gene Hale          | Melbourne    |   666 |     1 |
|    1018 | 2014-05-13 00:00:00 | 2014-05-15 00:00:00 | Steve Nguyen       | Melbourne    |    56 |     2 |
|    1016 | 2015-11-22 00:00:00 | 2015-11-26 00:00:00 | Matt Abelman       | Melbourne    |     3 |     3 |
|    1015 | 2015-11-22 00:00:00 | 2015-11-26 00:00:00 | Tracy Blumstein    | Naperville   |    69 |     1 |
|    1011 | 2014-06-09 00:00:00 | 2014-06-14 00:00:00 | Ken Black          | Philadelphia |  1706 |     1 |
|    1012 | 2014-06-09 00:00:00 | 2014-06-14 00:00:00 | Sandra Flanagan    | Philadelphia |   911 |     2 |
|    1014 | 2016-12-05 00:00:00 | 2016-12-10 00:00:00 | Eric Hoffmann      | Philadelphia |   408 |     3 |
|    1013 | 2017-04-15 00:00:00 | 2017-04-20 00:00:00 | Ken Black          | Philadelphia |    16 |     4 |
+---------+---------------------+---------------------+--------------------+--------------+-------+-------+

但是现在我有两个请求

1. 只保留 RankA 为 1 的那些行
2. 只保留 RankA 小于 3(即 1 或 2)的那些行

我不知道如何过滤 RankA 列

我愿意在 SQL-Server 和 mysql 中寻求解决方案

Answer 1

只需使用子查询，然后应用过滤器

select * from (
SELECT OrderID, OrderDate, ShipDate, CustomerName, City, Sales, 
RANK() OVER(PARTITION BY City ORDER BY Sales DESC) as RankA 
FROM Table1
) t where t.RankA=1

可以用cte写

with cte as
(
SELECT OrderID, OrderDate, ShipDate, CustomerName, City, Sales, 
RANK() OVER(PARTITION BY City ORDER BY Sales DESC) as RankA 
FROM Table1
) select * from cte
  where cte.RankA<3