时间戳

首页 > 解决方案 > TSQL 从表 A 中选择表 B 中每分钟时间戳至少有一条记录在表 B 中的行数

sql-server - TSQL 从表 A 中选择表 B 中每分钟时间戳至少有一条记录在表 B 中的行数

问题描述

我是 SQL 查询的新手，我有一种情况，我不知道如何解决它。我有两张桌子，例如：

Table B
Id AId  Timestamp (datetimeoffset(7) column)
1  1    2017-08-31 08:25:32.7000000 -07:00
2  2    2017-08-31 10:25:32.5000000 -05:00
3  2    2017-08-31 10:25:32.9000000 -05:00
4  8    2017-08-31 10:25:32.5000000 -02:00
5  1    2017-05-30 18:31:43.8000000 +03:00
6  7    2017-05-29 18:31:43.8000000 +03:00

Table A
Id   Name
1    AA
2    AB
7    AC
8    AD

我试过的是这样的：

select DATEADD(MINUTE, DATEDIFF(MINUTE, 0, p.Timestamp), 0) as TimestampMinute, count(1) Count
from A a
cross apply (select top 1 b.Timestamp from B b where a.Id=b.AId) p
group by DATEADD(MINUTE, DATEDIFF(MINUTE, 0, p.Timestampl), 0)
order by DATEADD(MINUTE, DATEDIFF(MINUTE, 0, p.Timestampl), 0)

但我的问题是它应该添加偏移量，然后在几分钟内四舍五入并计算表A中在那一分钟至少有一条记录的行数，但它只是四舍五入，忽略偏移量，我认为这就是为什么它算坏的问题。因此，对于日期，2017-08-31 15:25我应该有来自表 A 的 2 行（Id 1 和 2）。表 A 中的 Id 1 对应于表 B 的第 1 行，而 Id 2 对应于第 2 行和第 3 行（应用不同的）。

预期结果：

2017-08-31 15:25  2
2017-08-31 12:25  1
2017-05-30 15:31  1
2017-05-29 15:31  1

我正在努力从错误中吸取教训并提高自己，所以请温柔一点。

标签： sql-servertsql

解决方案

您需要在四舍五入到分钟之前将其转换为，因此代码datetimeoffset：datetime2

convert(datetime2, b.timestamp, 1) -- timestamp is your column from table b

第三个参数（1用于考虑时区差异并计算它。如果它是0它将省略该部分并使用 转换它+00.00）。

解决方案：

select 
  DATEADD(MINUTE, DATEDIFF(MINUTE, 0, convert(datetime2, b.timestamp, 1)), 0) as TimestampMinute , 
  count(distinct b.aid) as cnt
from b
inner join a on b.aid = a.id -- new part
group by DATEADD(MINUTE, DATEDIFF(MINUTE, 0, convert(datetime2, b.timestamp, 1)), 0)
order by DATEADD(MINUTE, DATEDIFF(MINUTE, 0, convert(datetime2, b.timestamp, 1)), 0);

输出：

TimestampMinute            cnt
2017-05-29 15:31:00.000     1
2017-05-30 15:31:00.000     1
2017-08-31 12:25:00.000     1
2017-08-31 15:25:00.000     2

推荐阅读