代码之家 › 专栏 › 技术社区 › LAffair

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

tsql sql-server

LAffair · 技术社区 · 8 年前

我是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 我应该有2行从表A(ID 1和2)。表A中的ID 1与表B的行1对应,ID 2与行2和3对应(应用distinct)。

预期结果:

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

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

2 回复 | 直到 8 年前

Kamil Gosciminski 8 年前

你需要转换 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

DhruvJoshi 8 年前

您需要的是如下查询

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

推荐文章