我有一个每秒更新一些值的表。我想检索每分钟的最后一条记录。
我已经尝试过这段代码，但它返回所有记录。
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 field.
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)
