我正在通过观看YouTube来学习SQL,我正在努力实现以下目标。我有一个包含三列的表格
Inspection Guid, SensorData Guid, DateTime
我想获取一个检查指南、传感器数据指南、日期时间表,但没有重复的检查指南。
从(为了方便起见,我缩短了 Guids)
6E814118 DA875776 2016-05-25 14:59:17.523
6E814118 BAEB255D 2016-05-25 14:59:17.523
4B7D4E90 BB717852 2016-05-25 14:58:46.260
06BB9435 2321EDD6 2016-05-25 14:58:16.870
06BB9435 D4168F8B 2016-05-25 14:58:16.870
8D9A8502 3EED26BE 2016-05-25 14:57:47.400
5C9AA885 77D342DD 2016-05-25 14:57:15.217
5C9AA885 43DEF213 2016-05-25 14:57:15.217
自
6E814118 DA875776 2016-05-25 14:59:17.523
4B7D4E90 BB717852 2016-05-25 14:58:46.260
06BB9435 2321EDD6 2016-05-25 14:58:16.870
8D9A8502 3EED26BE 2016-05-25 14:57:47.400
5C9AA885 77D342DD 2016-05-25 14:57:15.217
按日期时间排序。
非常感谢您的帮助。
ROW_NUMBER()
根据DATE
选择第一个 GUID,然后选择要显示ORDER BY
。
SELECT *
FROM (
SELECT [Inspection Guid],
[SensorData Guid],
[DateTime],
ROW_NUMBER() OVER (PARTITION BY [Inspection Guid]
ORDER BY [DateTime] DESC) rn
FROM YourTable
) T
WHERE rn = 1
ORDER BY [DateTime] DESC
WITH duplicates AS
(
SELECT
[Inspection Guid],
[SensorData Guid],
[DateTime],
ROW_NUMBER() OVER
(
PARTITION BY [Inspection Guid], [DateTime]
ORDER BY [SensorData Guid]
) AS rn
FROM YourTable
)
SELECT *
FROM duplicates
WHERE rn = 1
分区 2 字段[Inspection Guid]
,[DateTime]
确保每个日期时间的分区是唯一的。
你想要的实际上不可能按照要求实现。
以这 2 条记录为例:
06BB9435 2321EDD6 2016-05-25 14:58:16.870
06BB9435 D4168F8B 2016-05-25 14:58:16.870
如果您只想显示一次06BB9435
,您将如何决定显示哪些其他数据?您必须决定为每个重复的 GUID 显示哪个日期。如果你想要最近的日期,以及按字母顺序排列的最高传感器数据,那么你可以这样做:
SELECT [Inspection Guid], MAX([SensorData Guid]), MAX([DateTime])
FROM YourTable
GROUP BY [Inspection Guid]
ORDER BY MAX([DateTime]) DESC