获取具有相同的创建日期datetime至分钟的行 [英] Get rows that has same created date datetime till minutes

问题描述

我有一张表，其中的数据如下：

I have a table with data like:

   id  question_id Created_dt           secondary_question_id
  --------------------------------------------------------
   1     71        2016-03-12 16:33:00        123
   2     71        2016-03-12 16:33:09        124
   3     71        2016-03-12 16:33:12        125
   4     72        2018-05-15 16:39:00        156
   5     72        2018-05-15 16:39:41        135
   6     73        2018-05-15 16:39:00        129

现在，我想获取所有具有相同created_dt的记录，直到几分钟。我在下面尝试过，但没有得到适当的结果。

Now I would like to get all the records that has same created_dt till minutes. I tried below but not getting appropriate results.

  SELECT question_id, COUNT(Created_dt)
  FROM  TABLE
  GROUP BY Created_dt, question_id
  ORDER BY Created_dt

推荐答案

如果我了解您的要求，则可以忽略此查询的秒数。因此，转换为 varchar 并截断秒数，然后再转换回 datetime 即可完成此操作。也许不是最好的性能，如果性能是一个问题，您要考虑正确存储 datetime 而不用秒。

If I understand what you are asking you want to ignore the seconds for this query. So converting to a varchar and truncating the seconds off, then converting back to a datetime will accomplish this. Maybe not with the best performance, if performance was an issue you want to consider properly storing the datetime without seconds.

declare @Table table (id int, question_id int, created_dt datetime, secondary_question_id int)

insert into @Table (id, question_id, created_dt, secondary_question_id)
  select 1, 71, '2016-03-12 16:33:00', 123
  union all
  select 2, 71, '2016-03-12 16:33:09', 124
  union all
  select 3, 71, '2016-03-12 16:33:12', 125
  union all
  select 4, 72, '2018-05-15 16:39:00', 156
  union all
  select 5, 72, '2018-05-15 16:39:41', 135
  union all
  select 6, 73, '2018-05-15 16:39:00', 129

--select *, convert(datetime,convert(varchar(17),created_dt,113))
--from @Table

SELECT question_id, COUNT(*)
FROM (
  select question_id, convert(datetime,convert(varchar(17),created_dt,113)) created_dt_new
  from @TABLE
) X
GROUP BY created_dt_new, question_id
ORDER BY created_dt_new

这篇关于获取具有相同的创建日期datetime至分钟的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！