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

Question

我是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

我正在努力从错误中学习并提高自己，所以请保持温柔。

Answer 1

您需要在舍入到分钟之前将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

Answer 2

您需要的是如下所示的查询

查看工作演示

; with newB as 
(
select id,Aid,DATEADD(MINUTE, DATEDIFF(MINUTE, 0, Timestamp)- datepart(tz,Timestamp), 0) as TimestampMinute
from B
)
select TimestampMinute, count(distinct Aid) from A join newB B
on A.id=B.Aid
group by TimestampMinute