22,209
社区成员
发帖
与我相关
我的任务
分享
select val,times from testdb
where cast(DATEPART(MINUTE,times) as varchar(2)) in ('56','57','58','59','00','11','12','13','14','15','26','27','28','29','30','41','42','43','44','45')
DROP TABLE #tab
CREATE TABLE #tab(
dt DATETIME
)
INSERT INTO #tab
SELECT '2019-06-05 13:00:00' UNION ALL
SELECT '2019-06-05 12:59:00' UNION ALL
SELECT '2019-06-05 12:58:00' UNION ALL
SELECT '2019-06-05 12:57:00' UNION ALL
SELECT '2019-06-05 12:56:00' UNION ALL
SELECT '2019-06-05 12:16:00' UNION ALL
SELECT '2019-06-05 12:15:00' UNION ALL
SELECT '2019-06-05 12:14:00' UNION ALL
SELECT '2019-06-05 12:13:00' UNION ALL
SELECT '2019-06-05 12:29:00' UNION ALL
SELECT '2019-06-05 12:28:00' UNION ALL
SELECT '2019-06-05 12:43:00'
--测试数据结束
;WITH cte AS (
SELECT DATEADD(minute, -1, dt) AS newdt,
DATEPART(minute, DATEADD(minute, -1, dt)) / 15 AS [quarter],
DATEPART(minute, DATEADD(minute, -1, dt))%15 AS model,
dt
FROM #tab
),cte2 AS (
SELECT *,
ROW_NUMBER() OVER(
PARTITION BY DATEPART(YEAR, newdt),
DATEPART(MONTH, newdt),
DATEPART(DAY, newdt),
DATEPART(hour, newdt),
[quarter] ORDER BY model DESC
) AS rn
FROM cte
)
SELECT dt
FROM cte2
WHERE rn = 1
--这个结果应该是你要的对吧
dt
-----------------------
2019-06-05 12:15:00.000
2019-06-05 12:29:00.000
2019-06-05 12:43:00.000
2019-06-05 13:00:00.000