带有group by的SQL查询，用于多个日期范围

I need to formulate a t-sql query and so far I have been unable to do so. The table that I need to query is called Operations with two columns ,an FK OperationTypeID and an OperationDate. The query needs to return a result which comprises of the count of operation type id during the range specified.

我需要制定一个t-sql查询，到目前为止我一直无法这样做。我需要查询的表称为具有两列的操作，即FK OperationTypeID和OperationDate。查询需要返回一个结果，该结果包括在指定范围内的操作类型id的计数。

Through the application interface the user can specify multiple operationtype Ids as well as their individual date ranges so for instance, the operationtype id 'A' can be looked for in the range 22/04/2010 to 22/04/2012 and operationtype Id 'B' can be searched in 15/10/2012 to 15/11/2013 and so on for other operation type ids. Now I need to return a count for each operationtype id during each of the range specified for individual operation type Ids.

通过应用程序界面，用户可以指定多个操作类型ID以及它们各自的日期范围，例如，可以在22/04/2010到22/04/2012范围内查找操作类型ID“A”和操作类型Id'可以在15/10/2012至15/11/2013中搜索B'，以此类推其他操作类型ID。现在，我需要在为各个操作类型ID指定的每个范围内返回每个operationtype id的计数。

What is the most efficient way to achieve this in a single t-sql query considering the performance issues ... a rough layout presented below, i am not very good at formatting so i hope it will still give an idea.

考虑到性能问题，在单个t-sql查询中实现这一目标的最有效方法是什么...下面给出的粗略布局，我不太擅长格式化，所以我希望它仍然会给出一个想法。

+---------------+----------+----------+-----+
|OperationTypeID|Min date  |Max Date  |Count|
+---------------+----------+----------+-----+
|A              |22/04/2010|22/04/2012|899  |
+---------------+----------+----------+-----+
|B              |15/10/2012|15/11/2013|789  |
+---------------+----------+----------+-----+

.... and so on

.... 等等

Would appreciate if anyone can help. The query needs to return a count for each operationtype id based on the min/max date range specified by the user. The Min/Max functions available in sql server probably don't apply here. One possible approach that I have thought of so far makes use of the Union All approach, where I formulate a single query for a single operation type id based on the date range and then do a UNION All, any performance impacts?

如果有人能帮忙，我将不胜感激查询需要根据用户指定的最小/最大日期范围返回每个operationtype id的计数。 sql server中可用的最小/最大函数可能不适用于此处。到目前为止，我想到的一种可能的方法是使用Union All方法，我根据日期范围为单个操作类型id制定单个查询，然后执行UNION All，任何性能影响？

1 个解决方案

#1

You will need to store the search criteria somewhere. The best place, would probably be a temporary table with the following columns:

您需要在某处存储搜索条件。最好的地方，可能是一个包含以下列的临时表：

CREATE TABLE #SearchCriteria (
    OperationTypeId VARCHAR(1)
    MinDate DATETIME
    MaxDate DATETIME
)

Now, once you have populated this table, a simple query like this, should give you what you want:

现在，一旦你填充了这个表，像这样的简单查询，应该给你你想要的：

SELECT OperationTypeId, 
    MinDate, 
    MaxDate,
    (SELECT COUNT(*) FROM Operations 
      WHERE OperationDate BETWEEN SC.MinDate AND SC.MaxDate 
        AND OperationTypeId = SC.OperationTypeId) AS [Count]
FROM
    #SearchCriteria SC

If you must have everything in a single query (without using a temporary table), do something like this:

如果必须在一个查询中包含所有内容（不使用临时表），请执行以下操作：

SELECT OperationTypeId, 
    MinDate, 
    MaxDate,
    (SELECT COUNT(*) FROM Operations 
      WHERE OperationDate BETWEEN SC.MinDate AND SC.MaxDate 
        AND OperationTypeId = SC.OperationTypeId) AS [Count]
FROM
    (VALUES ('A', '22/04/2010', '22/04/2012')
           ,('B', '15/10/2012', '15/11/2013')
         /* ... etc ... */
    ) SC(OperationTypeId, MinDate, MaxDate)

#1