如何在SQL Server选择中执行此操作?
例:
ID Name Event
------------------------------
1 Mary Running 20/10/2017
1 Mary Running 20/10/2017
1 Mary Stopped 20/10/2017
1 Mary Running 20/10/2017
2 John Stopped 20/10/2017
2 John Running 20/10/2017
3 Lucy Stopped 20/10/2017
3 Lucy Running 20/10/2017
3 Lucy Stopped 20/10/2017
我想为每个 ID 显示结果 1,但只能在一行中显示
ID Name Event Row
-----------------------------------
1 Mary Running 20/10/2017 1
1 Mary Running 20/10/2017 0
1 Mary Stopped 20/10/2017 0
1 Mary Running 20/10/2017 0
2 John Stopped 20/10/2017 1
2 John Running 20/10/2017 0
3 Lucy Stopped 20/10/2017 1
3 Lucy Running 20/10/2017 0
3 Lucy Stopped 20/10/2017 0
没有您的查询,我们无法给出具体的答案,所以这是一个一般的答案。 使用 ROW_NUMBER()
为每一行指定一个数字,然后使用 CASE
语句将除第 1 行以外的所有行替换为 0:
SELECT ID, Name, Event,
CASE WHEN RN = 1 THEN 1 ELSE 0 END [Row]
FROM (SELECT ID, Name, Event,
ROW_NUMBER() over (PARTITION BY ID ORDER BY ID) RN --Add Event to the
--partition if needed
FROM YourTable
) A
正如@JNevill提到的,您可以通过将 window 函数直接添加到 CASE
语句来清理此问题以避免子查询:
SELECT ID, Name, Event,
CASE WHEN ROW_NUMBER() over (PARTITION BY ID ORDER BY ID) = 1
THEN 1
ELSE 0
END [Row]
FROM YourTable
若要与 ID 和日期区分开来,可以使用以下代码:
SELECT ID,RIGHT([Event],10) FROM Some_table GROUP BY ID,RIGHT([Event],10)
或
SELECT distinct ID,RIGHT([Event],10) FROM Event_table
例如,如果添加行:
(1,"玛丽","运行 21/10/2017")
您将获得 ID 1 的两行,一行显示 20/10/2017,另一行显示 21/10/2017。
对带有 1 和 0 的列有什么特别需要吗?