每天选择列 [英] Select Column for each day

问题描述

我很困惑，这是获得员工（第一次参加）的最佳方法。 （最后一次）他们登录我们的生物识别时钟系统的每一天。这些数据来自生物识别设备RAW文本文件，我刚将其导入我的数据库，现在我需要选择每个员工First IN&每天最后一次..



<前lang =HTML> Emp_Name Emp_Time Emp_Type
Mike 2013-04-01 08:30:48
Mike 2013-04-01 08:35:48
Mike 2013-04-01 17:25:32 Out
Mike 2013-04-01 17:35:32 Out
Mike 2013-04-02 08:35:48在
Mike 2013-04-02 08:35:48在
Mike 2013-04-02 17:45:32 Out
Mike 2013 -04-02 17:55:32 Out
Josh 2013-04-01 08:30:48
Josh 2013-04-01 08:35:48
Josh 2013-04 -01 17:25:32 Out
Josh 2013-04-01 17:35:32 Out
Josh 2013-04-02 08:35:48
Josh 2013-04-02 08:35:48
Josh 2013-04-02 17:45:32 Out
Josh 2013-04-02 17:55:32 Out

我试过这段代码

选择不同
 CE.Emp_Name，
（从Raw_Record中选择min（Emp_Time）作为MINCE，其中MINCE.Emp_Type = ' 在'和MINCE.Emp_Name = CE.Emp_Name）作为MinTime，
（从中选择max（Emp_Time） Raw_Record作为MAXCE，其中MAXCE.Emp_Type = '  Out'和MAXCE.Emp_Name = CE.Emp_name）作为MaxTime来自Raw_Record的
作为CE 

但它只返回以下内容：

名称时间超时
 
 Mike 2013-04-01 08:30:48 2013-04-02 17:55:32 
 Josh 2013-04-01 08:30:48 2013-04-02 17:55:32 

我需要生产的是不知怎的，这样：

名称时间超时
 
 Mike 4/1/2013 08:30 ：48 2013-04-01 17:35:32 
 Mike 2013-04-02 08:35:48 2013-04-02 17:55:32 
 Josh 2013-04-01 08:30 ：48 2013-04-01 17:35:32 
 Josh 2013-04-02 08:35:48 2013-04-02 17:55:32 

解决方案

我的第一个回应是使用以下 SELECT Raw_Record 的使用c $ c>声明：

  SELECT  Emp_Name， CONVERT （日期，Emp_Time） AS  Emp_Date，Emp_Time，Emp_Type 
  FROM  Raw_Record 

此查询的结果是：

  Emp_Name   Emp_Date   Emp_Time   Emp_Type  
 Mike 2013-04-01 2013-04-01 08：30：48.000 In 
 Mike 2013-04- 01 2013-04-01 08：35：48.000在
 Mike 2013-04-01 2013-04-01 17：25：32.000 Out 
 Mike 2013-04-01 2013-04-01 17： 35：32.000 Out 
 Mike 2013-04-02 2013-04-02 08：35：48.000 In 
 Mike 2013-04-02 2013-04-02 08：35：48.000 in 
 Mike 2013-04-02 2013-04-02 17：45：32.000 Out 
 Mike 2013-04-02 2013-04-02 17：55：32.000 Out 
 Josh 2013-04-01 2013- 04-01 08：30：48.000在
 Josh 2013-04-01 2013-04-01 08：35：48.000在
 Josh 2013-04-01 2013-04-01 17：25：32.000出
 Josh 2013-04-01 2013 -04-01 17：35：32.000 Out 
 Josh 2013-04-02 2013-04-02 08：35：48.000 In 
 Josh 2013-04-02 2013-04-02 08:35： 48.000英镑
 Josh 2013-04-02 2013-04-02 17：45：32.000 Out 
 Josh 2013-04-02 2013-04-02 17：55：32.000 Out 

这样我就可以在选择中包含唯一日期，并获得每个唯一日期的最大和最小 Emp_Time 。

结果查询将是：

  SELECT   DISTINCT  CE.Emp_Name，
（ SELECT  MIN（Emp_Time）
  FROM （ SELECT  Emp_Name， CONVERT （ Date ，Emp_Time） AS  Emp_Date，Emp_Time，Emp_Type 
  FROM  Raw_Record） AS  MIN CE 
  WHERE  MINCE.Emp_Type = '  In '  AND  MINCE.Emp_Date = CE.Emp_Date  AND  MINCE.Emp_Name = CE.Emp_Name）  AS  MinTime，
（ SELECT  MAX（Emp_Time）
  FROM （ SELECT  Emp_Name， CONVERT （日期，Emp_Time） AS  Emp_Date，Emp_Time，Emp_Type 
  FROM  Raw_Record） AS  MINCE 
  WHERE  MINCE.Emp_Type = '  Out'  AND  MINCE.Emp_Date = CE.Emp_Date  AND  MINCE.Emp_Name = CE.Emp_Name） AS  MaxTime 
  FROM （ SELECT  Emp_Name， CONVERT （日期，Emp_Time） AS  Emp_Date，Emp_Time，Emp_Type 
  FROM  Raw_Record） AS  CE 

这给出了您要查找的结果，但它不是一个非常有效和优雅的查询。



所以我开始寻找更好的方法来解决你的问题，这就是我想出来的：

  SELECT   DISTINCT  Raw_Record.Emp_Name，Min_In.Emp_Time  AS  MinTime，Max_Out.Emp_Time  AS  MaxTime 
  FROM  Raw_Recor d 
 
  JOIN （ SELECT  Emp_Name，Emp_Type， CONVERT （日期，Emp_Time） AS  Emp_Date，Min（Emp_Time）< span class =code-keyword> AS  Emp_Time 
  FROM  Raw_Record 
  WHERE  Emp_Type = ' 在' 
  GROUP   BY  Emp_Name，Emp_Type， CONVERT （日期，Emp_Time）） AS  Min_In 
  ON  Min_In.Emp_Name = Raw_Record .Emp_Name  AND  Min_In.Emp_Date =  CONVERT （日期，Raw_Recor d.Emp_Time）
 
  JOIN （ SELECT  Emp_Name，Emp_Type， CONVERT （日期，Emp_Time） AS  Emp_Date，Max（ Emp_Time） AS  Emp_Time 
  FROM  Raw_Record 
  WHERE  Emp_Type = '  Out' 
  GROUP   BY  Emp_Name，Emp_Type， CONVERT （日期，Emp_Time）） AS  Max_Out 
  ON  Max_Out。 Emp_Name = Raw_Record.Emp_Name  AND  Max_Out.Emp_Date =  CONVERT （日期，Raw_Record.Emp_Time）

它将原始表的内容与基于 Emp_Name的两个子查询的结果相结合和唯一日期（ Emp_Date ）。第一个子查询返回 In 类型的最小 Emp_Time ，第二个返回最大值 Emp_Time 用于'' Out ''类型。

这是一个查询是我在2013年3月9日解决原始问题的解决方案中的原始查询的简单扩展。我刚刚在两个子查询中添加了对日期的检查其中子句。

 选择  distinct  
 CE.Emp_Name，
（选择 min（Emp_Time）来自 Raw_Record  As  MINCE 其中​​ CAST（MINCE.emp_time  as   DATE ）= CAST（CE.emp_time  As   DATE ）< span class =code-keyword> AND  MINCE.E mp_Type = ' 在' 和 MINCE。 Emp_Name = CE.Emp_Name）作为 MinTime，
（选择 max（Emp_Time）来自 Raw_Record 作为 MAXCE 其中 CAST（MAXCE.emp_time < span class =code-keyword> as   DATE ）= CAST（CE.emp_time  As   DATE ） AND  MAXCE.Emp_Type = '  Out' 和 MAXCE.Emp_Name = CE.Emp_name）正如 MaxTime 
 来自 Raw_record 作为 CE 

在SQL Server Express 2012上测试



P .S。不要忘记使用聚集索引来提高性能。

试试这个：

  DECLARE   @ tmp  表（Emp_Name  VARCHAR （ 30 ），Emp_Time  DATETIME ，Emp_Type  VARCHAR （ 3 ））
 
  INSERT  < span class =code-keyword> INTO   @ tmp （Emp_Name，Emp_Time，Emp_Type）
  VALUES （'  Mike'，'   2013-04-01 08:30:48'，' 在'）
  INSERT   INTO   @ tmp （Emp_Name，Emp_Time，Emp_Type）
  VALUES  （'  Mike'，'  2013-04-01 08:35:48'，'  In '）
  INSERT   INTO   @tmp （Emp_Name，Emp_Time，Emp_Type）
  VALUES （'  Mike'，'  2013-04-01 17:25:32 '，'  Out'）
  INSERT   INTO   @ tmp （Emp_Name，Emp_Time，Emp_Type）
 < span class =code-keyword> VALUES （'  Mike'，'  2013-04-01 17:35:32'，'  Out'）
  INSERT   INTO   @ tmp （Emp_Name，Emp_Time，Emp_Type）
  VALUES （'  Mike'，'  2013-04-02 08:35:48'，' 在'）
  INSERT   INTO   @ tmp （Emp_Name，Emp_Time，Emp_Type） 
  VALUES （'  Mike'，'  2013-04-02 08:35:48'，' 在'）
  INSERT   INTO   @ tmp （Emp_Name，Emp_Time，Emp_Type）
  VALUES （'  Mike'，'  2013-04-02 17： 45:32'，'  Out'）
  INSERT   INTO   @ tmp （Emp_Name，Emp_Time，Emp_Type）
  VALUES （'  Mike'， '  2013-04-02 17:55:32'，'  Out'）
  INSERT   INTO   @ tmp （Emp_Name，Emp_Time，Emp_Type）
  VALUES （'  Josh'，'  2013-04-01 08:30:48'，' 在'）
  INSERT   INTO   @ tmp （Emp_Name，Emp_Time，Emp_Type）
  VALUES （'  Josh'，'  2013-04-01 08:35:48'，' 在'）
  INSERT   INTO   @tmp （Emp_Name，Emp_Time，Emp_Type）
  VALUES （' < span class =code-string> Josh'，'  2013-04-01 17:25： 32'，'  Out'）
  INSERT   INTO   @ tmp （Emp_Name，Emp_Time，Emp_Type）
  VALUES （'  Josh'，'  2013-04-01 17:35:32'，'   Out'）
  INSERT   INTO   @ tmp （Emp_Name，Emp_Time，Emp_Type）
  VALUES （'  Josh'，'  2013-04-02 08 ：35：48'，' 在'）
  INSERT   INTO   @ tmp （Emp_Name，Emp_Time，Emp_Type）
  VALUES （'  Josh' ，'  2013-04-02 08:35:48'，' 在'）
  INSERT   INTO   @ tmp （Emp_Name，Emp_Time，Emp_Type）
  VALUES  （'  Josh'，'  2013-04-02 17：45：3 2'，'  Out'）
  INSERT   INTO   @ tmp （Emp_Name，Emp_Time，Emp_Type）
  VALUES （'  Josh'，'  2013-04-02 17:55:32'，'   Out'）
 
  SELECT  t3.RowNo，t3。 Emp_Name，t3.DateInOut，t3.Time_In，t4.Time_Out 
  FROM （
  SELECT  ROW_NUMBER（） OVER （ ORDER   BY  Emp_Name，Time_In） AS  RowNo，t1。* 
  FROM （
 < span class =code-k eyword> SELECT  Emp_Name， CONVERT （ VARCHAR （ 10 ），Emp_Time， 121 ） AS  DateInOut，MIN（Emp_Time） AS  Time_In 
  FROM   @ tmp  
  WHERE  Emp_Type = ' 在' 
  GROUP   BY  Emp_Name， CONVERT （ VARCHAR （ 10 ），Emp_Time， 121 ）
） AS  t1） AS  t3  INNER   JOIN （
  SELECT  ROW_NUMBER（） OVER （ ORDER   BY  Emp_Name，Time_Out） AS  RowNo，t2。* 
  FROM （
 < span class =code-keyword> SELECT  Emp_Name， CONVERT （ VARCHAR （ 10 ），Emp_Time， 121 ） AS  DateInOut， MAX（Emp_Time） AS  Time_Out 
  FROM   @ tmp  
  WHERE  Emp_Type = '  Out' 
  GROUP   BY  Emp_Name， CONVERT （ VARCHAR （ 10 ），Emp_Time， 121 ）
） AS  t2） AS  t4  ON  t3.RowNo = t4.RowNo 

返回值：

 R. Emp_N。 DateInOut Time_In Time_Out 
 1 Josh 2013-04-01 2013-04-01 08：30：48.000 2013-04-01 17：35：32.000 
 2 Josh 2013-04-02 2013-04-02 08：35：48.000 2013-04-02 17：55：32.000 
 3 Mike 2013-04-01 2013-04-01 08：30：48.000 2013-04-01 17：35：32.000 
 4 Mike 2013-04-02 2013-04-02 08：35：48.000 2013-04-02 17:55:32000 

您只能从 Time_In 和 Time_Out 中提取时间。为此，请使用 CAST或CONVERT功能 [ ^ ]。

I am so confused which is the best approach to get employee''s (first time in) & (last time out) each day when they have login to our biometrics clock system. This data came from a biometric device RAW text file and i just imported it to my DB, Now i needed to select each employees First IN & LAST OUT everyday..

Emp_Name	Emp_Time		Emp_Type
Mike		2013-04-01 08:30:48	In
Mike		2013-04-01 08:35:48	In
Mike		2013-04-01 17:25:32	Out
Mike		2013-04-01 17:35:32	Out
Mike		2013-04-02 08:35:48	In
Mike		2013-04-02 08:35:48	In
Mike		2013-04-02 17:45:32	Out
Mike		2013-04-02 17:55:32	Out
Josh		2013-04-01 08:30:48	In
Josh		2013-04-01 08:35:48	In
Josh		2013-04-01 17:25:32	Out
Josh		2013-04-01 17:35:32	Out
Josh		2013-04-02 08:35:48	In
Josh		2013-04-02 08:35:48	In
Josh		2013-04-02 17:45:32	Out
Josh		2013-04-02 17:55:32	Out

I have tried this code

select distinct
CE.Emp_Name,
(select min(Emp_Time) from Raw_Record As MINCE where MINCE.Emp_Type ='In' and MINCE.Emp_Name=CE.Emp_Name) As MinTime,
(select max(Emp_Time) from Raw_Record As MAXCE where MAXCE.Emp_Type ='Out' and MAXCE.Emp_Name=CE.Emp_name) As MaxTime
from Raw_Record As CE

But it only returns the following:

NAME    TIME IN                 TIME OUT  

Mike	2013-04-01 08:30:48	2013-04-02 17:55:32
Josh	2013-04-01 08:30:48	2013-04-02 17:55:32

What i needed to produce is somehow like this:

NAME    TIME IN                 TIME OUT

Mike	4/1/2013 08:30:48	2013-04-01 17:35:32
Mike	2013-04-02 08:35:48	2013-04-02 17:55:32
Josh	2013-04-01 08:30:48	2013-04-01 17:35:32
Josh	2013-04-02 08:35:48	2013-04-02 17:55:32

解决方案

My first response was to replace the use of Raw_Record in your query with the following SELECT statement:

SELECT Emp_Name, CONVERT(Date, Emp_Time) AS Emp_Date, Emp_Time, Emp_Type
  FROM Raw_Record

The result of this query is:

Emp_Name	Emp_Date	Emp_Time		Emp_Type
Mike		2013-04-01	2013-04-01 08:30:48.000	In 
Mike		2013-04-01	2013-04-01 08:35:48.000	In 
Mike		2013-04-01	2013-04-01 17:25:32.000	Out
Mike		2013-04-01	2013-04-01 17:35:32.000	Out
Mike		2013-04-02	2013-04-02 08:35:48.000	In 
Mike		2013-04-02	2013-04-02 08:35:48.000	In 
Mike		2013-04-02	2013-04-02 17:45:32.000	Out
Mike		2013-04-02	2013-04-02 17:55:32.000	Out
Josh		2013-04-01	2013-04-01 08:30:48.000	In 
Josh		2013-04-01	2013-04-01 08:35:48.000	In 
Josh		2013-04-01	2013-04-01 17:25:32.000	Out
Josh		2013-04-01	2013-04-01 17:35:32.000	Out
Josh		2013-04-02	2013-04-02 08:35:48.000	In 
Josh		2013-04-02	2013-04-02 08:35:48.000	In 
Josh		2013-04-02	2013-04-02 17:45:32.000	Out
Josh		2013-04-02	2013-04-02 17:55:32.000	Out

This way I could include the unique date in the selection and there by getting the maximum and minimum Emp_Time for each unique date.
The resulting query would be:

SELECT DISTINCT CE.Emp_Name,
       (SELECT MIN(Emp_Time)
          FROM (SELECT Emp_Name, CONVERT(Date, Emp_Time) AS Emp_Date, Emp_Time, Emp_Type
                  FROM Raw_Record) AS MINCE
         WHERE MINCE.Emp_Type ='In' AND MINCE.Emp_Date = CE.Emp_Date AND MINCE.Emp_Name = CE.Emp_Name) AS MinTime,
        (SELECT MAX(Emp_Time)
           FROM (SELECT Emp_Name, CONVERT(Date, Emp_Time) AS Emp_Date, Emp_Time, Emp_Type
                   FROM Raw_Record) AS MINCE
          WHERE MINCE.Emp_Type ='Out' AND MINCE.Emp_Date = CE.Emp_Date AND MINCE.Emp_Name = CE.Emp_Name) AS MaxTime
  FROM (SELECT Emp_Name, CONVERT(Date, Emp_Time) AS Emp_Date, Emp_Time, Emp_Type
          FROM Raw_Record) AS CE

This gives the result you are looking for, but it is not a very efficient and elegant query.

So I started looking for a better way to solve your problem and this is what I came up with:

SELECT DISTINCT Raw_Record.Emp_Name, Min_In.Emp_Time AS MinTime, Max_Out.Emp_Time AS MaxTime
  FROM Raw_Record

  JOIN (  SELECT Emp_Name, Emp_Type, CONVERT(Date, Emp_Time) AS Emp_Date, Min(Emp_Time) AS Emp_Time
            FROM Raw_Record
           WHERE Emp_Type = 'In'
        GROUP BY Emp_Name, Emp_Type, CONVERT(Date, Emp_Time)) AS Min_In
    ON Min_In.Emp_Name = Raw_Record.Emp_Name AND Min_In.Emp_Date = CONVERT(Date, Raw_Record.Emp_Time)

  JOIN (  SELECT Emp_Name, Emp_Type, CONVERT(Date, Emp_Time) AS Emp_Date, Max(Emp_Time) AS Emp_Time
            FROM Raw_Record
           WHERE Emp_Type = 'Out'
        GROUP BY Emp_Name, Emp_Type, CONVERT(Date, Emp_Time)) AS Max_Out
    ON Max_Out.Emp_Name = Raw_Record.Emp_Name AND Max_Out.Emp_Date = CONVERT(Date, Raw_Record.Emp_Time)

It joins the content of the original table with the results of two sub-queries based on the Emp_Name and unique date (Emp_Date). The first sub-query returns the minimum Emp_Time for the ''In'' type and the second the maximum Emp_Time for the ''Out'' type.

Here is a query that is a simple extension of the original query that I put in the solution to your original question on 9 March 2013. I just added a check for the date to the two subquery Where clauses.

select distinct
CE.Emp_Name,
(select min(Emp_Time) from Raw_Record As MINCE where CAST(MINCE.emp_time as DATE)=CAST(CE.emp_time As DATE) AND MINCE.Emp_Type ='In' and MINCE.Emp_Name=CE.Emp_Name) As MinTime,
(select max(Emp_Time) from Raw_Record As MAXCE where CAST(MAXCE.emp_time as DATE)=CAST(CE.emp_time As DATE) AND MAXCE.Emp_Type ='Out' and MAXCE.Emp_Name=CE.Emp_name) As MaxTime
from Raw_record As CE

Tested on SQL Server Express 2012

P.S. Don''t forget to have a clustered index to improve performance.

Try this:

DECLARE @tmp TABLE(Emp_Name VARCHAR(30), Emp_Time DATETIME, Emp_Type VARCHAR(3))

INSERT INTO @tmp (Emp_Name, Emp_Time, Emp_Type)
VALUES('Mike', '2013-04-01 08:30:48', 'In')
INSERT INTO @tmp (Emp_Name, Emp_Time, Emp_Type)
VALUES('Mike', '2013-04-01 08:35:48', 'In')
INSERT INTO @tmp (Emp_Name, Emp_Time, Emp_Type)
VALUES('Mike', '2013-04-01 17:25:32', 'Out')
INSERT INTO @tmp (Emp_Name, Emp_Time, Emp_Type)
VALUES('Mike', '2013-04-01 17:35:32', 'Out')
INSERT INTO @tmp (Emp_Name, Emp_Time, Emp_Type)
VALUES('Mike', '2013-04-02 08:35:48', 'In')
INSERT INTO @tmp (Emp_Name, Emp_Time, Emp_Type)
VALUES('Mike', '2013-04-02 08:35:48', 'In')
INSERT INTO @tmp (Emp_Name, Emp_Time, Emp_Type)
VALUES('Mike', '2013-04-02 17:45:32', 'Out')
INSERT INTO @tmp (Emp_Name, Emp_Time, Emp_Type)
VALUES('Mike', '2013-04-02 17:55:32', 'Out')
INSERT INTO @tmp (Emp_Name, Emp_Time, Emp_Type)
VALUES('Josh', '2013-04-01 08:30:48', 'In')
INSERT INTO @tmp (Emp_Name, Emp_Time, Emp_Type)
VALUES('Josh', '2013-04-01 08:35:48', 'In')
INSERT INTO @tmp (Emp_Name, Emp_Time, Emp_Type)
VALUES('Josh', '2013-04-01 17:25:32', 'Out')
INSERT INTO @tmp (Emp_Name, Emp_Time, Emp_Type)
VALUES('Josh', '2013-04-01 17:35:32', 'Out')
INSERT INTO @tmp (Emp_Name, Emp_Time, Emp_Type)
VALUES('Josh', '2013-04-02 08:35:48', 'In')
INSERT INTO @tmp (Emp_Name, Emp_Time, Emp_Type)
VALUES('Josh', '2013-04-02 08:35:48', 'In')
INSERT INTO @tmp (Emp_Name, Emp_Time, Emp_Type)
VALUES('Josh', '2013-04-02 17:45:32', 'Out')
INSERT INTO @tmp (Emp_Name, Emp_Time, Emp_Type)
VALUES('Josh', '2013-04-02 17:55:32', 'Out')

SELECT t3.RowNo, t3.Emp_Name, t3.DateInOut, t3.Time_In, t4.Time_Out
FROM (
	SELECT ROW_NUMBER() OVER (ORDER BY Emp_Name, Time_In) AS RowNo, t1.*
	FROM(
		SELECT Emp_Name, CONVERT(VARCHAR(10), Emp_Time,121) AS DateInOut, MIN(Emp_Time) AS Time_In
		FROM @tmp
		WHERE Emp_Type = 'In'
		GROUP BY Emp_Name, CONVERT(VARCHAR(10), Emp_Time,121)
	) AS t1) AS t3 INNER JOIN (
				SELECT ROW_NUMBER() OVER (ORDER BY Emp_Name, Time_Out) AS RowNo, t2.*
				FROM(
					SELECT Emp_Name, CONVERT(VARCHAR(10), Emp_Time,121) AS DateInOut, MAX(Emp_Time) AS Time_Out
					FROM @tmp
					WHERE Emp_Type = 'Out'
					GROUP BY Emp_Name, CONVERT(VARCHAR(10), Emp_Time,121)
				) AS t2) AS t4 ON t3.RowNo = t4.RowNo

Returned values:

R.  Emp_N.  DateInOut   Time_In                 Time_Out
1   Josh    2013-04-01  2013-04-01 08:30:48.000 2013-04-01 17:35:32.000
2   Josh    2013-04-02  2013-04-02 08:35:48.000 2013-04-02 17:55:32.000
3   Mike    2013-04-01  2013-04-01 08:30:48.000 2013-04-01 17:35:32.000
4   Mike    2013-04-02  2013-04-02 08:35:48.000 2013-04-02 17:55:32.000

You can extract only time from Time_In and Time_Out. To do it, use CAST or CONVERT function[^].

这篇关于每天选择列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！