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

Question

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

我们将滑动日期信息保存在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();

出现错误:

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.

请问有什么帮助吗？

提前致谢。

Answer 1

错误说明了一切:

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

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

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

尝试:

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]

我已删除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])