SQL 选择列表中的列无效，因为它不包含在聚合函数或 GROUP BY 子句中

Column invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

提问by user1557020

We have a table which will capture the swipe record of each employee. I am trying to write a query to fetch the list of distinct employee record by the first swipe for today.

我们有一个表格，它将捕获每个employee. 我正在尝试编写一个查询，通过今天的第一次滑动来获取不同员工记录的列表。

We are saving the swipe date info in datetimecolumn. Here is my query its throwing exception.

我们正在datetime列中保存滑动日期信息。这是我的查询其抛出异常。

 select distinct 
    [employee number], [Employee First Name]
    ,[Employee Last Name]
    ,min([DateTime])
    ,[Card Number]
    ,[Reader Name]
    ,[Status]
    ,[Location] 
from 
    [Interface].[dbo].[VwEmpSwipeDetail] 
group by  
    [employee number] 
where 
    [datetime] = CURDATE();

Getting error:

获取错误：

Column 'Interface.dbo.VwEmpSwipeDetail.Employee First Name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

列“Interface.dbo.VwEmpSwipeDetail.Employee First Name”在选择列表中无效，因为它不包含在聚合函数或 GROUP BY 子句中。

Any help please?

请问有什么帮助吗？

Thanks in advance.

提前致谢。

回答by Tanner

The error says it all:

错误说明了一切：

...Employee First Name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

...Employee First Name' 在选择列表中无效，因为它不包含在聚合函数或 GROUP BY 子句中

Saying that, there are other columns that need attention too.

话虽如此，还有其他栏目也需要注意。

Either reduce the columns returned to only those needed or include the columns in your GROUP BYclause or add aggregate functions (MIN/MAX). Also, your WHEREclause should be placed before the GROUP BY.

将返回的列减少到仅需要的列，或者将这些列包含在您的GROUP BY子句中，或者添加聚合函数 (MIN/MAX)。此外，您的WHERE子句应放在GROUP BY.

Try:

尝试：

select   distinct [employee number]
      ,[Employee First Name]
      ,[Employee Last Name]
      ,min([DateTime])
      ,[Card Number]
      ,min([Reader Name])
from [Interface].[dbo].[VwEmpSwipeDetail] 
where CAST([datetime] AS DATE)=CAST(GETDATE() AS DATE)
group by  [employee number], [Employee First Name], [Employee Last Name], [Card Number]

I've removed statusand locationas this is likely to return non-distinct values. In order to return this data, you may need a subquery (or CTE) that first gets the unique IDs of the SwipeDetailstable, and from this list you can join on to the other data, something like:

我已经删除了status，location因为这很可能会返回非不同的值。为了返回此数据，您可能需要一个子查询（或 CTE）来首先获取SwipeDetails表的唯一 ID ，然后您可以从该列表中加入其他数据，例如：

SELECT [employee number],[Employee First Name],[Employee Last Name].. -- other columns
FROM [YOUR_TABLE]
WHERE SwipeDetailID IN (SELECT MIN(SwipeDetailsId) as SwipeId
                        FROM SwipeDetailTable
                        WHERE CAST([datetime] AS DATE)=CAST(GETDATE() AS DATE)
                        GROUP BY [employee number])

回答by Navneet

Please Try Below Query :

请尝试以下查询：

select   distinct [employee number],[Employee First Name]
          ,[Employee Last Name]
          ,min([DateTime])
          ,[Card Number]
          ,[Reader Name]
          ,[Status]
          ,[Location] from [Interface].[dbo].[VwEmpSwipeDetail] group by [employee number],[Employee First Name]
          ,[Employee Last Name]
          ,[Card Number]
          ,[Reader Name]
          ,[Status]
          ,[Location] having [datetime]=GetDate();

回答by Glenn

First find the first timestamp for each employee on the given day (CURDATE), then join back to the main table to get all the details:

首先找到给定日期（CURDATE）每个员工的第一个时间戳，然后连接回主表以获取所有详细信息：

WITH x AS (
    SELECT [employee number], MIN([datetime] AS minDate
      FROM [Interface].[dbo].[VwEmpSwipeDetail]
      WHERE CAST([datetime] AS DATE) = CURDATE()
      GROUP BY [employee number]
)
select [employee number]
      ,[Employee First Name]
      ,[Employee Last Name]
      ,[DateTime]
      ,[Card Number]
      ,[Reader Name]
      ,[Status]
      ,[Location]
  from [Interface].[dbo].[VwEmpSwipeDetail] y
  JOIN x ON (x.[employee number] = y.[employee number] AND x.[minDate] =Y.[datetime]

回答by Giles

This should not be marked as mysql as this would not happen in mysql.

这不应该被标记为 mysql，因为这不会发生在 mysql 中。

sql-server does not know which of the grouped [Employee First Name] values to return so you need to add an aggregate (even if you only actually expect one result). min/max will both work in that case. The same would apply to all the other rows where they are not in the GROUP BY or have an aggregate function (EG min) around them.

sql-server 不知道要返回哪个分组的 [Employee First Name] 值，因此您需要添加一个聚合（即使您实际上只期望一个结果）。在这种情况下，最小/最大都可以工作。这同样适用于所有其他不在 GROUP BY 中或在它们周围有聚合函数 (EG min) 的行。