我们可以使用具有相同fieldname的group by和where条件

I have a requirement like have to pull all records in the date range the user selected, selecting all employees who started from 15-Jan-2011 to 20-Aug-2011 and group by date.

我有一个要求，就是必须在用户选择的日期范围内提取所有记录，选择从2011年1月15日到2011年8月20日开始的所有员工以及按日期分组。

How should I write SQL query for this:

我应该如何为此编写SQL查询：

  SELECT *
    FROM employees 
   WHERE startdate >= '15-jan-2011' 
     AND startdate <= '20-aug-2011'
GROUP BY startdate

2 个解决方案

#1

Absolutely. It will result in filtering the records on your date range and then grouping it by each day where there is data.

绝对。它将导致过滤日期范围内的记录，然后在有数据的每一天对其进行分组。

It should be noted that you will only be able to select the startdate and then whatever aggregates you're calculating. Otherwise, it should work perfectly fine.

应该注意的是，您只能选择startdate，然后选择您正在计算的聚合。否则，它应该工作得很好。

For example, this query will give you a count of employees for each startdate:

例如，此查询将为您提供每个startdate的员工计数：

SELECT startdate, count(*)
FROM employees 
WHERE startdate >= '15-jan-2011' 
      AND startdate <= '20-aug-2011'
GROUP BY startdate

#2

You can, but the "GROUP BY" clause is used for grouping together sets of rows, so it does not make sense for your question (or anything that involves a "SELECT *").

您可以，但“GROUP BY”子句用于将行集合分组，因此对您的问题（或任何涉及“SELECT *”的内容）没有意义。

To answer your question though:

尽管如此回答你的问题：

SELECT DATEADD(dd, 0, DATEDIFF(dd,0,StartDate)) AS 'StartDate', <other fields>
FROM   Employees
WHERE  StartDate BETWEEN '15-Jan-2011' AND '20-Jan-2011'
ORDER BY StartDate

Note: the stripping of the time from the date came from here

注意：从这里开始剥离时间

#1