SQL Server范围计数

问题描述：

我有一个SQL Server表，其中一列代表时间，而另一列代表ID，例如：

I have SQL Server table in which one of the column represents time, while another represents an ID, like this:

    Datum                     SomeID 
2017-01-01 07:44:57.840         1   
2017-01-02 07:45:10.093         2   
2017-01-02 07:45:21.557         3    
2017-01-03 09:07:21.253         2  
2017-01-05 09:07:42.680         1 
2017-01-06 09:07:49.007         5

我正在寻找将时间单位作为输入的SQL查询或存储过程（例如'YY'，'MM'，' DD， HH）并返回每个时间间隔具有唯一ID计数的表。如果在该时间间隔内未找到任何记录，则计数应为零。

I am looking for an SQL Query or Stored Procedure which takes the time unit as an input (e.g. 'YY', 'MM', 'DD', 'HH') and returns a table with the unique ID count for each time interval. If no records are found for that interval, the count should be zero.

在这种情况下，假设以 DD作为时间单位，结果应为：

In this case, assuming 'DD' as time unit, the result should be:

Time interval      Count
2017-01-01           1
2017-01-02           2
2017-01-03           1
2017-01-04           0
2017-01-05           1
2017-01-06           1

答

最简单的方法可能是动态SQL：

Probably the simplest method is dynamic SQL:

declare @timeunit nvarchar(255);
declare @sql nvarchar(max);

set timeunit = N'hh';

set @sql = '
select dateadd(@timeunit, datediff(@timeunit, 0, datum), 0) as dt, count(*)
from t
group by dateadd(@timeunit, datediff(@timeunit, 0, datum), 0) 
';

set @sql = replace(@sql, '@timeunit', @timeunit);

exec sp_executesql @sql;

我对将用户输入放入这样的查询并不感到兴奋。不过，您可以随时检查它的有效值：

I am not thrilled with putting user input into a query like this. You can readily check it for valid values, though:

(case when @timeunit in ('year', 'yy', 'month', 'mm',  . . .) then . . .
 end)

SQL Server范围计数

相关推荐