sql - 仅显示一次分组列中的记录

Question

我有一个大表，其基本结构如下:

Aircode  FlightNumber Locator Title
A01      F01          A       Miss
A01      F01          A       Mr
A01      F01          B       Miss
A01      F01          B       Miss
A01      F02          A       Mr
A01      F02          A       Mr
A01      F02          C       Mr
A02      F01          A       Mr

我想知道是否可以在 MSSQL 中创建一个查询并仅返回一次组名称，例如:

Aircode  FlightNumber Locator Title
A01      F01          A       Miss
                              Mr
                      B       Miss
                              Miss
         F02          A       Mr
                              Mr
                      C       Mr
A02      F01          A       Mr

经过一番搜索后，我找到了使用 Row_Number() Over (Partition by) 的方法，但在这种情况下，我对多个组感到非常困惑，因为我想不出隐藏多个列中的多个值的方法。我想知道是否有正确的方法来处理这段数据。

编辑1通过应用CTE和ROW_NUMBER方法，它首先返回没有值的空白记录，然后返回标题、定位器等。示例:

Aircode  FlightNumber Locator Title


                              Miss
                              Miss
                      A       Mr
 A01       F01        B       Miss
 A02       F02        B       Miss

Answer 1

尝试为每个分区级别分配一个 ROW_NUMBER 并仅显示第一个分区的文本:

WITH cte AS (
  SELECT
     [Aircode]
    ,[FlightNumber]
    ,[Locator]
    ,[Title]
    ,[r1] = ROW_NUMBER() 
               OVER(PARTITION BY [Aircode] 
                    ORDER BY [AirCode])
    ,[r2] = ROW_NUMBER() 
               OVER(PARTITION BY [Aircode], [FlightNumber] 
                    ORDER BY [FlightNumber])
    ,[r3] = ROW_NUMBER() 
               OVER(PARTITION BY [Aircode], [FlightNumber], [Locator]
                    ORDER BY [Locator])
  FROM <table>
)
SELECT  [Aircode]      = CASE WHEN [r1] = 1 THEN [Aircode] ELSE '' END
       ,[FlightNumber] = CASE WHEN [r2] = 1 THEN [FlightNumber] ELSE '' END
       ,[Locator]      = CASE WHEN [r3] = 1 THEN [Locator] ELSE '' END
       ,[Title]
FROM cte
ORDER BY cte.[Aircode], cte.[FlightNumber], cte.[Locator], cte.[Title]