如何仅为组中的第一行返回值

I have the below sample data

我有以下示例数据

MasterRecordId, MasterRecordDate, TargetDate, MasterRecordIdCount
1              | 15/02/2017      |02/02/2017  | 3
1              | 15/02/2017      |05/08/2017  | 3
1              | 15/02/2017      |12/12/2017  | 3
2              | 12/05/2017      |07/08/2017  | 2
2              | 12/05/2017      |10/08/2017  | 2

When i do a select query i want the output to be like below

当我做一个选择查询时，我希望输出如下所示

MasterRecordId, MasterRecordDate, TargetDate, MasterRecordIdCount
1              | 15/02/2017      |02/02/2017  | 3
1              |                 |05/08/2017  | 
1              |                 |12/12/2017  | 
2              |                 |07/08/2017  | 2
2              | 12/05/2017      |10/08/2017  |

Basically, MasterRecordDate and MasterRecordIdCount for MasterRecordId1 will always be the same so i dont want to repeat.

基本上，MasterRecordDate和MasterRecordIdCount for MasterRecordIdC将始终相同，所以我不想重复。

2 个解决方案

#1

This is the type of transformation that you should be doing in the application layer. Why? Because the result set from your version depends entirely on the ordering of the results.

这是您应该在应用程序层中进行的转换类型。为什么？因为您的版本的结果集完全取决于结果的顺序。

You can do this in SQL, but you need to ensure the final ordering:

您可以在SQL中执行此操作，但您需要确保最终排序：

select MasterRecordId,
       (case when seqnum = 1 then MasterRecordDate end) as MasterRecordDate,
       TargetDate,
       (case when seqnum = 1 then MasterRecordIdCount end) as MasterRecordIdCount
from (select t.*,
             row_number() over (partition by MasterRecordId order by TargetDate) as seqnum
      from t
     ) t
order by MasterRecordId, TargetDate;

Two very important points.

两个非常重要的观点。

(1) The outer query needs an order by to ensure that the result set is ordered appropriately. SQL does not guarantee the ordering of a result set unless you explicitly specify an order by.

（1）外部查询需要一个order by以确保结果集被正确排序。除非您明确指定顺序，否则SQL不保证结果集的排序。

(2) You might be tempted to eliminate the subquery and use row_number() twice. That does work for the data you have provided. However, if two dates are the same, you would run the risk of the two row_numbers() returning "1" on different rows.

（2）您可能想要消除子查询并使用row_number（）两次。这对您提供的数据有效。但是，如果两个日期相同，则会冒两个row_numbers（）在不同行上返回“1”的风险。

#2

Try this:

尝试这个：

SELECT MasterRecordId,
       ISNULL((CASE WHEN RN = 1 then MasterRecordDate END),'') AS MasterRecordDate,
       TargetDate,
       ISNULL((CASE WHEN RN = 1 then MasterRecordIdCount END),'') AS MasterRecordIdCount
FROM(
    SELECT MasterRecordId, MasterRecordDate, TargetDate, MasterRecordIdCount
        ,ROW_NUMBER() OVER(PARTITION BY MasterRecordId ORDER BY TargetDate)RN
    FROM Your_Table
    )D
ORDER BY MasterRecordId, TargetDate

#1