获取每位员工每日首次登录与末次登出记录

Hey there! Based on your table structure and desired output, you can use a GROUP BY query with aggregate functions to pull the earliest (first login) and latest (last logout) times for each employee on each date.

Step-by-Step SQL Query #

First, you need to group your data by Personel_ID and the date part of your DateTime column. Then use MIN() to get the first login time and MAX() to get the last logout time.

Here's the query (note: date extraction functions vary by database, I'll cover common ones):

For MySQL/MariaDB:

SELECT
  MIN(Id) AS Id, -- Picks the earliest Id for the group, matching your sample output
  Personel_ID,
  DATE(DateTime) AS Date,
  TIME(MIN(DateTime)) AS `Login Time`,
  TIME(MAX(DateTime)) AS `Logout Time`
FROM your_table_name
GROUP BY Personel_ID, DATE(DateTime)
ORDER BY Personel_ID, Date;

For SQL Server:

SELECT
  MIN(Id) AS Id,
  Personel_ID,
  CAST(DateTime AS DATE) AS Date,
  CONVERT(TIME, MIN(DateTime)) AS [Login Time],
  CONVERT(TIME, MAX(DateTime)) AS [Logout Time]
FROM your_table_name
GROUP BY Personel_ID, CAST(DateTime AS DATE)
ORDER BY Personel_ID, CAST(DateTime AS DATE);

For Oracle:

SELECT
  MIN(Id) AS Id,
  Personel_ID,
  TRUNC(DateTime) AS Date,
  TO_CHAR(MIN(DateTime), 'HH24:MI:SS') AS "Login Time",
  TO_CHAR(MAX(DateTime), 'HH24:MI:SS') AS "Logout Time"
FROM your_table_name
GROUP BY Personel_ID, TRUNC(DateTime)
ORDER BY Personel_ID, TRUNC(DateTime);

What This Does: #

• GROUP BY Personel_ID, [date part]: Groups all records by each employee and each individual date.
• MIN(DateTime): Grabs the earliest timestamp of the day (first login).
• MAX(DateTime): Grabs the latest timestamp of the day (last logout).
• MIN(Id): Ensures we get the smallest Id for each group, which aligns with your sample output's first Id per row.

Sample Output (Matching Your Desired Format) #

Running this query on your provided data will return:

Just replace your_table_name with the actual name of your table, and adjust the date/time functions if you're using a different database system.

内容的提问来源于stack exchange，提问作者Mert Ali