sql - 过滤 View

Question

我有一个关于 MS SQL 查询的问题。我有以下要过滤的数据:

+----+----------+-------+-----------+    
| Id | Location | Store |  Result   |    
+----+----------+-------+-----------+    
|  1 | AB       |     1 | Executed  |    
|  2 | AB       |     1 | Cancelled |    
|  3 | AB       |     2 | Executed  |    
|  4 | AB       |     2 | Missing   |
|  5 | AB       |     2 | Executed  |    
|  6 | CD       |     3 | Cancelled |    
|  7 | CD       |     3 | Executed  |    
|  8 | EF       |     4 | Missing   |    
|  9 | EF       |     4 | Cancelled |    
| 10 | GH       |     5 | Cancelled |    
+----+----------+-------+-----------+

我想达到这样的结果:

+----------+-------+----------+    
| Location | Store |  Result  |    
+----------+-------+----------+
| AB       |     1 | Executed |    
| AB       |     2 | Executed |    
| CD       |     3 | Executed |    
| EF       |     4 | Missing  |    
| GH       |     5 | Cancelled|    
+----------+-------+----------+

已应用以下选择标准:

• 如果结果包含 1 行，请选择该状态(ID 10)

• 如果结果具有相同的位置和商店:

  • 如果该组有 1 行状态为已执行，请选择该状态
  • 如果组内有多条已执行的行，无论顺序如何，都选择1条已执行状态的行

每个组中的数据将始终相同，因此对于此示例，如果不符合我想要的条件，则 GROUP BY Location、Store 就足够了。

提前致谢!

Answer 1

使用 Row_number 为每个组合选择一行，如下所示:

Select Location, Store, Result from
  (Select Location, Store, Result
    , Row_Number() over (partition by Location, Store 
            order by case Result when 'Executed' then 1 else 2 end) as RN
from Table
  ) a
where RN = 1

正如 mellamokb 指出的那样，如果您不希望它在没有“已执行”行时随机选择结果，则可能需要向 case 语句添加更多选项。