SQL查询获取每分钟的最后一条记录
问题描述:
我有一个每秒更新一些值的表。我想检索每分钟的最后一个记录。SQL查询获取每分钟的最后一条记录
我试过这段代码,但它是返回所有记录。
SELECT
t0.brandID, t0.brandName, t0.cdt, t0.udt, t0.brandstatus
FROM
brands t0
WHERE
t0.cdt BETWEEN '2013-11-15' and '2014-11-15'
GROUP BY
t0.brandID, t0.brandName, t0.cdt, t0.udt, t0.brandstatus,(datepart(minute, t0.cdt)/1)
我的表结构是:
ID brandname cdt udt brandstatus addedby
1 khasim 2013-11-01 19:14:18.120 2013-11-15 19:14:18.123 1 1
2 khasim 2013-11-01 19:14:18.121 2013-11-15 19:14:18.123 1 1
3 khasim 2013-11-01 19:14:18.122 2013-11-15 19:14:18.123 1 1
4 khasim 2013-11-01 19:14:18.123 2013-11-15 19:14:18.123 1 1
5 khasim 2013-11-02 19:17:57.700 2013-11-15 19:17:57.700 1 2
6 tanveer 2013-11-03 19:18:05.947 2013-11-15 19:18:05.947 1 2
7 abcdef 2013-11-04 20:50:06.783 2013-11-15 20:50:06.787 1 4
预期的结果是:
ID brandname cdt udt brandstatus addedby
4 khasim 2013-11-01 19:14:18.123 2013-11-15 19:14:18.123 1 1
5 khasim 2013-11-02 19:17:57.700 2013-11-15 19:17:57.700 1 2
6 tanveer 2013-11-03 19:18:05.947 2013-11-15 19:18:05.947 1 2
7 abcdef 2013-11-04 20:50:06.783 2013-11-15 20:50:06.787 1 4
答
使用ROW_NUMBER
添加编号方式加上PARTITION BY
:
WITH Cte AS(
SELECT *,
rn = ROW_NUMBER() OVER(
PARTITION BY brandname, DATEADD(MINUTE, DATEDIFF(MINUTE, 0, cdt), 0)
ORDER BY cdt DESC
)
FROM tbl
)
SELECT
ID, brandname, cdt, udt, brandstatus, addedby
FROM Cte
WHERE rn = 1
ORDER BY Id
氏S:
DATEADD(MINUTE, DATEDIFF(MINUTE, 0, cdt), 0)
回合下来的cdt
到其最接近的分钟,即2013-11-01 19:14:18.123
到2013-11-01 19:14:00.000
。
答
您可以使用下面的查询:
SELECT ID, brandname, cdt, udt, brandstatus, addedby
FROM (
SELECT ID, brandname, cdt, udt, brandstatus, addedby,
ROW_NUMBER() OVER (PARTITION BY brandname,
CAST(udt AS DATE),
CONVERT(VARCHAR(5), udt, 114)
ORDER BY udt DESC) AS rn
FROM brands) AS t
WHERE t.rn = 1
这一部分:
CONVERT(VARCHAR(5), udt, 114)
是为了从udt
现场提取小时和分钟使用。
答
试试下面的查询
SELECT t0.brandID, t0.brandName, t0.cdt, t0.udt, t0.brandstatus FROM brands t0 WHERE t0.cdt BETWEEN '2013-11-15' and '2014-11-15'
and t0.cdt in (select MAX(cdt) from brands group by (CONVERT(CHAR(16),cdt,120)));
'DATEPART(MINUTE,CDT)'也将获得细部 –
呀,肯定分钟的一部分。但是这样会扰乱分区。我想我已经写得很差。 –
你的解决方案是我正在寻找的,但是你的意思是搞乱了分区,我把分区改为cdt(日期时间),并检查没有任何问题。我应该在那里指定“之间”日期 – Tan