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

你需要转换 datetimeoffset 到 datetime2 在四舍五入到分钟之前,因此代码:

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

第三个论点( 一 是否需要考虑时区差异并进行计算。如果是的话 零 它将省略该部分并使用 +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

您需要的是如下查询

See working demo

; 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