如何在 SQL 中选择按用户分组的当天的第一个条目

Question

我环顾四周，但无法完全掌握从 MS Access 2000 表中提取数据所需的 SQL 查询问题的完整答案。

下面是表 [Time Sub] 的示例：

**CLIENT_ID, DATE_ENTERED, CODE, MINUTES**  
11111, 5/12/2008 3:50:52 PM, M, 38  
11111, 5/12/2008 2:55:50 PM, M, 2  
11714, 5/13/2008 1:15:32 PM, M, 28  
11111, 5/13/2008 6:15:12 PM, W, 11  
11112, 5/12/2008 2:50:52 PM, M, 89  
11112, 5/12/2008 5:10:52 PM, M, 9  
91112, 5/14/2008 1:10:52 PM, L, 96
11112, 5/12/2008 5:11:52 PM, M, 12

我需要为每个客户端选择每天的第一个条目，该条目不是代码 L 或 W。

我知道这可以在 SQL 语句中完成，但我就是做不到弄清楚如何。我可以接近，但永远无法想出正确的输出。

任何帮助表示赞赏。

谢谢， 麦克风

Answer 1

Select ...
From Table As T
Where Date_Entered = (
                        Select Min(T2.Date_Entered)
                        From Table As T2
                        Where T2.Client_Id = T.Client_Id
                            And DateDiff("d", 0, T2.Date_Entered) = DateDiff("d", 0, T.Date_Entered)
                            And T2.Code Not In("L","W")
                        )

Answer 2

试一试。我想这就是你所要求的。

SELECT
  [Time Sub].CLIENT_ID,
  [Time Sub].CODE,
  MinDate.TheDay
FROM
  [Time Sub] INNER JOIN
    (
    SELECT
      [Time Sub].CLIENT_ID,
      MIN([Time Sub].DATE_ENTERED) as MinimumDate,
      Format([Time Sub].DATE_ENTERED, "YYYY-MM-DD") AS TheDay
    FROM
      [Time Sub]
    GROUP BY
      [Time Sub].CLIENT_ID,
      Format([Time Sub].DATE_ENTERED, "YYYY-MM-DD")
    ) AS MinDate
  ON MinDate.MinimumDate = [Time Sub].DATE_ENTERED AND MinDate.CLIENT_ID = [Time Sub].CLIENT_ID
WHERE
  [Time Sub].CODE NOT IN ("L", "W")

Answer 3

我从未使用过 MS Access，因此您可能需要纠正 SQL 差异：

select * from [Time Sub] A 
where
  DATE_ENTERED = (
    select top 1 DATE_ENTERED
    from [Time Sub] B
    where
      Int(A.DATE_ENTERED) = Int(B.DATE_ENTERED)
      and A.CLIENT_ID = B.CLIENT_ID
    order by
      DATE_ENTERED        
    )
  and (CODE <> 'L')
  and (CODE <> 'W')

Answer 4

我在使用了其他人已经做过的事情后发现了这一点。这是我想出的：

SELECT [Time sub].CLIENT_ID, 
       [Time sub].Code, 
       [Time sub].Minutes, 
       FirstDay.MinEntry

FROM [Time sub] 
     INNER JOIN [
        SELECT
         [Time sub].CLIENT_ID,
         MIN([Time sub].[Date_Entered]) AS MinEntry

        FROM
         [Time sub]

        WHERE
          [Time sub].Code NOT IN ("T", "L")

       GROUP BY
          [Time sub].CLIENT_ID,
          DateValue([Time sub].[Date_Entered])

 ].AS FirstDay 

 ON FirstDay.MinEntry = [Time sub].[Date_Entered]

 ORDER BY FirstDay.MinEntry, [Time sub].CLIENT_ID;