gpt4 book ai didi

sql-server - SQL查询获取每分钟的最后一条记录

转载 作者:行者123 更新时间:2023-12-03 16:33:10 28 4
gpt4 key购买 nike

我有一个表格,每秒更新一些值。我想检索每分钟的最后一条记录。

我试过这段代码,但它返回了所有记录。

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

最佳答案

您可以使用以下查询:

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 字段中提取小时和分钟。

关于sql-server - SQL查询获取每分钟的最后一条记录,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/36004190/

28 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com