返回行取决于它们之间的时间戳
问题在于我想从表中获取每条记录，但基于DateTime。
我有表格每30分钟添加每一个子对象的当前状态，如下所示：
| ID子 | ID | 颜色 | 时间戳 |
|---|---|---|---|
| 1 | 7EB43D1D-7274-41C4-35DA-08D727A424E6 | Orange | 20222-06-27 08：00：17.9843893 |
| 2 | A8FDBB08-3747-4B93-BC66-08D7382060CE | 紫色 | 00：17.9843893 |
| 3 | 7EB43D1D-7274-41C4 B08-3747-4B93 | ： | - |
| 2022-06-27 08 | BC66- 08D7382060CE | 蓝色 | ：30：15.7043893 |
| 7EB43D1D | -7274-41C4-41C4-35DA-08D727A424E6 | 橙色 | 09 |
| 2060CE | 2022-06-27 08 | 5 | 2022-06-27 ：00：18.2841893 |
，现在我需要在某个时期获得一个子对象的积分。但是我不想获得所有的全部，因为我可以以太多的积分结束，我只想有时每小时1或每天获得1个（可能会改变）
我已经尝试了 row_number 我每30分钟添加一次点，但由于我需要添加clausure for Subid的位置，然后我可能会以不正确的结果结尾（因为我在同时添加或删除了这些子对象）
SELECT * FROM (
SELECT [Id]
,[SubId]
,[Color]
,[Timestamp]
, ROW_NUMBER() OVER (ORDER BY OccupancyHistoryId) as rownum
FROM [dbo].[Table]) AS t
WHERE t.SubId = '7EB43D1D-7274-41C4-35DA-08D727A424E6' AND t.rownum % 2 = 0
我是否想念一些明显的东西？也许我的方法错了？
预期结果：对于 2022-06-27到 2022-06-28 的EG记录，每个2小时只有1个。
| Id | SubId | Color | Timestamp |
|---|---|---|---|
| 1 | 7EB43D1D-7274-41C4-35DA-08D727A424E6 | orange | 2022-06-27 08:00:17.9843893 |
| 5 | 7EB43D1D-7274-41C4-35DA-08D727A424E6 | yellow | 2022-06-27 10:00:18.2841893 |
| 10 | 7EB43D1D-7274 -41C4-35DA-08D727A424E6 | 橙色 | 2022-06-27 12：00：11.2821893 |
The problem is that I want to get every n'th record from table but based on datetime.
I have table where I add record every 30 minutes with current state for each of my Sub objects, something like below:
| Id | SubId | Color | Timestamp |
|---|---|---|---|
| 1 | 7EB43D1D-7274-41C4-35DA-08D727A424E6 | orange | 2022-06-27 08:00:17.9843893 |
| 2 | A8FDBB08-3747-4B93-BC66-08D7382060CE | purple | 2022-06-27 08:00:17.9843893 |
| 3 | 7EB43D1D-7274-41C4-35DA-08D727A424E6 | red | 2022-06-27 08:30:15.7043893 |
| 4 | A8FDBB08-3747-4B93-BC66-08D7382060CE | blue | 2022-06-27 08:30:15.7043893 |
| 5 | 7EB43D1D-7274-41C4-35DA-08D727A424E6 | yellow | 2022-06-27 09:00:18.2841893 |
| 6 | A8FDBB08-3747-4B93-BC66-08D7382060CE | orange | 2022-06-27 09:00:18.2841893 |
And now I need to get points for one Sub object in certain period. But I dont want to get all entires cause I can end with too many points, I just want to get sometimes 1 per hour or 1 per day (it may change)
I already tried with ROW_NUMBER as I know that I'm adding point every 30 minutes but cause I need add where clausure for SubId then I might end with incorrect result (cause I'm adding or removing those Subobject in meanwhile)
SELECT * FROM (
SELECT [Id]
,[SubId]
,[Color]
,[Timestamp]
, ROW_NUMBER() OVER (ORDER BY OccupancyHistoryId) as rownum
FROM [dbo].[Table]) AS t
WHERE t.SubId = '7EB43D1D-7274-41C4-35DA-08D727A424E6' AND t.rownum % 2 = 0
Am I miss something obviouse? Or maybe my approach is wrong?
Expected result: For e.g records from 2022-06-27 to 2022-06-28 but only 1 per each 2 hours.
| Id | SubId | Color | Timestamp |
|---|---|---|---|
| 1 | 7EB43D1D-7274-41C4-35DA-08D727A424E6 | orange | 2022-06-27 08:00:17.9843893 |
| 5 | 7EB43D1D-7274-41C4-35DA-08D727A424E6 | yellow | 2022-06-27 10:00:18.2841893 |
| 10 | 7EB43D1D-7274-41C4-35DA-08D727A424E6 | orange | 2022-06-27 12:00:11.2821893 |
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
多亏了 @ourmandave的评论，我能够解决这个问题。我没有注意到我可以将
日期>与％一起使用。因此，为了每两个小时只能获得一个条目，我只是简单地编写这样的查询。所以，显然：
Thanks to @ourmandave's comments, I was able to resolve the problem. I didn't notice that I can use
DATEDIFFwith%.So, to get entries only one per two hours, I simply write the query like that. So, obviously: